Speed up queries

[unclesvenno]

Hello All,

Can anyone tell me if a query will execute quicker on linked tables on a seperate server if the queries are generated as "Queries" via the objects panel in the Access Database Window or as a String within the VBA code? If so what is the difference?

Thanks,
Uncle Svenno

 

[PHV]

When a saved query is executed for the first time, it is "compiled", ie the optimizer build and save a query plan.
Then when the query is executed again the plan is already build and the query will execute faster.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[unclesvenno]

Hi PHV,

Thanks for taking the time to answer my question. Unfortunately I'm a little slow on the uptake sometimes, do you mean by this that it doesn't really matter either way? I am making an mde file which to my understanding compiles the code. So if I create an SQL query as a String variable in the code of an event as to creating the query through Access (ie with the Wizard or manually) they'll both be compiled and therefor they execute at the same speed?

Thanks again,
Uncle Svenno

 

[JonFer]

Compiling your DB will not optimize the SQL string in a module. However, if the linked tables are in a separate RDBMS, your SQL string sent as a pass-through query would probably run faster than an Access query unless it was also a pass-through.

John

http://www.interautomateconsulting.com