Optimising: Changing form sources to queries... why?

[munkee]

I have just been reading a website which suggests in order to improve the performance of a database the recordsource of a form should be based on a query.

Now I know it is based on a sql string i.e. SELECT Person from tbl.Security etc however I don't understand the benefit of having the source set to being a query. Surely a query is just running a sql statement?

http://www.myaccesspro gram.com/Tips_Best_Pract ices.html

"To increase performance of form and drop down list loading, use stored queries for your record sources because they have already been optimized by Access. Note: the Access form wizard will create Select SQL statements for record sources, and these are not optimum. "

The article also mentions not using memo type fields over a network. Am I correct in thinking the closest you can get to a memo field is 250 characters? As I feel this would be too small for certain fields in my database, which will be being used across a network.

[Stewart Ross]

It is certainly good practice to base a form on a query and not a table, though in my opinion this is not a performance matter. As far as I know Access queries are not 'compiled' into an optimised form in advance of being run, and I have not found any performance advantage to one mode as against the other.

An advantage of basing a form on a query is that you can vary the default sort order of the query to what your users need. The default sort order of the data presented in a table is determined by the primary key and any indexes you have set. These may or may not reflect the order you want to present the data to users.

Another advantage is the ability to include calculated fields and joins to other tables, giving much more flexibility in what you present to users without using DLookup and other relatively slow functions.

I've not had any difficulty using Memo fields across a network, although mine do not store thousands of characters at a time. The next nearest equivalent, the Text field, is limited to 255 characters, which is too small for many applications.

The best advice I can give is to concentrate on getting the database design right - normalising the tables and ensuring that proper relationships are set right from the start. Tweaks will follow later, but the speed differences that arise from good design are orders of magnitude more than you get from swapping a memo field for a fixed-length text field, for example!

-Stewart

[NeoPa]

I was under the impression that QueryDefs (saved queries in Access) actually were optimised if they are saved after running. Whether this is a form of compilation, or more likely, just the saving of a few notes as to the costs involved running the separate constituents of the SQL, I don't know.

I'm pretty sure this comes into play more and more as the complexity of the record sources used increases. I can't dig it up now, but I vaguely remember reading advice saying to save any QueryDefs again whenever the fundamentals of the underlying data changes drastically.

EG. If, during development and testing, you used a table with a single record in it, then this table was populated much more fully during the live running, then this would be a good time to resave the QueryDef, as these notes could be updated to reflect the newer situation.

[ADezii]

Queries are Compiled and Optimized the first time you run the Query, and are not recompiled until you resave and rerun the Query. Jet makes Optimization decisions based on the size of the Source Tables and the presence of Indexes when you compiled the Query. You should force Jet to recompile a Query after you've altered Indexes, or significantly changed the Query in any manner. You can force recompilation by opening the Query in Design Mode, saving it, and then reexecuting it.

[munkee]

Thanks for all the replies I have been seeing quite a bit of info on the net regarding keeping your queries up to date and optimised.

Going back to my original post. I have read that using a query as a record source for a form will mean under certain circumstances you will not be able to update the underlying tables.

For example I have a form which I use to display records as a single form, it has mutliple tabs within and sub forms located on these tabs for various operations. The form recordsource is linked to my main table. I can add a new record using this same form and I also use it when I am using other forms to display overviews of date which I can then click a "view record" button and load up this form with the primary key filtered to the correct record.

With this being said would I benefit from having my form sert up with a query. I'm just not understanding how I can provide a query to a form which is fundamentally spewing out my data from my main table, or see any enhancements.

[NeoPa]

Updatable queries support updates in the forms just as tables do. Clearly you wouldn't want to use a non-updatable query for a form where updates are required. Many do, then come here to ask why it's not working for them.

That said, I suspect the original advice was more about using compiled QueryDefs rather than simple SQL string queries.

Lastly, basing a form on a table can be considered to be pretty well synonymous with a query (or QueryDef) of the form :

SELECT *
FROM   [Table]

[munkee]

Ah I think I finally understand what I was reading now. I got the complete wrong end of the stick. Basically it is saying if I end up with any Select statements in my form recordsource I would be much better off making a built in query within access and then referencing this static query as the forms recordsource. Primarily because stored queries are already optimised for the size of the data whereas the straight sql string is not.

With this being said I assume this preliminary optimisation out weighs the fact that some tables will have increasing data therefore if I do not decompile/recompile my saved queries often they could end up running as slugish as a Sql string in the form recordsource?

Also, for example if I have my recordsource in a form set as my main table for instance: tblLog and within this form I have sub forms which are linked to tblLog via the primary key. Would I benefit creating a stored query which is = to the exact same fields present within my tblLog?

Hopefully I have explained the last part sufficiently.

Now for my final question!. Which method is the better optimised in this situation:

Using a dynamic where clause creator such as that found within the Allen Browne advanced form filter. Which would produce the better optimisation in the following scenarios:

-Filtering a form recordsource that is set to a table

-Filtering a form recordsource that is based on a query

-Utilising the where clause to be appended to the end of your SELECT string within vba which is then set to the forms recordsource?

Any comments are welcomed, thanks for the feedback so far it has sparked me off to look deep through my access book and the internet to uinderstand whats going on.

[NeoPa]

Munkee: With this being said I assume this preliminary optimisation out weighs the fact that some tables will have increasing data therefore if I do not decompile/recompile my saved queries often they could end up running as slugish as a Sql string in the form recordsource?

There are two elements to this.

1. A QueryDef would save in as much as the compiling and preparation would be absent.
2. If it's out of date, the execution of the query may be slower than otherwise.

Many factors determine whether the performance would make up for the guaranteed delay in doing the preparation.

Munkee: Also, for example if I have my recordsource in a form set as my main table for instance: tblLog and within this form I have sub forms which are linked to tblLog via the primary key. Would I benefit creating a stored query which is = to the exact same fields present within my tblLog?

I'm not sure where you're going with this.

Subforms are usually linked to the main form by link fields. They wouldn't typically use the same record source. As such, I can see no potential for benefit. Sorry if I'm missing the point.

Munkee: Which would produce the better optimisation in the following scenarios:

I would say the best approach is to apply a filter rather than to redo the record source itself. As we've already discussed, such SQL would need to be re-prepared before execution. It also makes the process more complex.

If there is a table ready and available, I'd use the table. If for no better reason than simplicity. I'm not aware of any benefits of using a QueryDef over a table. I'd very much doubt the performance would be effected noticeably though. You could test it if you're interested.

[munkee]

When distributing front ends to users and working with a database across a network what is the general method of ensuring querydefs are kept up to date? I could only assume there is a redistribution at some point of the front end or users are asked to compile the querydefs again.

[NeoPa]

Re-preparing a QueryDef is only required when the data changes fundamentally.

It's about optimising the making available of the data. I can't even think of a good example scenario for when the result of the preparation might change. I just have a feeling that when a table has very (very) few records, it may be suitable to link it into another table on the fly. The same situation where both tables have more than a certain threshold of records might require creation of a temporary indexed table that is then used further down the line in the execution of the QueryDef.

Does this :

1. Help you understand the sorts of things involved?
2. Help you to realise how drastically the situation would need to change before the preparation would return a different result?