Why ADO So Slow ?
Both examples use ADODB in VB6.
Example 1 takes about 4 seconds to populate a grid with 5000 lines.
Example 2 takes less than 1 second to populate the same grid with the same data from the same file.
WHY ?
Have I lost something by using the FAST Method ?
Thanks for any comments
EXAMPLE 1 - SLOW
Dim strProvider As String
Dim strSource As String
OpenTable:
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
strSource = "Data Source=" & InteleStatsPath & ";"
Set rstConnRecordset = New ADODB.Connection
strConnRecordset = strProvider & strSource & "Persist Security Info=False"
rstConnRecordset.Open strConnRecordset
Set rstRecordset = New ADODB.Recordset
On Error GoTo CreateTable
With rstRecordset
.ActiveConnection = rstConnRecordset
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open TableName1 <--- BIG DELAY HERE
End With
If Not rstRecordset.BOF And Not rstRecordset.EOF Then
Set Grid.DataSource = rstRecordset <--- BIG DELAY HERE
End If
EXAMPLE 2 - VERY FAST
(Private Db As New ADODB.Connection)
(Private Rs As New ADODB.Recordset)
Db.Provider = "Microsoft.Jet.OLEDB.4.0"
Db.Open InteleStatsPath 'DatabaseImportPath
On Error Resume Next
Rs.Close
Set Rs = Nothing
SetStrSql
On Error GoTo EE1
Rs.Open StrSQL, Db, adOpenStatic, adLockReadOnly
If Rs.RecordCount = 0 Then
MsgBox "No Records on File", vbInformation, "Master File"
Exit Sub
End If
Rs.MoveFirst
If Not Rs.BOF And Not Rs.EOF Then
Set Grid.DataSource = Rs
End If
[2033 byte] By [
George1111] at [2007-11-20 8:23:29]
# 1 Re: Why ADO So Slow ?
Mostly because of the CursorLocation. In Example 1 you are using a Client Side cursor and second example you are not specifying a cursor.
Lets wait for others to give us more insight on this.
# 2 Re: Why ADO So Slow ?
I am running the program on the same machine where the data resides -
Also, I am querying an ACCESS Database
# 3 Re: Why ADO So Slow ?
does the grid is really populated with records in #2? because the recordset's cursor location (server side) isn't appropriate for data grid.
# 4 Re: Why ADO So Slow ?
Yes - it does populate in both examples
Thanks
# 5 Re: Why ADO So Slow ?
agree with Thread1
if it use to populate grid it must use ClientSide cursor
in example #2 u didn't set CursorLocation so i think it's on Client as default otherwise u'll get an error
for the #, it's fast because of the CursorType and also because of the Locktype
from MSDN
CursorType
adOpenDynamic 2 Uses a dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the Recordset are allowed, except for bookmarks, if the provider doesn't support them.
adOpenForwardOnly 0 Default. Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward through records. This improves performance when you need to make only one pass through a Recordset.
adOpenKeyset 1 Uses a keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your Recordset. Data changes by other users are still visible.
adOpenStatic 3 Uses a static cursor, which is a static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.
adOpenUnspecified -1 Does not specify the type of cursor.
LockType
adLockBatchOptimistic 4 Indicates optimistic batch updates. Required for batch update mode.
adLockOptimistic 3 Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method.
adLockPessimistic 2 Indicates pessimistic locking, record by record. The provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately after editing.
adLockReadOnly 1 Indicates read-only records. You cannot alter the data.
adLockUnspecified -1 Does not specify a type of lock. For clones, the clone is created with the same lock type as the original.
also at #1 u open it with Table Name rather than using Query, u should use adCmdTable option
adCmdUnspecified -1 This value indicates that the CommandText property is unspecified. This value is not supported by the OLE DB Provider for AS/400 and VSAM.
adCmdText 1 This value evaluates the CommandText property as a textual definition of a command or stored procedure call. adCmdTable 2 This value evaluates the CommandText property as a table name. This value is not supported by the OLE DB Provider for AS/400 and VSAM.
adCmdStoredProc 4 This value evaluates the CommandText property as a stored procedure name. This value is not supported by the OLE DB Provider for AS/400 and VSAM.
adCmdUnknown 8 This value indicates that the type of command in CommandText property is not known. This is the default value. This value is not supported by the OLE DB Provider for AS/400 and VSAM
HTH,
erick
# 6 Re: Why ADO So Slow ?
i have noticed that the first time i open a connection to an MSAccess DB
it takes longer than the second time using the very same code.
see what happens if you swap the order of the open operations.
is the second way now slower thant the first?
# 7 Re: Why ADO So Slow ?
In the code
With rstRecordset
.ActiveConnection = rstConnRecordset
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open TableName1 <--- BIG DELAY HERE ONE
End With
If Not rstRecordset.BOF And Not rstRecordset.EOF Then
Set Grid.DataSource = rstRecordset <--- BIG DELAY HERE TWO
End If
Is it that because we are using adUseClient in the open statement, this causes the table to be copied from the server to the client memory causing the delay in ONE
Obviously there needs to be a delay in TWO as the table is populating agrid
If I changed adUseClient to adUseServer then there would be no need to copy data from the server to the client
Does this make sense ??
Thanks
