Off-Request/Background Query

When you invoke a query, the request will be held up for the length of time it takes to perform the query. For the vast majority of applications this is the usual way of servicing a request. However there are situations where you may not wish to hold up the client request, waiting, for the query to complete. For example, performing some logging operations.

OpenBD offers a facility to manage these, off-request, query operations in a structured manner using what is known as background processing.

To utilise this functionality, you simply add the background="yes" attribute to the CFQUERY tag.

<cfquery datasource="#application.ds#" background="yes">
  insert into tablex (a,b,c) values (1,2,<cfqueryparam value="#Day(now())#">)
</cfquery>

What this attribute does, is to collect all the data for the query to run, and then adds it to the background query spooling engine for execution at a later time. The tag returns immediately without any interaction to the database you looking to the query against.

The spooling engine, executes the query in a serialized manner, one after another, using the same connection pooling contention as any other normal query tag. This is done in a completely separate thread managed by the OpenBD application server. The queries are saved to disk to ensure no loss even in the event of a server restart.

The main advantage of this mechanism, over say pushing it to a CFTHREAD block, is that there is a lot less overhead. Management of the queries, persistance and error reporting is all built-in without having to manually provide for that.

Tracking Off-Requests queries

Queries are spooled to the directory /bluedragon/cfquerybatch/spool/. Every 5 minutes the engine comes along and picks up all the queries waiting for execution. They are then run against the given datasource. If the engine fails to execute the query, then the file is moved to the /bluedragon/cfquerybatch/failed/ directory.

If the query failed because of a connection error, then file name will be prefixed with "connection-YYYY.batchsql". Any other error is marked as simply "failed-YYYY.batchsql".

In addition to the renaming and moving of the files, you can look at the /bluedragon/cfquerybatch/querybatch-error.log for the precise reason it failed. The SQL that was attempted will be detailed in there.

Once all the files have been processed, an entry is written to the main /bluedragon/cfquerybatch/querybatch.log log file detailing the number of queries it ran, how many failed and what the average time for execution was.

17/09/10 01:21.37: -] runSQL: success=6; SQLFailed=0; ConnectedFailed=0; SQL=32ms; avg=5ms
17/09/10 01:26.37: -] runSQL: success=6; SQLFailed=0; ConnectedFailed=0; SQL=43ms; avg=7ms

It is very easy to respool failed queries, by simply copying the files back into the spool directory, where they will be picked up within 5 minutes. You can place the files anywhere inside the spool directory, the sub-directory does not matter. OpenBD creates a number of sub-directories within the spool directory to allow for greater File I/O efficieny on operating systems by spreading the number of large files over a number of separate directories.