Is there a Query Optimization Tool?

A

Al

I have a fairly complext query that I am building. There are 3 tables
involved in this query. The 3rd table is a lookup table that I am aliasing
and accessing 24 times using different criteria for each of the 24 accesses.
All tables are normalized, and indexed.

I am experiencing unacceptable access times when I add the 17th alias (18
tables). Anything after the 17th alias causes the query to run for many
minutes. With 24 aliases the query can run for 1/2 hour. It actually works
but this level of speed is not acceptable.

Is there a query optimization/debug tool in Access that will help?

Or what are some others ways to accomplish this task?
 
6

'69 Camaro

Hi, Al.
Is there a query optimization/debug tool in Access that will help?
Click to expand...

Jet can provide a query analysis tool for you if you don't mind making a
Windows Registry change. Please see the following Web page for a link to
the article, "Use Microsoft Jet's ShowPlan to Write More Efficient Queries":

http://www.Access.QBuilt.com/html/articles.html

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
A

Al

Thanks 69 Camaro for the link (my buddy had a 69 Chevelle 396--what a car).

Wow! What an eye-opener. I'm not real sure just how to interpret the
results, but this shows that there is a lot of work going on "behind the
scenes".

I am finding temporary indexes being built seemingly based upon combinations
or other indexes. Would it be good to build these indexes into the table
definition?

Alan
 
T

Tom Lake

Thanks 69 Camaro for the link (my buddy had a 69 Chevelle 396--what a
car).

Wow! What an eye-opener. I'm not real sure just how to interpret the
results, but this shows that there is a lot of work going on "behind the
scenes".
Click to expand...

There's an error on that Website in 13 Ways to Loathe VB, #11, Logic. (at
least in Access 2003)

Dim b As Boolean, c As Boolean
b = Check1.Value
c = Not Check1.Value

The checkbox will set b and c correctly based on the value of the checkbox.
The author states c will always be True.

Tom Lake
 
Top