Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Visual Basic
Expertise: Advanced
Jun 29, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 Sub
Private 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
End Sub
Private 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 time
End Sub
Private 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 happening
End Sub
By using both adAsyncFetch and adAsyncExecute, you can start processing even while you're returning data.
Darren McBratney
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date