Understanding Indexing?

[James C.]

I have a master table that runs slow. I looked in the access help files and
it said that I can put an index on one of my fields to help speed it up. I
have a city field and tried putting an index on this and I did not notice any
speed difference.

Any ideas on indexing? I heard that it doesn't work very well in Access,
only in SQL tables.

Thanks

 

[John Vinson]

I have a master table that runs slow. I looked in the access help files and
it said that I can put an index on one of my fields to help speed it up. I
have a city field and tried putting an index on this and I did not notice any
speed difference.

Any ideas on indexing? I heard that it doesn't work very well in Access,
only in SQL tables.

Well, you heard wrong. Or, more likely, the person who told you this
is a SQL snob who doesn't know much about Access.

An Index will *slightly* slow things down when you're updating or
inserting into a table, since Access must write to the index as well
as writing into the table (and must occasionally rebuild the index).

It will *markedly* speed up queries which use the indexed field as a
criterion for searching, or which sort by the indexed field.

It's quite possible to write a query which defeats the benefit of the
index - for instance, if you use a query

WHERE Street LIKE "*" & [Enter street name:] & "*"

it won't use the index, since only criteria which have no wildcard at
the beginning will use the index.

Could you post the SQL of an example of a query which you feel does
not benefit from indexing? Indicate which fields have had their
indexes changed.

John W. Vinson[MVP]

 

[James C.]

Here is my SQL Code:

--------------------------------------
SELECT TOP 20 IIf([Origin]="FFP","LAND",[Origin]) AS Orig,
Sum(tblMaster.[TTL Price]) AS [Total Revenue], Sum(tblMaster.AWHRev) AS [AWH
Revenue], Sum(tblMaster.PAX) AS Passengers, Count(tblMaster.TRL) AS Packages,
Sum(tblMaster.Duration) AS [Room Nights]
FROM tblMaster
WHERE (((tblMaster.GuaranteeYear)="2005") AND ((tblMaster.[Guarantee Date])
Between #1/1/2005# And Date()-1) AND ((tblMaster.[Hotel Code])=[HCode]))
GROUP BY IIf([Origin]="FFP","LAND",[Origin])
ORDER BY Sum(tblMaster.AWHRev) DESC;

I have Indexes on the Hotel Code field.

I have a master table that runs slow. I looked in the access help files and
it said that I can put an index on one of my fields to help speed it up. I
have a city field and tried putting an index on this and I did not notice any
speed difference.

Any ideas on indexing? I heard that it doesn't work very well in Access,
only in SQL tables.

Well, you heard wrong. Or, more likely, the person who told you this
is a SQL snob who doesn't know much about Access.

An Index will *slightly* slow things down when you're updating or
inserting into a table, since Access must write to the index as well
as writing into the table (and must occasionally rebuild the index).

It will *markedly* speed up queries which use the indexed field as a
criterion for searching, or which sort by the indexed field.

It's quite possible to write a query which defeats the benefit of the
index - for instance, if you use a query

WHERE Street LIKE "*" & [Enter street name:] & "*"

it won't use the index, since only criteria which have no wildcard at
the beginning will use the index.

Could you post the SQL of an example of a query which you feel does
not benefit from indexing? Indicate which fields have had their
indexes changed.

John W. Vinson[MVP]

 

[John Vinson]

Here is my SQL Code:

Ok... you're calling an inefficient IIF function ON EVERY SINGLE ROW,
and sorting *by this function call*. Obviously a function cannot be
indexed. Might it be possible to run an Update query updating the FFP
values to LAND, or train the users that FFP *means* LAND? That will
make the query MUCH faster right there.

Sum(tblMaster.[TTL Price]) AS [Total Revenue], Sum(tblMaster.AWHRev) AS [AWH
Revenue], Sum(tblMaster.PAX) AS Passengers, Count(tblMaster.TRL) AS Packages,
Sum(tblMaster.Duration) AS [Room Nights]
FROM tblMaster
WHERE (((tblMaster.GuaranteeYear)="2005") AND ((tblMaster.[Guarantee Date])
Between #1/1/2005# And Date()-1) AND ((tblMaster.[Hotel Code])=[HCode]))

For use next year, or the year after, try

Between DateSerial(Year(Date()), 1, 1) AND Date() - 1

GROUP BY IIf([Origin]="FFP","LAND",[Origin])
ORDER BY Sum(tblMaster.AWHRev) DESC;

And you're ordering by a Sum field so that it must first run the
totals query, and *then* sort the results by a (again, non indexed)
field.

I have Indexes on the Hotel Code field.

Try putting an index on GuaranteeYear and GuaranteeDate as well, it'll
help. But this is still going to be slow. Getting rid of the IIF is
the biggest thing you can do though.


John W. Vinson[MVP]

 

[James C.]

John,

I actually timed the difference between having an index on the hotel field
and not having it and it did make a big difference. This is however just one
of many queries that I am trying to speed up. The query that I copied below
just deals with one table. Will indexing work when you have multiple tables?

Here is an example where I use a "Translator Table" so that I can type
Arizona instead of PHX, TUS, etc....) The translator table allows me to do
groupings on my destination field. Here is the code.

------------------------------------------------
SELECT TOP 20 IIf([Origin]="FFP","LAND",[Origin]) AS Orig,
Sum(tblMaster.[TTL Price]) AS [Total Revenue], Sum(tblMaster.AWHRev) AS [AWH
Revenue], Sum(tblMaster.PAX) AS Passengers, Count(tblMaster.TRL) AS Packages,
Sum(tblMaster.Duration) AS [Room Nights]
FROM tblTranslator INNER JOIN tblMaster ON tblTranslator.DEST = tblMaster.DES2
WHERE (((tblMaster.GuaranteeYear)="2005") AND ((tblMaster.[Guarantee Date])
Between #1/1/2005# And Date()-1))
GROUP BY IIf([Origin]="FFP","LAND",[Origin]), tblTranslator.GROUP
HAVING (((tblTranslator.GROUP)=[Destination]))
ORDER BY Sum(tblMaster.AWHRev) DESC;
---------------------------------------------------------
I have 2 fields indexed, the DES2 field from tblMaster, the DES field from
tblTranslator.

Again, is it possible for indexing to work when linking tables?

Here is my SQL Code:

Ok... you're calling an inefficient IIF function ON EVERY SINGLE ROW,
and sorting *by this function call*. Obviously a function cannot be
indexed. Might it be possible to run an Update query updating the FFP
values to LAND, or train the users that FFP *means* LAND? That will
make the query MUCH faster right there.

Sum(tblMaster.[TTL Price]) AS [Total Revenue], Sum(tblMaster.AWHRev) AS [AWH
Revenue], Sum(tblMaster.PAX) AS Passengers, Count(tblMaster.TRL) AS Packages,
Sum(tblMaster.Duration) AS [Room Nights]
FROM tblMaster
WHERE (((tblMaster.GuaranteeYear)="2005") AND ((tblMaster.[Guarantee Date])
Between #1/1/2005# And Date()-1) AND ((tblMaster.[Hotel Code])=[HCode]))

For use next year, or the year after, try

Between DateSerial(Year(Date()), 1, 1) AND Date() - 1

GROUP BY IIf([Origin]="FFP","LAND",[Origin])
ORDER BY Sum(tblMaster.AWHRev) DESC;

And you're ordering by a Sum field so that it must first run the
totals query, and *then* sort the results by a (again, non indexed)
field.

I have Indexes on the Hotel Code field.

Try putting an index on GuaranteeYear and GuaranteeDate as well, it'll
help. But this is still going to be slow. Getting rid of the IIF is
the biggest thing you can do though.


John W. Vinson[MVP]

 

[John Vinson]

John,

I actually timed the difference between having an index on the hotel field
and not having it and it did make a big difference. This is however just one
of many queries that I am trying to speed up. The query that I copied below
just deals with one table. Will indexing work when you have multiple tables?

Here is an example where I use a "Translator Table" so that I can type
Arizona instead of PHX, TUS, etc....) The translator table allows me to do
groupings on my destination field. Here is the code.

Again... the IIF will KILL you for time. Just for kicks, try taking
out the IIF and just grouping by Origin itself.

Sum(tblMaster.[TTL Price]) AS [Total Revenue], Sum(tblMaster.AWHRev) AS [AWH
Revenue], Sum(tblMaster.PAX) AS Passengers, Count(tblMaster.TRL) AS Packages,
Sum(tblMaster.Duration) AS [Room Nights]
FROM tblTranslator INNER JOIN tblMaster ON tblTranslator.DEST = tblMaster.DES2

Is there a relationship defined between the fields DEST and DES2?

WHERE (((tblMaster.GuaranteeYear)="2005") AND ((tblMaster.[Guarantee Date])
Between #1/1/2005# And Date()-1))

again... do GuaranteeYear and Guarantee Date have indexes?

GROUP BY IIf([Origin]="FFP","LAND",[Origin]), tblTranslator.GROUP
HAVING (((tblTranslator.GROUP)=[Destination]))

Add GROUP to the query a second time, using WHERE as the aggregate
function on one instance, and GROUP BY on the other. Putting the
criterion on the GROUP BY field forces Access to find, sort, and group
all records in the table, and then throw away everything except the
one destination. Putting [Group] = [Destination] in the WHERE clause
instead of HAVING will filter the records first, before all the
grouping operations.

ORDER BY Sum(tblMaster.AWHRev) DESC;
---------------------------------------------------------
I have 2 fields indexed, the DES2 field from tblMaster, the DES field from
tblTranslator.

Again, is it possible for indexing to work when linking tables?

It is OBLIGATORY for linked tables (linked in the relationships
window) to be indexed; in fact creating a relationship creates the
needed indexes (so don't do it yourself or you'll get a redundant
index).

If by linked tables you mean linked to a backend database - you must
create the indexes *in the backend*, on the tables themselves, not on
the links.

John W. Vinson[MVP]