Using the ADO GetRows Method for Faster Data Retrieval

by Ernest Bonat and Christopher Bond

In this article, we show the results of a case study performed to show the fastest method for retrieving data from the IBM DB2 Mainframe. We'll discuss the differences in retrieval time with each new release of VB. In this article, we'll also show you how we used the GetRows method of ADO Recordset object using VB 6.0 against MS Access 97, in which we got 40,000 records in a single array without any problems (in about 11 seconds). We'll then compare the processing time between the Recordset GetRows method and the Recordset object by itself. The grid control that we use in our comparison, shown in Figure A, is the True DBGrid 6.0 (OLE DB) Control in unbound extended mode.

Figure A: This is the ADO Recordset object using GetRows method population.

Background

Microsoft originally introduced the GetRows method of the DAO 3.0 and RDO 1.0 (32-bit) recordset objects in VB 4.0. We experimented with it, trying to get an entire data table from the IBM DB2 Mainframe with 47 fields and 3,317 records only to be confronted with a VB error that said something like Runtime error (and a number), Visual Basic out of memory. Back then, this method wasn't very effective, but things improved with VB 5.0 and 6.0--especially with the release of the new MS data access tool, ADO 2.0. Then, when VB 6.0 came out, we repeated our experiment, this time with 3,586 records in the table. To connect to the IBM DB2 Mainframe, we used the Microsoft OLE ODBC default provider. After a couple of attempts, our best time was nine seconds. We were so impressed with the outcome (considering the network traffic), we wrote this article to share our results.

Approaches to counting records

In Client/Server development, the unbound mode of programming is more effective because of the low processing speed of the bound Data Control and binding controls. In cases where applications display multiple records, we generally use the grid control. To populate a grid with data in the unbound mode, we need to know two parametershow many rows and how many columns are going to be in the grid. The number of rows are the records that will be returned in the Recordset object. The number of columns are the fields that have been selected in the Recordset object. To get the number of fields isn't a problem; you just use the Fields Count property of the Recordset object. For example:

Dim iFieldCount As Integer 

...

 `Open recordset named rdRecordsetName
...
iFieldCount = _
	rdRecordsetName.Fields.Count

This statement will return an integer value (iFieldCount) for the amount of fields. The real problem is determining the amount of records returned from the Recordset object. Back in the days of VB 3.0, there were two approaches to determine the number of records. One approach was to move the Recordset object and then use the RecordCount method like this:

Dim lRecordCount As Long 	
...
`Open recordset named rdRecordsetName
...
rdRecordsetName.MoveLast
rdRecordsetName.MoveFirst
...        	
lRecordCount = _
	rdRecordsetName.RecordCount

This approach is kind of slow because we're moving the entire Recordset to get the lRecordCount value. The more records there are to count, the slower the process.

The other approach is to use the Count SQL Aggregate Function in the stored procedure, and then call it in the separate Recordset object to get the lRecordCount value. The following example shows how this works. The stored procedure definition in the MS Access database might look something like this:


"SELECT Count(*) as RecordCountValue" & _
	" FROM tblTableName"

