MS Access – How to optimize queries so the database runs faster
This post lists common mistakes that lead to poor query performance. For this, we assume that you are using relational tables with primary keys, foreign keys, and indexes for the fields you are searching and sorting on.
Use SQL instead of VBA
In Access, Structured Query Language (SQL) and Visual Basic for Applications code (VBA) can be used. This extends the power of the query engine in Access (which is called JET or ACE), but it doesn’t make sense to call VBA if SQL can do the job.
Is Null / IsNull()
Is Null is native SQL and IsNull() is a VBA function call. Therefore, it only makes sense to use Is Null in your queries.
IIf() / Nz()
text
Domain aggregate functions
text
Use Indexes in your Expressions
If the database can use an index to select records or sort them, the query will be much faster. You can find two examples for that under “Calculated fields criteria” and “Sort by concatenated fields”.
Calculated fields criteria
In the example you see on the right, the Year() function looks simpler, but this makes the query slower to execute than in the other example. When using Year(), a VBA function call is made for each record, which gets a result and then searches the entire table and eliminates the records with other years. Without Year(), the index is used to immediately select the records for the matching year, which is much faster.
A general tip for optimising queries is to avoid VBA calls, especially for criteria or sorting, so that indexes can be used instead.
Sort by concatenated fields
For example, if you use a combo box that is used to select people by name, the fields for first name and last name are concatenated, then do not sort by the concatenated field! It is better to sort by both fields so that the indices on the fields can be used to perform the sorting.
Optimize Totals Queries
Simple queries, are already very fast because of the JET query optimizer. Still, you should get the best possible out of your queries to keep them fast.
WHERE / HAVING
Totals queries with a GROUP BY clause can contain a WHERE clause and a HAVING clause, of which the WHERE clause is executed before the aggregation and the HAVING clause is executed after, i.e. after the totals have been calculated. Therefore it is useful to define the criteria using the WHERE clause. You should use the HAVING clause only if you want the criteria to be applied to the aggregated totals.
In the MS Access query designer, you must make sure that the criteria are inserted in the correct place. When a field is added to the design grid, Access sets the Total row to Group By. When you enter the criteria below that, they are added to the HAVING clause. In order for the criteria to be added to the WHERE clause, you must add the field to the grid again, and select Where in the Total line.
FIRST / GROUP BY
Access offers Group By in the Total row when you add a field to a Totals query. Access then must group on all the fields that are added. Such queries can be optimised by selecting First instead of Group in the Total row below the other fields. With First, the value of the first matching record is returned without grouping by the field. When grouping by a memo (in the example Notes), only the first 255 characters are matched and the rest is chopped off. But if you use First instead of Group By, the whole memo field is returned. Thus, this optimisation is not only more efficient, but also solves the problem of truncated memo fields.
However, there is a disadvantage to using First and that is that the fields have to be aliased.
Other Optimizations
- For queries with multiple tables, it is useful to use JOINs instead of WHERE clauses because JET executes them faster.
- Do not use multiple tables on the outer side of a JOIN, as JET can misinterpret this.
- Use subqueries instead of domain aggregate functions.
- Use stacked queries to specify the order of queries to be executed. This is important because JET does not consider the parenthesis in the FROM clause when compiling an execution plan.
- By returning as few fields as possible, memory usage can be optimized and disk reads can be reduced. However, the key fields should be included so that JET can quickly identify the records.
- Simplify the criteria by building query strings dynamically, espacially where the user will enter only a few of the criteria options you give them.
Conclusion
As you read in this post, there are many things you can change to improve the query performance in MS Access.
If you would like to read more about MS Access, please read our guide : ‘Why is my query not updateable?‘
To read more about MS Access in general, view our ‘What is Microsoft Access‘ post or go to Microsoft.
Arrow Design, based in Dublin, Ireland, provides quality website design services in Dublin and beyond at affordable prices. If you would like help with implementing the above code, or any wordpress website development project, contact us. We love website design and it shows! We provide custom wordpress plugin development, website design training and lots more.
We do it all, so you don’t have to!
…We do more, so you can do less 🙂