ADO slow recordset getrows

[RB Smissaert]

Using Excel XP.
Using ADO with ODBC to connect to an Interbase database.
The ODBC driver is the one from EasySoft

The basic code goes like this:

Public ADOConn As ADODB.Connection

Set ADOConn = New ADODB.Connection



Dim rs As ADODB.Recordset
dim testArray as variant

Set rs = New ADODB.Recordset

'any useful properties to set here?
'rs.CursorLocation = adUseServer
'rs.CacheSize = 30

rs.Open Source:=SQLStatement, _
ActiveConnection:=ADOConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

testArray = rs.GetRows


Now the last statement can take a long time.
Strangely this seems to depend on the SQLStatement, but not on the number of
records returned.
So one query can return a large number of records and the rs.GetRows goes
very fast, whereas another SQLStatement returns less records, but rs.GetRows
takes much longer.
Tried all different properties for the recordset, but it doesn't make much
difference.
One problem is that I only can see how many records rs.Open produces after
transferring to an array because rs.RecordCount doesn't work.

Any advice here greatly appreciated.



RBS

 

[R. Choate]

If you'll tell me what you are trying to do(big picture), I might be able to
give you some helpful info.
--
RMC,CPA

Using Excel XP.
Using ADO with ODBC to connect to an Interbase database.
The ODBC driver is the one from EasySoft

The basic code goes like this:

Public ADOConn As ADODB.Connection

Set ADOConn = New ADODB.Connection



Dim rs As ADODB.Recordset
dim testArray as variant

Set rs = New ADODB.Recordset

'any useful properties to set here?
'rs.CursorLocation = adUseServer
'rs.CacheSize = 30

rs.Open Source:=SQLStatement, _
ActiveConnection:=ADOConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

testArray = rs.GetRows


Now the last statement can take a long time.
Strangely this seems to depend on the SQLStatement, but not on the number of
records returned.
So one query can return a large number of records and the rs.GetRows goes
very fast, whereas another SQLStatement returns less records, but rs.GetRows
takes much longer.
Tried all different properties for the recordset, but it doesn't make much
difference.
One problem is that I only can see how many records rs.Open produces after
transferring to an array because rs.RecordCount doesn't work.

Any advice here greatly appreciated.



RBS

 

[Pete Tringale]

RB,

Without knowing anything about the system architecture
involved one thing you may try is:

rs.CursorLocation = adUseClient

This caches the recordset rows on your local machine
instead of the database server. If the database you are
connecting to is remote, this could save many roundtrips
to the server.

Another thing to try is:

rs.LockType = adLockOptimistic

This tells the provider (database) to not lock records
unless an Update is called. But you aren't updating
records since

rs.CursorType = adForwardOnly and that is not an
updatable recordset cursor type.

Setting rs.LockType = adLockReadOnly may cause the
provider to place read-only locks on each row of the
result set. This could have a performance impact
depending on whether the provider does row-level or table-
level locking and/or if there are other processes
attempting to operate on the same tables.

Good Luck,

pete...

 

[Random]

Have you tried using vbs to read to a text file? Don't know why, but
the slowdown could be coming from Excel. This would be one way to
check.

 

[RB Smissaert]

Not quite sure what you mean, but this is the connection string:

EasySoftConn = "DSN=" & GetS6000DSN() & ";" & _
"UID=" & Username & _
";PWD=" & Password & _
";DB=" & GetIBPath() & _
";OLDMETADATA=1;"

RBS