Query running very slowly when executed through VBA 2

[AndyCLee]

Hi all,

I am trying to execute a query using Microsoft Access that has linked tables to a SQL server database. When I execute the query in Access, the results are returned almost instantly. However, when I run it through VBA, using a recordset, the recordset takes about 11 seconds to create.

The command I am using for the recordset is:

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

Where strSQL is obtained from the Access query using the following code:

Set qry = CurrentDb.QueryDefs("qryName")
strSQL = qry.SQL

It is only the "Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)" that is slow, the rest of the code seems to work well.

If anyone has any ideas why this is runnig so slowly, or how it can be sped up then please let me know.

Thanks

Andy

 

[KenReay]

Hi

A guess, could it be that the Querydef contains information from the query optimser, which enabled the saved query to run more quickly, but using the SQL string, in the .OpenRecordset method, means the SQL must be parsed and an execution strategy derived each time it is executed

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[AndyCLee]

Ken,

I'm not sure what the Query Optimiser is but I guess it temporarily holds info about queries so they run quicker next time? If I'm correct I tried to test this by closing Access and then running the query before I did anything else. The query still returned almost instantly. Would my test prove anything anyway?

Andy

 

[jsteph]

Is qryName set as Snapshot, or Dynaset type? If you open the querydef object from the dbwindow, then a snapshot will be much faster, but just using the .sql of that same query as a dao recordset with dbOpenDynaset,dbSeeChanges, can be slower.
--Jim

 

[AndyCLee]

Jim,

The query type is Dynaset. Would changing this to Snapshot make it any quicker? If so would I need to change the code that opens the recordset?

Thanks

Andy

 

[jsteph]

Changing to snapshot will typically speed the queries up, often dramatically. However, this is only if the query is opened via the db window--if you just get the .sql of the querydef and use that, then the other querydef properies are totally disconnected from the pure sql.
--Jim

 

[AndyCLee]

Thanks for your answers Jim and Ken. It appears that there is no way to speed up this query. Instead of speeding it up I have opted to show a progress bar instead. At least the users can see that something is happening and won't use task manager to close the app anymore.

Have a star each for your help.

Thanks

Andy