DAO provides an object model that supplies almost every piece of information about a query you might want to know. One piece of information it doesn’t provide, however, is whether or not a query is compiled. Michael and Julianne discuss how you can determine this interesting and sometimes useful property of a query.
The first time you run a query in Microsoft Access, the Jet Engine must “compile” the query into a binary form. This compilation, which can take anywhere from half a second to 6-8 seconds for very complex queries, represents the plan Jet will use to run your query. Any time you save a query, the “saved” version of the query will be decompiled, since you might have made design changes that would change the plan that Jet would use to execute the query. Also, whenever you compact a database, the statistics for the database are updated, and you might find that the original plan for a query is no longer appropriate (for example, the plan that Jet will determine is best for a table with 10 rows might be very different from the plan that’s best for 100,000 rows). Therefore, when you compact the database, queries will be decompiled as the statistics are updated.
Although you wouldn’t usually care about this information, there are times when it’s important. For example, you could create an idle loop in your application that’s invoked when the user isn’t actively using the application — a perfect place to compile queries that are in an uncompiled state. By compiling the queries during the idle loop, your users will be saved a performance hit when they force a compile by using the query. As we’ll discuss later on, there are situations when you want to decompile a query, so you might want to know whether a query is compiled to decide whether it’s worth decompiling it before running.
Is it compiled, or isn’t it?
The problem at this point is that there’s no supported way to determine whether or not the query is compiled (there’s no “IsCompiled” property for the QueryDef object, for instance). However, with some spelunking through the Jet database system tables, we’ve determined where the “compiled” version of the query is stored. It’s kept in the MSysObjects table in the “Lv” field. The complete information in MSysObjects is undocumented and beyond the scope of most books, let alone this article. Suffice it to say that this field will contain a lot of incomprehensible information if the query is compiled, and it will be empty if the query isn’t compiled. So, by running a simple query that filters the MSysObjects table by Type = 5 (5 is used for QueryDefs) and Name = <your query name>, you can check the Len() of the Lv column to determine whether the query is compiled. The function below, FQdfCompiled(), takes the name of a query and returns True or False, depending on whether or not the query is compiled:
Function FQdfCompiled(stQdfName As String) As Boolean Dim db As Database Dim rs As Recordset Dim stSql as String Set db = CurrentDb stSql = "SELECT * FROM MSysObjects WHERE Type = 5" Set rs = db.OpenRecordset(stSql, dbOpenDynaset) rs.FindFirst "Name = """ & stQdfName & """" If Not (rs.NoMatch) Then FQdfCompiled = (Len(rs!lv) > 0) Else ' Query does not exist, do nothing End If rs.Close Set rs = Nothing Set db = Nothing End Function
Note that this function doesn’t do anything in the case where the query doesn’t exist.
The final two remaining questions are:
- How do you decompile a query?
- How do you compile a query that isn’t currently compiled?
Decompiling your queries
Decompiling a query is simple: You just need to open it in design view, save it, and close it. The following function will do this for you:
Sub DecompileQuery(stQdfName As String) DoCmd.OpenQuery stQdfName, acViewDesign DoCmd.Save acQuery, stQdfName DoCmd.Close acQuery, stQdfName End Sub
You might want to turn off screen painting by using DoCmd.Echo, since opening the query in design mode will cause the query to flash on the screen in design mode quickly.
One question you might be asking is, “When would I need to decompile a query?” Usually, of course, the answer is that you wouldn’t. There are, however, many times you might want to do this. For instance, your application might perform queries against tables whose total number of records vary wildly (they might be tables that start off empty and then have many records added to them later). The query plan chosen when the tables are empty might translate into poor performance when the tables are fully loaded with data. By decompiling the query, however, you can avoid this problem by forcing a plan to be regenerated at the time the query is run. For a more convincing reason, look at the section entitled “Practical applications: Access/Graph bug,” later in this article.
Compiling your queries
Compiling a query is a little bit more difficult. In theory, all you need to do is run the query, but in practice, this could be very time-consuming and could even potentially harm your application if it’s a SQL Passthrough or action query. Unfortunately, there’s no supported way to compile a query without executing it (in other words, there’s no way to cause Jet to create a query execution plan without running the query). You can use a function such as the following to compile a query:
Function FCompileQuery(stQdfName As String) As Boolean Dim db As Database Dim qdf As QueryDef Dim rs As Recordset Set db = CurrentDb Set qdf = db.QueryDefs(stQdfName) If (qdf.Type = dbQSelect Or qdf.Type = dbQSetOperation) Then Set rs = db.OpenRecordset(stQdfName) rs.Close Set rs = Nothing FCompileQuery = True Else FCompileQuery = False End If Set qdf = Nothing Set db = Nothing End Function
The function does do a certain amount of checking to ensure that the query is safe to run (that is, a select query), but this might not be appropriate for your application. You’ll want to check to be sure that the query won’t tie up your application, for instance. There might even be action queries that can be run safely.
Practical applications: Access/Graph bug
So far, all of the things we’ve discussed might seem a little esoteric, so perhaps a practical example would be helpful. There’s a bug in Access 95 and Access 97 (partially fixed in Access 97 SR-1) that’s specific to creating charts (using Graph 97 to create charts on your Access form). The bug is very simple — the chart won’t open if the query is compiled by any of the following methods:
- You create a chart on a bound form.
- The form is filtered by the record you’re currently on.
- The filter is enforced by the LinkMasterField and LinkChildField properties.
- The LinkChildField column isn’t included in the SQL statement for the chart.
The reasons for this are very obscure, but they’re basically a side effect of the way the Jet Engine optimizes queries. The first time the query is run/compiled, the field information on the link field is present, and the query can then be filtered by this value; but once the query is compiled and saved, that information is no longer present and you can’t filter by it. The upshot of this problem is that if you use a saved query, the chart won’t work. Access 97, however, always uses a saved query — even when you think it’s not (see the sidebar “Saved Temporary Queries in Access 97” for information about temporary queries).
In SR-1 of Access 97, this bug was fixed by never using the saved query when the chart is run if the query used is a temporary one. Access 95 and the original ship version of Access 97 still fail, though, and even the SR-1 version of Access 97 will fail if you use a permanent saved query.
To fix this problem, you can use the code included in this article by one of two methods:
1. Save the SQL statement as a query (the SQL statement will be in the RowSource property of the control). You can then check to see if the query is compiled using FQdfCompiled, and if it returns True, call the DecompileQuery procedure.
2. Determine the name of the saved temporary query Jet plans to use, and use the same method to decompile it as above. You can use the StTempQdfName function below to get the query’s name:
Function StTempQdfName(iObjType As Long, _ stObjName As String, stCtlName As String) As String Dim stCtlPrefix As String Dim stObjPrefix As String Dim stOut As String Select Case iObjType Case acForm stCtlPrefix = "~sq_c" stObjPrefix = "~sq_f" Case acReport stCtlPrefix = "~sq_d" stObjPrefix = "~sq_r" End Select stOut = stObjPrefix & stObjName If Len(stCtlName) > 0 Then stOut = stOut & stCtlPrefix & stCtlName End If StTempQdfName = Left$(stOut, 64) End Function
This will fix the bug in both Access 95 and Access 97. The bug doesn’t exist in Access 2.0.
Conclusion
Whether you’re trying to get every ounce of performance out of your application or avoid the Access/Graph bug, it can often be useful to know whether a query has been compiled yet. Unfortunately, neither Access nor DAO exposes any way to retrieve this information. However, if you use the technique in this article to mine the information in the MSysObjects system table, you can use this valuable information in your applications.
Sidebar: Saved Temporary Queries in Access 97
The subject of using compiled queries rather than SQL statements (which, by definition, are always decompiled, since there’s no place for Jet to save the query plan) becomes an important performance issue. Imagine, for example, a bound form that has five combo boxes and a subform on it. If only SQL statements are used, you’ll have six queries you’ll define that need to be compiled every time you open the form, as well as one “internal query” that Access uses to link the subform. Assuming 1-8 seconds to compile each query, you can see where performance might suffer. As a result, in Access 2.0 and Access 95, many people use saved queries instead of SQL statements, because the saved queries only take this performance hit when the query is decompiled.
Access 97 introduces a new feature that addresses this issue automatically: It saves a special temporary query that’s then used by the form for all of the ComboBox RowSources and RecordSources. A saved query is even created for the internal queries used for subform and object links (such as those used by Microsoft Graph charts). This saved query works the same way as queries you save yourself; the first time you run the form after you’ve either compacted the database or saved the object, Jet compiles the temporary query. The compiled query is used every other time.
The naming convention for these temporary queries is very consistent, and it’s based on the form/report name and, for controls, the control name. Access uses those names with the prefixes shown in Table 1, truncating the resulting query name at 64 characters, if required.
Table 1. Object types and their temporary query prefixes.
Object Type | Prefix |
Form | ~sq_f |
Report | ~sq_r |
Form Control | ~sq_c |
Report Control | ~sq_d |
For examples of how the temporary query naming works, you can look at some of the examples for various objects in Table 2. You can also look at the StTempQdfName function in the article to obtain a name.
Table 2. Examples of temporary query names.
Object | Name of temporary query |
Form named frmBletch | ~sq_ffrmBletch |
Listbox named ListOfStuff on Report named Fitch | ~sq_rFitch~sq_dListOfStuff |
Chart named MyChart on Form named Wobble | ~sq_fWobble~sq_cMyChart |
Report named Pagorah | ~sq_rPagorah |
Control named VeryExtremely LargeControlName on Form named VeryVeryVeryExtremely LongFormName | ~sq_fVeryVeryVeryExtremelyLong FormName~sq_cVeryVeryExtremely LargeCon |
You don’t usually need to use the methods in this article to deal with the temporary queries, because:
- When you save the form, the temporary query won’t be used; Access will recreate the query and recompile it.
- When you compact the database, the temporary queries are deleted (this is a good reason to compact your database before you give it to customers, since the query plan used by your test data will likely not be the same as the one you’ll want Jet to use for the customer’s data).
One unfortunate example where these methods can be needed is shown under the “Practical applications: Access/Graph bug” section of the article.
Unfortunately, there’s no supported way to find out whether or not a temporary query is one that’s actually used (in other words, there’s no “NeverBeforeRunSinceLastSaved” property for forms and reports). If you want to make sure these queries are compiled, you’ll have to either move them to saved queries yourself or open the form in a hidden state to execute the queries (if you do this, make sure the startup code for your form doesn’t perform any actions you don’t want to happen).