Performance III - quick queries

The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.

Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.

Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.

Subdatasheet Name ■■■

The default setting for the Subdatasheet Name property of a table is [Auto]. Set it to [None] for all your tables that don't have or need sub-datasheets.

Access Help topic: SubdatasheetName Property

Minimize traffic ■■

Minimize the amount of data returned by your queries. Do this by structuring then to return only the fields and records needed. Reduce the number of fields returned, and put constraints on the query by using the WHERE clause. The exception to this is when doing a simple count of records, when SELECT COUNT(*) is fastest (since it doesn't have to check for Null values in a specified field).

Don't force local query processing ■

Don't use query constructs that cause processing to be done by Access on the local computer. The following query operations force the Jet database engine to perform local data processing:

• Join operations between table that are linked to different data source (i.e. a join between a SQL table and a linked Access table).
• Join operations based on query that uses the DISTINCT keyword, or a query that contains a GROUP BY clause.
• Outer joins that contain syntax that is not directly supported by the remote database server.
• The LIKE operator used with Text or Memo fields.
• Multi-level grouping and totalling operations.
• GROUP BY clauses that are based on a query with the DISTINCT keyword, or the GROUP BY clause.
• Crosstab queries that have more than one aggregate, or that have field, row, or column headings that contain aggregates, or that have an ORDER BY clause.
• User-defined functions, or functions that are not supported by the remote server.
• Complex combinations of INNER JOIN, LEFT JOIN, or RIGHT JOIN operations in nested queries.

Indexing... again ■■

Try indexing any fields in the query that are used to set criteria, and those that you use for sorting. Also, index the fields on both sides of a join (although if you create a relationship between joined fields, the index is automatically created). As mentioned elsewhere on this page, it is possible to over-index, so tread carefully!

Access Help topic: View or edit indexes

... and use Primary Key indexes whenever possible ■

Because Primary Key indexes do not allow null values, they can be faster than other indexes.

Access Help topic: About primary keys

And for non-indexed fields... ■

Try to avoid the use of the BETWEEN, AND, IN and = operators on non-indexed fields.

Only join with compatible types ■

This sounds like dating advice, but try to ensure fields that are joined in a query have the same, or compatible, data types - your queries will thank you for it, and so will your users. For reference, an AutoNumber field is a Long Integer type.

Avoid the Immediate If ■

The IIf (Immediate If) statement can be slow as it does not employ "short-circuit" evaluation, i.e. both sides of the expression are always evaluated.

Access Help topic: IIf Function

Avoid too many Functions ■

Avoid calculated fields, or fields that use expressions in subqueries - slowness is an inevitable by-product.

Use Domain Aggregate functions sparingly ■■

Things like DMax and DLookup are generally slow, so avoid them where possible. Unless desperate, try not to use them to access data from a table that is not already in your query.

Fixed column headings in crosstabs ■

Wherever possible, use fixed column headings in your crosstab queries.

If in doubt, compile! ■■

When your db is compacted its data statistics are updated. When you then run a query, these updated statistics are compiled in the query's execution plan. This sequence of events results in the fastest possible query. Before you deploy your application, compact the database, then force each query to be recompiled (which in turn causes it to use the latest statistics) - do this by opening it in design view, saving it, and then running it.

Rush-more! ■

Jet uses Rushmore query optimization where possible. Rushmore can be applied to queries run on native Access data, and on certain linked table types. To ensure that Rushmore optimizations are used whenever possible, create indexes on all fields that are used to restrict a query's output. Queries that don't contain indexes on fields used in their restriction clauses cannot benefit from Rushmore.

If you have 1:n joins... ■

...in a query with a restriction, try moving the restriction to the other side of the join. If the restriction is on the many side, try moving it to the one side. Compare performance for both versions, and choose the fastest (obviously).

The Sub-Way: sub-queries instead of joins ■

If you have a slow query with a join, try replacing the join with a sub query. In some cases, this may run faster.

Querying users and security groups

Unbelievably, it is possible to part with your hard-earned cash for commercial tools that, amongst other things, present a summary of user/group membership in an easy-to-interpret way. People can get away with it because these tools are much easier to use and interpret in this respect than Access's integrated view of the same information, which constrains you to looking at either all the members of one group or all the group memberships of one user. Even I, the most ardent of Access-philes, have to admit that sucks.

So what's the solution? Part with your hard-earned cash? Never! Instead, let's delve deeper into your system.mdw file. For the uninitiated, this is the workgroup information file that controls the security information for the database in question. There is a default workgroup information file that is used for all databases unless you specify otherwise via the /wrkgrp command line parameter. But I digress...

Take a look in the system.mdw file - I find my default version at C:\Program Files\Common Files\System\system.mdw (I'm using Access 2000, by the way). Equally, you can open up any copy of a system.mdw file that you might be using for other databases. Either way, you'll find a couple of tables in there, MSysAccounts and MSysGroups. If you can't see these: go to Tools, Options and check System Objects on the View tab. You'll also find some queries, notably MSysGroupMembers and MSysUserMemberships which are on the way to what we want but still constrain you to the one user/group at a time view mentioned earlier.

So where were we? Well, as the name suggests, the MSysAccounts and MSysGroups tables store info on the users and security groups in your workgroup information file, so we can write a neat crosstab query to provide a grid of which users belong to which groups, like so:

TRANSFORM Count(MSysGroups.GroupSID) AS CountOfGroupSID
SELECT MSysAccounts.Name
FROM (MSysAccounts INNER JOIN MSysGroups ON MSysAccounts.SID=MSysGroups.UserSID)
INNER JOIN MSysAccounts AS MSysAccounts_1 ON MSysGroups.GroupSID=MSysAccounts_1.SID
WHERE (((MSysAccounts.FGroup)=0) AND ((MSysAccounts_1.FGroup)<>0))
GROUP BY MSysAccounts.Name
PIVOT MSysAccounts_1.Name;

Go on, give it a try! It's pretty tidy, isn't it? One thing to note - you don't have to do this in the system.mdw - if you'd rather integrate this crosstab in your database application, simply create links to the two tables in the relevant system.mdw file (File, Get External Data, Link Tables...) and then you can create the query above in your database file.

Returning the top n values

Ever wanted to return a specified number of the highest values in a field? Well, as with so many things in Access there are two ways of doing this. At least they both boil down to the same thing.

The first way is the easiest if you don't like to muddy your hands with SQL too much. Open the query in design view and bring up the Properties window. Set the TopValues parameter - this can take an integer value (5, 10, 25, etc) or a percentage (10%, 20%, and so on) - in turn, these values constrain the data returned by the query. Then sort the field you want to return the top values of in descending order. Easy, eh? But I can almost hear you asking, what about a “bottom values” property? Well there's no such thing... instead, simply sort the field you want to return the bottom values of in ascending order and then set the TopValues value as described above.

The other way of doing this is to get down and dirty with some SQL, and essentially do manually what the TopValues property/sort order combo does for you automatically. Here's an example - suppose you want the names of the top 10 students from the class of 1992. Here's the SQL:

SELECT TOP 10
Forename, Surname
FROM tblStudents
WHERE GradYear=1992
ORDER BY FinalGrade DESC;

Here's another example - this time we're going to pull off the names of the bottom 10% of students from the same year. Here's the SQL - note the use of the PERCENT keyword and the change in sort order:

SELECT TOP 10 PERCENT
Forename, Surname
FROM tblStudents
WHERE GradYear=1992
ORDER BY FinalGrade ASC;

Too easy, right? But there are a few important things to note here though. If you do not define a sort order (the ORDER BY clause if you're using raw SQL), the query will return an arbitrary set of n records which is not what you want at all! Secondly, the TOP predicate does not choose between equal values. In the first SQL statement above, if the twenty-fifth and twenty-sixth highest students have the same final grade the query will return 26 records. And finally, the value that follows TOP must be an unsigned integer data type.

Querying system objects

How much do you know about MSysObjects? Well, it's a hidden system table that includes information about the contents of your database. As such, you can use it to see if objects exist, when they were created, and so on. These are the key fields in the MSysObjects table:

• Name - the text name of the database object
• DateCreate - the date and time the object was created
• DateUpdate - the date and time the design of the object was last modified
• Connect and Database - used for storing connection information for linked tables
• Type - numeric value indicating the type of object, as follows:
• Form: -32768
• Report: -32764
• Macro: -32766
• Module: -32761
• Local table: 1
• Local database: 2
• Collection (e.g. forms, reports, modules, etc): 3
• Query: 5
• Linked table: 6
• Relationship: 8

So, for example, to return the names of all tables created since 1st January 2001, the SQL would be something like:

SELECT Name
FROM MSysObjects
WHERE Type=1
AND DateCreate>=#01/01/2001#;

Note that a Type value of 5 also returns all SQL queries embedded as the recordsource in forms and reports, as well as standalone queries.

Union queries

Union queries offer a handy way of merging the results of two independent tables or queries. Here's the SQL - in this example, data from a staff table is being combined with data from a supervisors table to show all employees for a given region:

SELECT StaffId as IdNumber, Forename, Surname
FROM tblStaff
WHERE Region='South'
UNION SELECT SupervisorId, Forename, Surname
FROM tblSupervisors
WHERE Region='South'
ORDER BY IdNumber;

There are a couple of important things to remember when using UNION - these are:

• by default, no duplicate records are returned when you use a UNION operation; however, you can get round this by including the ALL predicate straight after the UNION keyword. This also makes the query run faster;
• all queries in a UNION operation must request the same number of fields; however, the fields don't have to be of the same size or data type;
• use aliases only in the first SELECT statement because they are ignored in any others. In the ORDER BY clause, refer to fields by what they are called in the first SELECT statement (cf. IdNumber in the example above);
• an ORDER BY clause can be used at the end of the last query argument to display the returned data in a specified order;
• a GROUP BY or HAVING clause can be used in each query argument to group the returned data.

The vagaries of dates in queries

Consider this situation - you've written a query that returns all records that fall within certain date parameters. When you run the query it returns the correct data, and everything is hunky-dory. Now let's say you're building the same query programmatically; first you have to remember that dates need to be contained within hash symbols in the same way that strings have to be in quotes, but that's not the real issue. If you're not from the USA, chances are you local date format will be something like dd/mm/yyyy, whereas it is mm/dd/yyyy in the States. Did you know that when you use VBA to concatenate dates into an SQL string, you must use a standard U.S. date format, regardless of your PC's regional settings.

Consider this example, in which a form contains a field Param for entering a date in, and a command button cmdOK which, when pressed, displays a count of staff who started work after the specified date. The code for the command button would be something like:

Sub cmdOK_Click()
    MsgBox DCount("*","tblStaff","StartDate>#" & Me.Param & "#")
End Sub

Looks okay doesn't it? But, at best, this will return the wrong results and, at worst, could crash your code. The date has to be parsed in US format, like this:

Sub cmdOK_Click()
    MsgBox DCount("*","tblStaff","StartDate>#" & Format(Me.Param, "mm/dd/yyyy") & "#")
End Sub

In the unlikely event that this still gives problems, use the following to completely negate the possibility of any ambiguity:

Sub cmdOK_Click()
    MsgBox DCount("*","tblStaff","StartDate>#" & Format(Me.Param, "dd-mmm-yyyy") & "#")
End Sub