Asynchronous Query Execution

Because of multithreading support is available, the MFC ODBC classes no longer use asynchronous processing. By default, drivers execute ODBC functions synchronously; that is, the application calls a function and the driver does not return control to the application until it has finished executing the function. However, some functions can be executed asynchronously; that is, the application calls the function, and the driver, after minimal processing, returns control to the application. The application can then call other functions while the first function is still executing.

Asynchronous execution is supported for most functions that are largely executed on the data source, such as the functions to prepare and execute SQL statements, retrieve metadata, and fetch data. It is most useful when the task being executed on the data source takes a long time, such as a complex query against a large database.

In MFC version 4.2 and higher, CDatabase::SetSynchronousMode() function which allows asynchronous execution has become obsolete. The MFC ODBC classes now use only synchronous processing.

Two classes CAsyncDatabase and CAsyncRecordset provide support for asynchronous execution. This allows an application perform other tasks while a complex query is executing.

Usage is very simple. Copy CAsyncDatabase.* and CAsyncRecordset.* files into the project. Do not forget


#include "AsyncDatabase.h"
#include "AsyncRecordset.h"
and examine the following samples.

For CAsyncDatabase:
- Open database as usual;
- Call ExecuteSQLAsync(<sql_query_text_here>);
- Call CAsyncDatabase's SQLStillExecuting() to determine whether the query is still executing;
- Call CDatabase's Cancel() function with the HSTMT returned by ExecuteSQLAsync() to cancel the query execution.

CAsyncDatabase sample.


// asynchronous query execution
void ExecuteLongQuery(strQuery)
{
	CAsyncDatabase DB;
	DB.OpenEx("");

	HSTMT hstmt = DB.ExecuteSQLAsync(strQuery);	// complex query
	while(DB.SQLStillExecuting(hstmt))
	{
		// check for Cancel command, for exmpl
		_GET_MESSAGE_
		if(Cancelled()) 	// by Cancel button, for example
		{
			DB.Cancel(hstmt);
			return;
		}
	}
}

For CAsyncRecordset:

- Open recordset using OpenAsync(...). (parameters are the same as for Open());
-Call CAsyncRecordset's StillExecuting() to determine whether the query is still executing;
-Call CRecordset's Cancel() function to cancel the recordset opening (i.e. SELECT query execution).

CAsyncRecordset sample.


// Open Asynchronous Recordset
{
	// DB is a CDatabase
	CAsyncRecordset rs(&DB);

	rs.OpenAsync(CRecordset::snapshot, strQuery, CRecordset::executeDirect);
	
	while(rs.StillExecuting())
	{
		_GET_MESSAGE_
		if(Cancelled())
		{
			rs.Cancel();
			rs.Close();
			return;
		}
	}
	
	if(!rs.IsOpen())
		return;
	else
		Task();	// perform some task
	rs.Close();
}


A few word about OnSetOptions() virtual.

The framework calls this member function to set initial options for the recordset or database. CRecordset and CDatabase's OnSetOptions() determines the data sources support for scrollable cursors and for cursor concurrency and sets the recordsets options accordingly.

You can override OnSetOptions() to set additional options specific to the driver or the data source. For example, if your data source supports opening for exclusive access, you might override OnSetOptions() to take advantage of that ability.

CAsyncDatabase and CAsyncRecordset classes use OnSetOptions() to determine the data source's support for asynchronous function execution and set options accordingly. (If data source does not support asynchronous execution both CAsyncDatabase and CAsyncRecordset execute functions synchronously without warning user.) So when You overrides OnSetOptions() be sure You call CAsyncDatabase::OnSetOptions() or CAsyncRecordset::OnSetOptions().

Download demo project - 14 KB

Download demo executable - 99,9 KB

Download source - 4 KB

P.S.
According to MFC documentation asynchronous processing slows performance.



Comments

  • Asynchronous Query execution in .Net

    Posted by elizas on 02/08/2010 09:00am

    Sometimes, we have requirements in which we have to retrieve data from different tables at one time that take too long time to execute because of bulk data. In that case we have to wait for execution of first query to complete to start the second one. This makes a long waiting time and hence poor performance. With ADO.Net 2.0, there is a new feature introduced for executing commands asynchronously. In ADO.NET 1.1 and prior versions, we have to wait for one query to finish the execution, then to execute another query. But now we can execute multiple queries asynchronously without waiting. To implement this we have to provide an additional setting in the connection string and that is to set bAsynchronous Processing=trueb. In case you have a requirement to use both synchronous and asynchronous operation then use two separate connection strings, one with bAsynchronous Processing=trueb and other with bAsynchronous Processing=falseb for better performance. http://www.mindfiresolutions.com/

    Reply
  • CRecordset "base class undefined"

    Posted by Legacy on 05/23/2002 12:00am

    Originally posted by: cdteruel

    please help me too!!

    Reply
  • Very useful

    Posted by Legacy on 05/24/2000 12:00am

    Originally posted by: Ly Nguyen

    Very useful! Your article have saved me from unnecessary using multi-threading in my program.

    Thank you

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • On-demand Event Event Date: December 18, 2014 The Internet of Things (IoT) incorporates physical devices into business processes using predictive analytics. While it relies heavily on existing Internet technologies, it differs by including physical devices, specialized protocols, physical analytics, and a unique partner network. To capture the real business value of IoT, the industry must move beyond customized projects to general patterns and platforms. Check out this webcast and join industry experts as …

  • On-Demand Webcast APIs can be a great source of competitive advantage. The practice of exposing backend services as APIs has become pervasive, however their use varies widely across companies and industries. Some companies leverage APIs to create internal, operational and development efficiencies, while others use them to drive ancillary revenue channels. Many companies successfully support both public and private programs from the same API by varying levels of access to different constituents. Nearly all …

Most Popular Programming Stories

More for Developers

RSS Feeds