Query optimisation

[bretto]

Hi,

I've been trying to coax a bit of speed out of some of our applications. At present I do most (95%) of queries like this;

mycon.execute(mysql)

where mycon is an ADODB connection object and mysql is a sql query stored as a string variable. I do it this way because I find it convenient and easy to manage.

My question therefore is there any performance advantage to be gained from using a database object query such as;

docmd.OpenQuery("Query1")

I was thinking maybe access does some crafty optimisation/compilation when you save a query that it either doesn’t do in the first case because access/jet don’t know about this query until it is run or access/jet performs the aforementioned "crafty optimisation/compilation" at run time therefore adding an avoidable performance penalty.

Any help/opinion on this matter would be much appreciated!

 

[dcx693]

I'm not sure on this, but I've heard a stored query will always run faster than one that Access need to compile at run-time.

 

[jfgambit]

Well...we have been told over and over that stored Queries run faster than coded ones, but see the below:

Access Development Best Practices
Posted January 7, 2003

Chat Date: October 3, 2002

Chat Participants:

Tim Getsch, Microsoft Access Program Manager
JP Bagel, Site Manager
Mike Wachal, Lead Beta Engineer
Mike Gunderloy, independent Access developer
Sanjay Jacob, Microsoft Access Program Manager
Rita Nikas, Microsoft Access MVP Lead

Q: Should record sources be saved queries rather than SQL statements to take advantage of optimization on saved queries vs SQL statements

A: It doesn’t matter. Access saves the SQL statement as to a hidden query. So there is only a small one time hit the first time you use the SQL Statement.

The above applies to Access 2000 and 2002.

If anyone is interested in the entire article let me know and i can post it.

Jeff

 

[dcx693]

I would definitely be interested. I'm not sure what the exact meaning of this statement is:

Access saves the SQL statement as to a hidden query. So there is only a small one time hit the first time you use the SQL Statement.

What do they mean by "the SQL statement"? A query viewed as a SQL statement? Because that's surely not the same thing as a SQL string that is saved as a literal string in code behind a form or in a field in a table.