Recordset very slow to open/close

[Arnaud Lesauvage]

Hi group !

I am querying an Access database in an Atuocad VBA Module.
The query is a count(*) on 2 joined tables. One of the table is
local, the other one is linked.

When I run the query in the Access database, it runs very fast (~1
second).
When I run it in VBA, the recordset is very slow to open *and*
very slow to close !
I really don't understand why !

Here is my code :

Dim oConn As ADODB.Connection
Dim oRs As New ADODB.Recordset

oConn.Provider = "Microsoft.Jet.OLEDB.4.0"
oConn.Properties("Data Source") = strDBPath
oConn.Properties("Jet OLEDB:System database") = strSecurePath
oConn.Open UserID:=strUser, Password:=strPass

oRs.CursorLocation = adUseServer '(I added this code to check if
server side cursor was faster, but it doesn't change anything)

oRs.Open "SELECT Count(*) AS theCount FROM A LEFT JOIN B ON ...
WHERE ...", oConn, adOpenForwardOnly, adLockReadOnly
lngMyCount = oRs("theCount")
oRs.Close
oConn.Close


As I mentionned earlier, the .Open method takes more than 5
seconds where it takes 1 second in Access, and the .Close method
takes more than 2 seconds (maybe 3 seconds) !

Does anyone have an idea ?

Thanks a lot !

Regards

[Arnaud Lesauvage]

Post by Arnaud Lesauvage
The query is a count(*) on 2 joined tables. One of the table is
local, the other one is linked.

Just a quick correction : both table are linked tables !

I must also point out that the connection is using a ServerSide
cursor. This is not explicitely in my code, but it seems to be the
default (I checked the connection's properties, and it uses a
server side cursor).

I should also have pointed out that the mdb file is on a network
share, but when the query is run from within access it is also on
this network share, so the question is : what is the difference
between my code and a native Access query ?

[ralph]

Post by Arnaud Lesauvage

Post by Arnaud Lesauvage
The query is a count(*) on 2 joined tables. One of the table is
local, the other one is linked.

Just a quick correction : both table are linked tables !
I must also point out that the connection is using a ServerSide
cursor. This is not explicitely in my code, but it seems to be the
default (I checked the connection's properties, and it uses a
server side cursor).
I should also have pointed out that the mdb file is on a network
share, but when the query is run from within access it is also on
this network share, so the question is : what is the difference
between my code and a native Access query ?
--
Arnaud

If by "Native Access Query" you mean a Query object you created in MSAccess
then it should run faster as it is already 'compiled' and 'optimized' while a
SQL statement in code needs to be parsed, etc.

What is the host for the VBA? If you are running the VBA inside MSAccess
then just call the Query and not use SQL.

If speed is a problem you might want to check out using DAO, as it can be
occasionally faster. Difficult to give any specifics as I don't know your
versions or your wire.

hth
-ralph

[Arnaud Lesauvage]

Post by Arnaud Lesauvage
The query is a count(*) on 2 joined tables. One of the table is
local, the other one is linked.

Just a quick correction : both tables are linked tables !

I must also point out that the connection is using a ServerSide
cursor. This is not explicitely in my code, but it seems to be the
default (I checked the connection's properties, and it uses a
server side cursor).

I should also have pointed out that the mdb file is on a network
share, but when the query is run from within access it is also on
this network share, so the question is : what is the difference
between my code and a native Access query ?

[Michel Walsh]

Hi,


Access does not close the connection, if you use Access
CurrentProject.Connection, so, does not need to open it either. It seems
your VBA code has to open one, which can be fast, or slow, dependant of the
hardware wiring.

For a firehose (forward only, read only), no need to open a recordset
explicitly. Try something like:

oConn.Open
myCount=oConn.Execute("SELECT COUNT(*) FROM ... ").Fields(0).Value
oConn.Close


and avoid modifying the connection string, since doing it, you are likely to
hit a connection already in the "connection pool" next time you use open it,
which should be then much faster (assuming it was slow to open it the first
time, that is).



Hoping it may help,
Vanderghast, Access MVP

Post by Arnaud Lesauvage
Hi group !
I am querying an Access database in an Atuocad VBA Module.
The query is a count(*) on 2 joined tables. One of the table is local, the
other one is linked.
When I run the query in the Access database, it runs very fast (~1
second).
When I run it in VBA, the recordset is very slow to open *and* very slow
to close !
I really don't understand why !
Dim oConn As ADODB.Connection
Dim oRs As New ADODB.Recordset
oConn.Provider = "Microsoft.Jet.OLEDB.4.0"
oConn.Properties("Data Source") = strDBPath
oConn.Properties("Jet OLEDB:System database") = strSecurePath
oConn.Open UserID:=strUser, Password:=strPass
oRs.CursorLocation = adUseServer '(I added this code to check if server
side cursor was faster, but it doesn't change anything)
oRs.Open "SELECT Count(*) AS theCount FROM A LEFT JOIN B ON ... WHERE
...", oConn, adOpenForwardOnly, adLockReadOnly
lngMyCount = oRs("theCount")
oRs.Close
oConn.Close
As I mentionned earlier, the .Open method takes more than 5 seconds where
it takes 1 second in Access, and the .Close method takes more than 2
seconds (maybe 3 seconds) !
Does anyone have an idea ?
Thanks a lot !
Regards
--
Arnaud