Query or Query Builder

[jsanders]

Which has higher performance at run time, saved queries or using query builder on a form?

 

[jsanders]

I'm probably asking this question wrong.

Is ther any difference between a saved query and one that is saved with a form?

 

[Jon K]

See Pat's answer in this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=84343
.

 

[jsanders]

Thank you Jon,

I looked up Pat answer as you suggested.

I looked up Querydef in the Access help, it doesn’t even know the word; typical MS help.


When you use the query builder function on a form, is that creating a static or dynamic sql statement?

In other words is a query that is saved within a form a saved query or is it processed each time the form runs?

 

[pono1]

Unfortunately, there is no one-size fits-all solution when it comes to optimizing the performance of your queries (and applications) since there are so many factors involved: network traffic and bandwidth (assuming you are developing within a client-server model -- front-end and back-end), column indexes (very important), processor speed, memory, the actual SQL (i.e., using Count(*) is supposedly faster than using Count(ColumnName)) , the columns returned in the query (you will always want to return the minimum amount of columns), the type of data queried (large strings? integers?), whether you are using joins (sometimes this will result in faster queries, sometimes not), and more...

The Access Developers Handbook (a book worth buying if you see yourself developing with Access for the long-term) devotes an entire chapter to application optimization -- in a nutshell it says that saved queries are generally faster but not always: If your query returns a vastly different number of rows depending on the criteria, a saved query might be slower than a temporary query (SQL you manually write in the code window)...

Of course the only way to know what works the best in your particular application is to test it...

Regards,
Tim

 

[jsanders]

And finally, to answer the original question, if you create SQL strings in the RecordSources of forms and reports, Access actually turns them into stored querydefs. Access generates a name based on the form/report object and prefixes it with the tilda "~" which prevents the query from showing in the querydef window. If you open the MSysObjects table, you will see the queries there.

Access help is funny (not ha-ha funny but strange funny). In Office, help is segregated into "interface" help and "programming" help (someone at Microsoft has the misconception that Access is like the rest of office). So, you get different results if you click on help from the database container window than you get if you click on help from the VBA window. The other help trick is to use the table of contents to drill down (also different from both windows). The drill down method is the only way to get to all the useful help entries for DAO/ADO and SQL.

Thank you Pat.


So basically if you like using saved queries, it’s just as well to save them with the form. Keeping your querydef window from over filling.

 

[jsanders]

Since we were talking about this I have created about 105 queries in the same database not including Record Source or Row Source queries and I have discovered some interesting management issue I wanted to share.

First I love saved queries in any form (especially since I don’t know jack about writing SQL).

Anyway I have found that if you create and save the root queries in Query Def form then later add them to a Record Source; you can combine them with other Root queries and do all of your criteria statements in the Record Source query. That way you get maximum flexibility from your saved Queries and it requires less duplicate queries.