devxlogo

Use Asynchronicity for Speed

Use Asynchronicity for Speed

If you need to run a complicated query that returns a large recordset, ADO 2.1 gives you the best of both worlds. Sometimes you just need to put a recordset into an AddItem type grid, or prepare it for a report. So, if you need to process the recordset as soon as the first record is fetched, you should start processing in the Execute_Complete event of the connection object. If you can also use a disconnected recordset, you can set the ActiveCon-nection property equal to nothing. This code might be the fastest way to process a large recordset with ADO:

 Private WithEvents m_adoConEvent As ADODB.Connection' the RS that enables the event Fetch_Complete ' to be fired off Attribute Private WithEvents m_adoRstEvent As ADODB.Recordset Private Sub GetRecordSet()	Dim sSQL As String 	'A large or complicated SQL statemtent	sSQL = "select a large complicate query"	Set m_adoConEvent = New ADODB.Connection	Set m_adoRstEvent = New ADODB.Recordset 	m_adoConEvent.Open "Connection String"	m_adoRstEvent.CursorLocation = adUseClient	Me.Caption = "Started" 	'Do something to tell the user where the process is 	'at. Have the command execute and fetch at the same 	'time without interrupting workflow.	m_adoRstEvent.Open sSQL, m_adoConEvent, _		adOpenStatc, adLockReadOnly, adCmdText _		Or adAsyncFetch Or adAsyncExecute End SubPrivate Sub m_adoConEvent_ExecuteComplete(ByVal _	RecordsAffected As Long, ByVal pError _	As ADODB.Error, adStatus As ADODB.EventStatusEnum, _	ByVal pCommand As ADODB.Command, ByVal pRecordset As _	ADODB.Recordset, ByVal pConnection As ADODB.Connection)	Do Until pRecordset.EOF		'start processing the recordset	LoopEnd SubPrivate Sub m_adoRstEvent_FetchComplete(ByVal pError _	As ADODB.Error, adStatus As ADODB.EventStatusEnum, _	ByVal pRecordset As ADODB.Recordset)	Set pRecordset.ActiveConnection = Nothing 	'this will speed processing timeEnd SubPrivate Sub m_adoRstEvent_FetchProgress(ByVal Progress _	As Long, ByVal MaxProgress As Long, _	adStatus As ADODB.EventStatusEnum, ByVal _	pRecordset As ADODB.Recordset)	'let the user know work is happeningEnd Sub

By using both adAsyncFetch and adAsyncExecute, you can start processing even while you’re returning data.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist