support@ascassociates.biz

sales@ascassociates.biz

Query Statistics & Analysis Of Performance - Comments On Access & SQL Server

Access is like a black box when it comes to figuring out what is happening, you write your SQL, it executes, and you wait for the answer. If you have to wait too long you may start asking questions like :-

• What is it doing ?
• Can it do things a bit faster ?

There are some general guidelines that you can follow when developing your SQL :-

• The more your SQL uses complex VBA Functions in the queries the slower they will be to execute, this is logical because the function must be applied to each row. This is also often unavoidable.
• The less precise the SQL where clause is, the slower it will be. Searching a text string for "fred" is faster than searching for "Fred*" which is faster than searching for "*Fred*". the introduction of 2 wildcards "*fred*" means all positions in the string must be searched. This is down to how you design your system.
• Performance can be improved by creating Indexes on commonly searched columns, but be warned that adding too many indexes will create an overhead when editing data, and indexing a column which is not selective will create overhead with no benefit. By not selective I mean that if you have a table with a column called "Gender" and it has 2 values "M" or "F", creating an index is not a good idea (even though you may search commonly on gender) because it is approximately a 50-50 split on the data. You want an index where only around 10% of the data is associated with each distinct data value.
• In Access you do not need to index Foreign Keys (Links To Other Tables) as long as you create relationships, Access automatically creates a "Hidden" index on the linking field (see later). Incidentally in SQL Server you are responsible for creating these indexes, it does not automatically index the linked columns.

Some Advice On Indexing From Microsoft With Regards To Jet 3.5

The following extract from a paper referenced later is well worth a read.

By simply adding an index to the column that was being updated, overall throughput diminished over five times! The question then becomes: When should a column be indexed? There is no concrete answer for this, as it depends on the type of application. The first rule of thumb is that highly duplicated data types should not be indexed (for example, Boolean data types, and columns that represent gender, state abbreviations, or country codes). The second rule of thumb is to not add indexes to columns simply to force Rushmore to use more than one index. An example of this would be indexing a column called City and a column called ZipCode in a customer table when the application is always going to be using both columns for retrieval purposes. In this instance, ZipCode is going to be the most unique index and would return a faster result set if City was not indexed. This is because Rushmore need not use the index on City, thus reducing overall I/O. Of course, if both values were not always being entered and they were used alternatively and equally, then having an index on both columns would probably be advantageous. Rushmore is best utilized when combined indexes generate a unique result set.

Remember databases are very complex tools, and trying to apply your own logical reasoning to how indexes are used may be a difficult undertaking. What we need is a mechanism for finding out whether our indexes are being used or not. Read on.

SQL Server & Access

Both these products have a lot in common, and the more you learn about SQL Server, the more you find that similar issues arise in Access, and this makes you think. For example in SQL Server we draw the distinction between a CLUSTERED and NON-CLUSTERED index. Each table can only have one CLUSTERED index, this controls the physical ordering of data. If you look inside Access these days you will discover that the primary key is called a CLUSTERED INDEX. This is no coincidence.

SQL Server Management Studio

This tool can actually draw pretty pictures showing exactly how your SQL is executed, it shows which indexes are used. An invaluable tool in tuning and optimising your applications.

SQL Server Profiler

This SQL Server tool allows you to monitor and examine all the SQL and other activities on a SQL Server. If you are designing an application using Access or ASP and SQL Server this is an invaluable tool for finding out what is going on. Interesting areas to examine are how Access transactions on a workspace translate into transactions on the SQL Server, and how TimeStamping is used when changed records are written back to the server.

Access Analysis

What about when we have a pure Access application? The following are what Microsoft refer to as unsupported features.

"These features were implemented primarily for use by the Microsoft Jet performance team as a way to better measure and improve Microsoft Jet performance, thus the availability or similar functionality of these features in future releases of Microsoft Jet is not guaranteed."

ISAMStats Function

The DAO object model exposes a function that allows the developer to get information about the raw disk reads, writes, locks, and caching. An example of this code can be downloaded from here: ISAMStatsDemo.

For further information on this function read the following Microsoft Article:-

Microsoft Jet 3.5 Performance Overview and Optimization Techniques.

BUT, the form will now be read only, and the example using the domain function does not suffer from this problem.

Tracing ODBC - TraceODBCApi Key (Access Linked To SQL Server, see comments below)

Initiates sending a trace of ODBC API calls to the file ODBCAPI.TXT You will probably find the output from this file is incomprehensible, unless you are familiar with the internals of ODBC driver architecture. A much better choice is the key described next.

Jet Database Engine Version 3.0: ODBC Connectivity.

TraceSQLMode Key (Access Linked To SQL Server, see comments below)

Microsoft Access can record all SQL statements sent to an ODBC data source in a file called Sqlout.txt. You can create this log file by setting the TraceSQLMode setting to 1. This is a very useful registry key if you do not have the SQL Server Profiler available.

ACC: TraceSQLMode Setting Helps Debug SQL Queries to ODBC

JETSHOWPLAN Key (Any Access Application)

The Microsoft Jet query engine implements a cost-based query optimizer. When a query is compiled, the query engine creates a query plan. This plan is used internally to find the quickest way to execute a query. Using the ShowPlan key in the registry will cause Microsoft Jet to create a text file containing the query execution plans.

When ShowPlan is turned on, Microsoft Jet appends text to a file called SHOWPLAN.OUT every time a query is compiled. You must modify or compact the database in order to have a stored query show its query plan.

The exact location of the key in the registry does depend on the version of the JET engine, for example :-

For related details and information on how to set the key see the following links: -

Microsoft Jet 3.5 Performance Overview and Optimization Techniques.

Use Jet's ShowPlan to write efficient queries.

Comments On Using Registry Keys

After you have set the keys, you would be advised to close the Access application, and then re-open and compact the database (which decompiles all the SQL).

The next problem is to find the output files. SHOWPLAN.OUT, odbcapi.txt and sqlout.txt are normally located in MyDocuments.

Remember when you have finished to switch all the registry keys off.

To see the effect of the TraceSQLMode or TraceODBCApi you need to be using ODBC, for example using linked table in Access to a SQL Server, or making a C++ program talk to Access using ODBC.

Relationships And Indexes

If you take the [Orders] and [Employees] tables from the Northwind database, and remove the relationship from the tables and the index from [Orders]table, field [EmployeeId]. Then Write the following query.

Now compact the database, run the query and examine showplan.out. Then create a relationship between the two tables as shown below: -

Then compact the database, re-run the query and examine showplan.out.

Even though the [Orders] [EmployeeId] field does not have an index, you will find that Access is using a hidden index on this field in the case where a relationship has been defined.

From this you can see that creating relationships means that Access will automatically create hidden indexes on the Foreign key Fields. THIS IS AN IMPORTANT OBSERVATION, because when you start working with SQL Server it DOES NOT do this, and you must create the indexes on the Foreign Keys.

One further point to note is that when you compact and repair an Access database the SQL in the queries plan get removed. When a query is first run a plan for the SQL will be generated, which means that the first time it is run it will be slightly slower to execute.

From this you can also see the benefits in using saved queries over writing SQL on-the-fly, as for saved queries the overhead of evaluating the plan occurs once when the query is first run.

Performance Analyzer

You can also use the Tools-Analyze-Performance to analyze and make recommendations for indexing your database.

Other Related Articles

The following articles are also worth reading : -

How To Improve Performance of Applications Using Jet 4.0.

INFO: MDAC Upgrade Issues with Access ODBC Driver.

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz

support@ascassociates.biz

sales@ascassociates.biz