JET utilization of Indexes in Query Execution

[Guest]

Query Index Questions:

Suppose that I have two tables Y and Z. The following are the facts
concerning the tables:

Table Y has a primary key based upon fields A,B,C,D. There are over 3
million records in the table. Fields A, B, C, D are primary keys that lookup
into other tables such as vendor, product, Process Calculation Run, etc.

Table Z has a primary key upon fields B, C where B and C are the same fields
listed above. The table also has a field E, which is related to field B.
The table has 18,000 records.

Two questions regarding query performance purposes: Question One: what will
happen if I create a new query with table Y and Z, using a one to one join
between two corresponding fields B and C of the 2 tables? Will Microsoft
Access be able to take advantage of the primary key index in table Y and Z?

The second question is a little bit different. Table Y is the same as
above. Table Z has a primary key on fields C and B (the order of the fields
is correct), and still contains Field E (which is related to Field C). I
created a new/extra index in table Y together on the fields B and C (this is
the correct order), and create a similar index in table Z together on the
fields B and E(Notice E is different than field C). Suppose, now I create a
new query using tables Y and Z with the joins on the fields Y.B to Z.B and
Y.C to Z.E. I also have added a where clause on the field Z.E. Will
Microsoft Access be able to utilize the created indexes on the fields Y.B/C
and Z/B.E when executing the query?

 

[Jason Lepack]

See inline comments

Query Index Questions:

Suppose that I have two tables Y and Z. The following are the facts
concerning the tables:

Table Y has a primary key based upon fields A,B,C,D. There are over 3
million records in the table. Fields A, B, C, D are primary keys that lookup
into other tables such as vendor, product, Process Calculation Run, etc.

Table Z has a primary key upon fields B, C where B and C are the same fields
listed above. The table also has a field E, which is related to field B.
The table has 18,000 records.

Two questions regarding query performance purposes: Question One: what will
happen if I create a new query with table Y and Z, using a one to one join
between two corresponding fields B and C of the 2 tables? Will Microsoft
Access be able to take advantage of the primary key index in table Y and Z?

It will be able to use the index in Z but not in Y. In Y it will be
able to scan the index, but not seek it. The reason is the ordering
of your index:
A B C D
1 1 1 1
1 1 1 2
1 2 3 4
2 1 2 3
2 1 3 4
2 2 2 2
3 1 2 1

If you pull out just the column B and C you get this:
B C
1 1
1 1
2 3
1 2
1 3
2 2
1 2

Not very effective eh? Query would probably be excecuted using an
index scan on Y and an index seek on Z.

The second question is a little bit different. Table Y is the same as
above. Table Z has a primary key on fields C and B (the order of the fields
is correct), and still contains Field E (which is related to Field C). I
created a new/extra index in table Y together on the fields B and C (this is
the correct order), and create a similar index in table Z together on the
fields B and E(Notice E is different than field C). Suppose, now I create a
new query using tables Y and Z with the joins on the fields Y.B to Z.B and
Y.C to Z.E. I also have added a where clause on the field Z.E. Will
Microsoft Access be able to utilize the created indexes on the fields Y.B/C
and Z/B.E when executing the query?

Also very inefficient because they are in an opposite sort order. In
a multiple join, you want both indexes ordered and in the same order
so that it just has to seek the first column of first index and match
with a seek in the first column of the second index. Now that it's
bookmarked the first column then it just has to seek the column from
that point on.

Cheers,
Jason Lepack

 

[Guest]

To find out, run a test. First Google "Access Showplan" and install it. Then
when you run the query, the results will be in a file in the same name as the
database with an .out extension.

Make sure to have plenty of records or Access might not even bother with
indexes as a full table scan would faster. Also if you make changes to a
table or query, run the query at least twice as the first time Access decides
the best execution plan for the future.

 

[Guest]

Jerry,

I have already used showplan. Showplan reports that Access is using
Rushmore on the fields. However, the query runs extremely slow, as if Access
is having to scan through the 3,000,000 plus records in table Y rather than
using an index.

 

[Guest]

Is table Y in an Access mdb file or are you linking to something like SQL
server? If linking to SQL server, all bets are off on Access properly using
indexes. You probably will get better performance by using pass-through
queries.

If table Y is in Access, you might be nearing it's practical limitation due
to performance issues. You may need to upsize to something like SQL Server if
it's really a problem.

 

[Guest]

All the tables are MS Access Tables. The Y table is a link to another Access
Database.