Dim lRecordCount as Long 	
...
`Open recordset named rdRecordsetName
...
lRecordCount = rdRecordsetName! 
	lRecordCountValue

This approach is faster than the first method, but it still requires two Recordset objectsone to get the lRecordCount value and the other to populate the grid control with data.

There's an excellent technical paper that discusses various approaches to counting records using the RDO 2.0 and GetRows method called "Counting your rows before they hatch." You can find the paper on the Microsoft Web site at

msdn.microsoft.com/vstudio/technical/articles/countingrows/default.asp

The author did a great job putting together some very useful information. This is definitely some worthwhile reading.

Features of the ADO GetRows method

With the introduction of ADO 2.0, Microsoft delivered the GetRows method of the Recordset objects. In fact, the main purpose of this method is to get multiple records of a Recordset object and put them into a variant two-dimensional array. This method is defined in one line of code as:


ArrayName = RecordsetName.GetRows
( Rows, Start, Fields )

We've dissected this line of code to show you what each variable means:

ArrayName--Returned two-dimensional variant array
RecordsetName--recordset object name
Rows--An optional long value indicating the number of records to retrieve, by default is -1 (or constant adGetRowsRest)
Start--An optional string or variant that evaluates to the bookmark for the record from which the GetRows method should begin
Fields--A variant expression representing a single field name ordinal position or an array of field names or ordinal position numbers

If you search for GetRows in VB Help, you'll find out more. Basically, there are two important benefits of the ADO GetRows method. First, you can automatically retrieve all the records without moving the entire Recordset object. Since the variant array is dynamically allocated, you no longer have to worry about the size of records returned. The size of the records can easily be determined using the Unbound function of the variant array plus one. This is the GetRows Recordset procedure, as shown in Listing A.

Listing A: The GetRows Recordset procedure

Public Sub GetRowsRecordset
	(prdADO As ADODB.Recordset, _
		pvarArrayData As Variant, _
		plRecordCount As Long, _
		piFieldCount As Integer)    
On Error GoTo GetRowsRecordsetError
	If prdADO.EOF then
		plRecordCount = 0	
	Else
		pvarArrayData =     
		prdADO.GetRows()
		plRecordCount =   
		UBound(pvarArrayData, 2) 
		+ 1
		piFieldCount =    
		prdADO.Fields.Count
	End if
GetRowsRecordsetExit:
	Exit Sub
	GetRowsRecordsetError:
	MsgBox "GetRows Method Failed." _
		& Err.Description, _
		vbCritical, _
		"GetRows Method Error"
	Resume GetRowsRecordsetExit
End Sub

The second important benefit (as mentioned in the VB Help file) is, after you call GetRows, the next unread record becomes the current record, or the EOF property is set to True if there are no more records. As a result, the EOF property of the Recordset object is the key value to ensure that all records are fetched, or no records were returned, as shown in the procedure GetRowsRecordset.

This procedure has been very useful in our VB applications where the Recordset object is passed by reference and returns the variant array, lRecordCount and iFieldCount values. These parameter values allow us to populate the grid control in the unbound mode with any type of data.

You'll find an example of using the RDO 2.0 GetRows method in the March 1998 Inside Visual Basic article "Visual Basic down and dirty." One thing we noticed right away was the RDO Recordset object that was created was moved from MoveLast to MoveFirst to determine the lRecordCount value, and then passed as a parameter to GetRows method. By using the ADO GetRows method described in this article, you don't have to do this anymore.

Running the comparison

We used the Access database North Wind (Nwind.mdb), which comes with VB 6.0. The Order Details table was the source of the data. To connect to the Access database, you can use the Microsoft Access Driver, as shown in the procedure SetConnectionString in Listing B, or Microsoft Jet 3.51 OLE DB Provider, as shown in the procedure SetConnectionMSAccess in Listing C.

Listing B: Setting the ADO Connection String using the Microsoft Access Driver

Public Sub SetConnectionString
	(ByVal sDBQ As String, _
		ByVal sDefaultDir As String, _
		ByVal sUID As String, _
		ByVal sPWD As String)
On Error GoTo SetConnectionStringError
	psConnectionString =  
	"DRIVER={Microsoft Access 
	Driver (*.mdb)};" & _
		"DBQ=" & sDBQ & ";" & _
		"DefaultDir=" & sDefaultDir &  
";" & _
		"UID=" & sUID & ";" & _
		"PWD=" & sPWD & ";"
SetConnectionStringExit:
	Exit Sub
SetConnectionStringError:
	MsgBox "Unable to set  
	Connection String. " & 
	Err.Description, _
		vbInformation, _
		"Connection String Error"
Resume SetConnectionStringExit
End Sub

Listing C: Setting the ADO connection string using Microsoft Access OLEDB Provider 3.51

Public Sub SetConnectionMSAccess
	(pconnMSAccess As    
	ADODB.Connection, _
		ByVal psDataSource As String)
On Error GoTo SetConnectionMSAccessError
With pconnMSAccess
		.Provider = 
		"Microsoft.Jet.OLEDB.3.51"
		.ConnectionString = "DATA 
		SOURCE=" & psDataSource
		.CursorLocation = adUseClient
		.Open
End With
SetConnectionMSAccessExit:
Exit Sub
SetConnectionMSAccessError:
	MsgBox "Unable to open 
	connection for Access Database 
	" & psDataSource & ". " & 
	Err.Description, _
		vbInformation, _
		"MS Access Connection Error"
Resume SetConnectionMSAccessExit 
End Sub

We created a form in VB 6.0, as shown in Figures A and B, with the True DBGrid Pro 6.0 control to be populated using Recordset, Command, and Parameter ADO objects. The form shows the ADO used object, total records, and the time (in seconds) it took to populate. Listing D displays all the procedures for Recordset, Command, and Parameter ADO objects, (including Access stored procedures), with the GetRows method used for populating the grid control.

Figure B: Here's the ADO Recordset object using Recordset object population.

Listing D: The GetRows Recordset method

Dim sConnectNwind As String, sSQL As String 
Dim snStart As Single, snEnd As Single
Dim lTotalRecords As Long, iTotalField as integer
Dim DataGridArray() As Variant

Dim connNwind As New ADODB.Connection
Dim cmNwind As New ADODB.Command
Dim rdNwind As New ADODB.Recordset
Dim prmNwind As New ADODB.Parameter


'=========================================================
'RECORDSET OBJECT (GETROWS METHOD)
'=========================================================
snStart = Timer
poGeneral.SetConnectionString sDBQ:=App.Path 
& "\" & "NWIND.MDB", _
							  
sDefaultDir:=App.Path, _
							  
sUID:=", _
							  
sPWD:="
sConnectNwind = poGeneral.ConnectionString()
sSQL = "Select * From [Order Details]"    
rdNwind.Open sSQL, sConnectNwind, adOpenForwardOnly, 
adLockReadOnly
poGeneral.GetRowsRecordset prdADO:=rdNwind, _
						        
pvarArrayData:=DataGridArray, _
						        
plRecordCount:=lTotalRecords, _
						        
piFieldCount:=iTotalField 							
iMaxCol_DBGrid = iTotalField 
lMaxRow_DBGrid = lTotalRecords    
rdNwind.Close
Set rdNwind = Nothing  


'=========================================================
'COMMAND OBJECT (GETROWS METHOD)
'=========================================================
snStart = Timer
poGeneral.SetConnectionString sDBQ:=App.Path & 
"\" & "NWIND.MDB", _
							  
sDefaultDir:=App.Path, _
							  
sUID:=", _
							  
sPWD:="
sConnectNwind = poGeneral.ConnectionString()
connNwind.Open sConnectNwind
With cmNwind
	Set .ActiveConnection = connNwind
	.CommandText = "qryOrderDetailsAll"
	.CommandType = adCmdStoredProc
	Set rdNwind = .Execute
End With
Set cmNwind = Nothing    
poGeneral.GetRowsRecordset prdADO:=rdNwind, _
						        
pvarArrayData:=DataGridArray, _
						        
plRecordCount:=lTotalRecords, _
						        
piFieldCount:=iTotalField 	
iMaxCol_DBGrid = iTotalField 	
lMaxRow_DBGrid = lTotalRecords    
rdNwind.Close
Set rdNwind = Nothing

'=========================================================
'THE qryOrderDetailsAll STORED PROCEDURE CODE:
'=========================================================
SELECT [Order Details].* FROM [Order Details];


'=========================================================
'PARAMETER OBJECT (GETROWS METHOD)
'=========================================================
Dim lOrderId as long  
lOrderId  = 11077  
snStart = Timer
poGeneral.SetConnectionString sDBQ:=App.Path & 
"\" & "NWIND.MDB", _
							  
sDefaultDir:=App.Path, _
							  
sUID:=", _
							  
sPWD:="
sConnectNwind = poGeneral.ConnectionString()
connNwind.Open sConnectNwind
With cmNwind
	Set .ActiveConnection = connNwind
	.CommandText = "qryOrderDetailsByOrderId"
	.CommandType = adCmdStoredProc
	Set prmNwind = .CreateParameter("OrderId", 
adInteger, _
		adParamInput, 10, lOrderId) 
	.Parameters.Append prmNwind
	Set rdNwind = .Execute
End With    
poGeneral.GetRowsRecordset prdADO:=rdNwind, _
						        
pvarArrayData:=DataGridArray, _
						        
plRecordCount:=lTotalRecords
						        
piFieldCount:=iTotalField 
iMaxCol_DBGrid = iTotalField
lMaxRow_DBGrid = lTotalRecords    
rdNwind.Close
Set rdNwind = Nothing


'=========================================================
'THE qryOrderDetailsByOrderId STORED PROCEDURE CODE:
'=========================================================
PARAMETERS parOrderDetails Long;
SELECT [Order Details].OrderID, [Order Details].ProductID, 
[Order _
	Details].UnitPrice, 
[Order Details].Quantity, [Order Details].Discount
FROM [Order Details]
WHERE ((([Order Details].OrderID)<=[parOrderDetails]));

Listing E shows the same procedures. But, in order to populate the DataGridArray two-dimensional array, it moves the ADO Recordset object by itself. The result of using the GetRows method for Recordset, Command, and Parameter ADO objects is similar for 10,000; 20,000; and 40,000 records, as shown in Table A.

Listing E: The Recordset object population of the grid

'
==========================================================
'RECORDSET OBJECT (RECORDSET MOVE)
'=========================================================
Dim lLoop As Long
snStart = Timer
poGeneral.SetConnectionString sDBQ:=App.Path & 
"\" & "NWIND.MDB", _
							  
sDefaultDir:=App.Path, _
							  
sUID:=", _
							  
sPWD:="
sConnectNwind = poGeneral.ConnectionString()
sSQL = "Select * From [Order Details]"
With rdNwind
		.Open sSQL, sConnectNwind, adOpenForwardOnly, 
adLockReadOnly
		iMaxCol_DBGrid = .Fields.Count
		Do Until rdNwind.EOF
			If lLoop = 0 Then
				ReDim DataGridArray(0 To 
iMaxCol_DBGrid - 1, 0)
			Else
				ReDim Preserve DataGridArray
(0 To iMaxCol_DBGrid _
						- 1, lLoop)
			End If
			DataGridArray(0, lLoop) = .Fields(0)
			DataGridArray(1, lLoop) = .Fields(1)
			DataGridArray(2, lLoop) = .Fields(2)
			DataGridArray(3, lLoop) = .Fields(3)
			DataGridArray(4, lLoop) = .Fields(4)
			.MoveNext
			lLoop = lLoop + 1
		Loop
End With
lMaxRow_DBGrid = UBound(DataGridArray, 2) + 1
rdNwind.Close
Set rdNwind = Nothing


'=========================================================
'COMMAND OBJECT (RECORDSET MOVE)
'=========================================================
Dim lLoop As Long
snStart = Timer
poGeneral.SetConnectionString sDBQ:=App.Path & "\" 
& "NWIND.MDB", _
							  
sDefaultDir:=App.Path, _
							  
sUID:=", _
							  
sPWD:="
sConnectNwind = poGeneral.ConnectionString()
connNwind.Open sConnectNwind
With cmNwind
		Set .ActiveConnection = connNwind
		.CommandText = "qryOrderDetailsAll"
		.CommandType = adCmdStoredProc
		Set rdNwind = .Execute
End With
Set cmNwind = Nothing    
With rdNwind
		iMaxCol_DBGrid = .Fields.Count
		Do Until rdNwind.EOF
			If lLoop = 0 Then
				ReDim DataGridArray(0 To 
iMaxCol_DBGrid - 1, 0)
				Else
				ReDim Preserve DataGridArray(0 
To iMaxCol_DBGrid _
						- 1, lLoop)
			End If			DataGridArray(0, 
lLoop) = .Fields(0)
			DataGridArray(1, lLoop) = .Fields(1)
			DataGridArray(2, lLoop) = .Fields(2)
			DataGridArray(3, lLoop) = .Fields(3)
			DataGridArray(4, lLoop) = .Fields(4)
			.MoveNext
			lLoop = lLoop + 1
		Loop
End With
lMaxRow_DBGrid = UBound(DataGridArray, 2) + 1
rdNwind.Close
Set rdNwind = Nothing


'=========================================================
'PARAMETER OBJECT (RECORDSET MOVE)
'=========================================================
Dim lLoop As Long, lOrderId As Long
lOrderId = 11077
snStart = Timer
poGeneral.SetConnectionString sDBQ:=App.Path & "\" 
& "NWIND.MDB", _
							  
sDefaultDir:=App.Path, _
							  
sUID:=", _
							  
sPWD:="
sConnectNwind = poGeneral.ConnectionString()
connNwind.Open sConnectNwind
With cmNwind
		Set .ActiveConnection = connNwind
		.CommandText = "qryOrderDetailsByOrderId"
		.CommandType = adCmdStoredProc
		Set prmNwind = .CreateParameter("OrderId", 
adInteger, _
			adParamInput, 10, lOrderId)
		.Parameters.Append prmNwind
		Set rdNwind = .Execute
End With
With rdNwind
		iMaxCol_DBGrid = .Fields.Count
		Do Until rdNwind.EOF
			If lLoop = 0 Then
				ReDim DataGridArray(0 To 
iMaxCol_DBGrid - 1, 0)
			Else
				ReDim Preserve DataGridArray
(0 To iMaxCol_DBGrid _
						- 1, lLoop)
			End If
			DataGridArray(0, lLoop) = .Fields(0)
			DataGridArray(1, lLoop) = .Fields(1)
			DataGridArray(2, lLoop) = .Fields(2)
			DataGridArray(3, lLoop) = .Fields(3)
			DataGridArray(4, lLoop) = .Fields(4)
			.MoveNext
			lLoop = lLoop + 1
			Loop
End With
lMaxRow_DBGrid = UBound(DataGridArray, 2) + 1
rdNwind.Close
Set rdNwind = Nothing

Table A: Processing time (sec) for ADO 2.0 objects using the GetRows method

Total Records Recordset Command Parameter

10,000 3.07 2.86 2.91

20,000 5.88 5.66 5.77

40,000 11.42 11.20 11.37

Table B: Comparison of GetRows method to Recordset object by itself with 40,000 records.

Method Type Recordset Command Parameter

GetRows 11.42 11.20 11.37

Recordset object 15.55 14.72 14.66

Table B shows the processing time for 40,000 records using the GetRows method and Recordset object by themselves to populate the grid control. As you can see, the GetRows method of the ADO Recordset object is faster than using the Recordset object by itself. In fact, for all ADO objects the GetRows method is faster than using the Recordset object by itself.

Conclusion

When talking with VB developer friends about the ADO 2.0 GetRows method, at first they balk and argue, "no way, a variant array takes too much memory." But, after we show them the results of our comparison, they immediately ask for a copy of the GetRowsRecordset procedure, and that's the end of our discussion. So, if you happen to know of any VB developers who are still using ADO Recordset object by itself, spread the word about the ADO 2.0 GetRows method. It's faster and takes only one line of code!

Copyright © 1999, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.

Total Records	Recordset	Command	Parameter
10,000	3.07	2.86	2.91
20,000	5.88	5.66	5.77
40,000	11.42	11.20	11.37

Method Type	Recordset	Command	Parameter
GetRows	11.42	11.20	11.37
Recordset object	15.55	14.72	14.66