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.
Top of Page
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.
Top of Page
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.
Top of Page
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.
Top of Page
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.
Top of Page
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."
Top of Page
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.
Top of Page
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.
Top of Page
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
Top of Page
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 :-
\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\3.0\Engines\Debug
\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines\Debug
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.
Top of Page
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.
Top of Page
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.
Top of Page
Performance Analyzer
You can also use the Tools-Analyze-Performance to analyze and make recommendations for indexing your
database.
Top of Page
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.
Top of Page
by Andrew Couch Office Access MVP, Director ASC Associates
andy@ascassociates.biz