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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: