Queries On Steroids ... Part IV

Query analysis tools for the stout of heart.

Let me begin by confessing that I am not qualified to write this article. Everything you read below comes from Internet research, primarily from two sources:

I'd like to give special thanks to the authors of the Access Developer's Handbook, and to the professionals that freely give of their time and expertise on the many user groups, including but not limited to Lyle Fairfield, David Fenton, Albert Kallal and Jim Dettman, whose posts proved most helpful in researching this article.

It has, for some time now, been a goal of mine to understand how to analyze queries in both Microsoft Access and SQL Server. I've bought a few books and even started reading them on more than one occasion. Nevertheless, for some reason, I just can't seem to follow through and really devote the time to it that would be required to become proficient at reading the results of analysis tools. Therefore, this article will cover the tools available and to a limited extent, my understanding of how the results may be interpreted. If you need to analyze your queries, you're going to have to slog through the results yourself and try to make sense of the data returned.

The disclaimer out of the way, let's plunge headlong into two undocumented and unsupported Jet Optimization tools in Microsoft Access that may help you in identifying performance bottlenecks in your queries:

• Showplan option
• ISAMStats method

Showplan Option

Showplan is an undocumented option that, once enabled, creates a file named showplan.out where query execution plan information is appended each time a query is run. The location of this file can be a bit elusive, but if you don't find it in the same folder as the currently active mdb file, then look in your My Documents folder. Only one file will be created per folder. Results from all Access database queries are appended to the same file and over time, it will grow large.

Before we describe how to enable this feature, we're obliged to supply this warning:

To turn on Showplan, you simply edit one of the Microsoft Jet registry entries. If you are comfortable with the registry, then simply navigate to the following Windows Registry key using Regedit and set the JETSHOWPLAN key to the text value of 'ON'
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Debug.

While I'm comfortable with the Registry, I prefer to create a pair of .reg files for managing this setting with the double-click of my mouse. The following scripts were supplied by the authors of the Access Developer's Handbook, an indispensable resource for Access professionals. Simply copy and paste each of these scripts into Notepad and save them as .reg files.

JET ShowPlan ver 4.0 ON.reg

  Windows
  Registry Editor Version 5.00
  [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Debug]
  "JETSHOWPLAN"="ON"

JET ShowPlan ver 4.0 OFF.reg

  Windows
  Registry Editor Version 5.00
  [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Debug]
  "JETSHOWPLAN"="OFF"

Note: You may have version 3.5 of the Jet engine installed. If so this registry key will be found in the following node:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Debug

Additional Note: For Office 12 (Access 2007), the key is located here, but is still undocumented.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Debug]

Once enabled, you need only open a query to begin logging. (Since this file grows rapidly, I found that, for the sake of this article, it was necessary to save off its contents and delete the showplan.out file from time to time.) I created a simple query to select author name and state from the pubs authors table, where state='CA'. Below are the results, before and after an index was added to the state field.

First Query ... single table with where clause:
   SELECT authors.au_lname, authors.au_fname, authors.state
   FROM authors WHERE authors.state='CA';

--- Query1 ---
Author note: most recent query run is on top
- Inputs to Query -
Table 'authors'
- End inputs to Query -
01) Restrict rows of table authors
      using index 'state'
      for expression "authors.state='CA'"

--- Query1 ---  
Author note: First run without index on state column
- Inputs to Query -
Table 'authors'
- End inputs to Query -
01) Restrict rows of table authors
      by scanning
      testing expression "authors.state='CA'"

Second Query ... with primary key links and group by clause:
   SELECT stores.stor_name, sales.ord_date, Sum(sales.qty) AS [Total Books Sold]
   FROM stores INNER JOIN sales ON stores.stor_id = sales.stor_id
   GROUP BY stores.stor_name, sales.ord_date;

--- Query2 ---
Results after adding Primary Keys to tables
- Inputs to Query -
Table 'sales'
    Using index 'storessales'
    Having Indexes:
    storessales 21 entries, 1 page, 6 values
      which has 1 column, fixed
    PrimaryKey 21 entries, 1 page, 21 values
      which has 3 columns, fixed, unique, primary-key, no-nulls
Table 'stores'
- End inputs to Query -
01) Inner Join table 'stores' to table 'sales'
      using index 'sales!storessales'
      join expression "stores.stor_id=sales.stor_id"
