Multiple indexes on same fields in different order

[Petr Danes]

I sometimes need to find the largest or smallest value in a field. If I put
two indexes on the same field, one in ascending order and one in descending
order, is Jet smart enough to use the proper index if a give it a grouping
query with the condition Max or Min, or am I just wasting resources?

Pete

 

[Jerry Whittle]

How many records are you talking about? If it's less than ten thousand, it
probably doesn't matter.

You could get out a stopwatch and test which works faster.

Then there's always Showplan which will tell you what index, if any, that is
being used by a query.

http://articles.techrepublic.com.com/5100-10878_11-5064388.html

 

[Petr Danes]

Around 80,000. I tried the ShowPlan bit a few days ago, but wasn't able to
find the file that it supposedly creates, so I probably did something wrong.

Also, I'm running this split over a network, with Access for both frontend
and backend, so if I put a proper index on the backend, it might make a
sizable difference in how fast it gets something from an extreme end, no?

Pete

 

[Jerry Whittle]

Your best bet is to create the indexes and get out a stopwatch. See if it
makes a difference. Tip: Time the second or third execution of the query as
Access often takes extra time to optimize the query in memory on the first
run.

 

[Petr Danes]

Okay, I'll try that.

Your best bet is to create the indexes and get out a stopwatch. See if it
makes a difference. Tip: Time the second or third execution of the query
as
Access often takes extra time to optimize the query in memory on the first
run.