02) Group result of '01)'
--- Query2 ---
First query run without primary keys defined
- Inputs to Query -
Table 'sales'
Table 'stores'
- End inputs to Query -
01) Sort table 'sales'
02) Inner Join table 'stores' to result of '01)'
      using temporary index
      join expression "stores.stor_id=sales.stor_id"
03) Group result of '02)'

Now comes the hard part ... interpreting the results. I've marked what I believe to be the critical results above with red text. In the first query example, we are informed that rows were returned as a result of "scanning." This is bad. We want the query optimizer to find an index, as it does in the second run (top result) after having added the state index.

In the second query example, linked tables had no primary keys defined, and no indexes. The first run (bottom result) shows that a temporary index was used. I suppose it's good that Access created a temporary index, but I suspect it's better to have created the indexes myself. The second run takes advantage of the 'sales!storessales' index that was created when I added a primary key to Store, and created a relationship to the sales table.

These examples are simple and obvious. To test something more complex, I cleared the showplan.out file and launched my most complicated application. After the splash screen and startup functions, I selected a search from the menu and loaded a complex form with subforms. From that form, I double-clicked to drill to an additional form. The results? Showplan grew to 77 kb which took up 77 pages in Microsoft Word. A total of 216 entries were made to Showplan.out in less than 2 minutes.

What does this mean? I don't know. I'd have to pore over all these results to identify each and decide which, if any, could be optimized. Clearly, this is not the way to use the showplan option.

One of the newsgroup posts I found was from an accomplished developer named David Fenton. He posted the following to Comp.Databases.MS-Access on 2002/01/28 ...

It's an eye opener. I had one app where running a single normal everyday process caused the log file to grow from 38K to nearly 2MBs (it was updating fields in a temp table using functions that used DAO to get their values; every call to those DAO functions got listed in Showplan, in one case for a couple of thousand records in the updated table!). I completely re-architected that component of the app, and the users are delighted.

ISAMStats method

If it can be said that I know little about Showplan, I know even less about ISAMStats, but having run across it in my research, I'm compelled to include it for those of you who wish to take your query analysis to another level. Again I emphasize that the code below is something revealed by my research and credit should go to the two references at the top of this article.

The ISAMStats return metadata such as disk reads and writes. The DAO method for retrieving this information is shown below. I also created a disposable function to run DiskStats against two similar queries. The first is the query analyzed above named Query1. It simply selects a list of authors who live in the state of CA. After having run the DiskStats 3 times and recording the results, I changed the query where clause to allow the user to enter a state.

Option Compare Database
Option Explicit
Public Function DiskStats(bolReset As Boolean)
  On Error Resume Next
   
  Dim lngTemp    As Long
  If bolReset = True Then
    DBEngine(0).BeginTrans
    DBEngine(0).CommitTrans
    lngTemp = DBEngine.ISAMStats(0, True)
    lngTemp = DBEngine.ISAMStats(2, True)
  Else
    MsgBox "disk read = " & DBEngine.ISAMStats(0) & vbCrLf & _
           "disk write = " & DBEngine.ISAMStats(1) & vbCrLf & _
           "cache read = " & DBEngine.ISAMStats(2) & vbCrLf & _
           "read ahead = " & DBEngine.ISAMStats(3) & vbCrLf & _
           "locks placed = " & DBEngine.ISAMStats(4) & vbCrLf & _
           "locks released = " & DBEngine.ISAMStats(5) & vbCrLf
  End If
End Function
Function TestDiskStats()
  Call DiskStats(True)
  DoCmd.OpenQuery "Query1"
  Call DiskStats(False)
End Function

I can't say the results were surprising, because I really didn't know what to expect. What's more, I can't interpret them for you either. For that, you'll have to check MSDN or post your question to the newsgroups where you will find someone who knows more about it than I do. However, I can analyze the results and see that where the state was 'hard coded' to CA, it required fewer disk reads, fewer cache reads and fewer locks. So, while I don't necessarily understand what each attribute represents, I can use them for making comparisons. Run the stats, modify the query and re-run stats to determine which method is more efficient.

Does this help?

I had promised 4 installments of Queries on Steroids, including a discussion of Showplan. I'm not sure how helpful it is to me, let alone my readers, but as usual I'd be happy to hear from you. Perhaps you can add to this discussion and share your techniques on analyzing and interpreting the results of Jet Showplan and ISAMStats.

» See All Articles by Columnist Danny J. Lesandrini