Indexes with DAO

[Troy]

I have an Access 2000 database with a standard frontend/backend setup
(backend has tables, frontend has everything else).

I do a lot of DAO SQLSelect access in VBA to get to the data in the tables.
I have tried to make sure that the SQL statements are as specific as possible
to minimize the amount of data returned to the calling functions.

My question is how are indexes used in this situation? Are the indexes that
are created in the Table design automatically used? Do I need to create
custom indexes or is there a SQL way I should be doing this? I want to
improve the overall performance of the SQL Queries, but I am not clear how to
make sure the queries are being aided by an index.

I hope the above makes sense.

Thanks

 

[Tony Toews [MVP]]

My question is how are indexes used in this situation? Are the indexes that
are created in the Table design automatically used?

Yes, whenever possible.

Do I need to create custom indexes

Where appropriate yes. In general if a field is used in for record
selection or sequencing it should be indexed. Note though that the
more indexes you have the slower performance will be when adding new
records. This may not be significant though until you have many
indexes and hundreds of thousands of records.

or is there a SQL way I should be doing this?

Better to do it in the table design view.

I want to
improve the overall performance of the SQL Queries, but I am not clear how to
make sure the queries are being aided by an index.

I hope the above makes sense.

Perfect sense.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Troy]

Tony -

Thanks for the quick response. As a follow up, if I have a SQL select like
this:

SQLSelect = "Select SUM(Hours) as VacationSum from tbl_Time WHERE USERID
like '" & UserID & "*' and DATE LIKE '*" & CalendarYear & "*' "

Is it enough to set the Index property to Yes in the Table Design for both
the USERID and DATE fields? Or do I need to create a muliple fields index
that has both the USERID and DATE fields in it? If so, does the name of the
Index matter?

Thanks!

Troy

 

[Alex Dybenko]

Hi,
if you typically retrieve data from this table using both USERID and DATE
fields - then it has sense to make one complex index for both fields. Else
you can make 2 indexes. Name does not matter

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[David W. Fenton]

As a follow up, if I have a SQL select like
this:

SQLSelect = "Select SUM(Hours) as VacationSum from tbl_Time
WHERE USERID
like '" & UserID & "*' and DATE LIKE '*" & CalendarYear & "*' "

Is it enough to set the Index property to Yes in the Table Design
for both the USERID and DATE fields? Or do I need to create a
muliple fields index that has both the USERID and DATE fields in
it? If so, does the name of the Index matter?

Why are you using LIKE in your WHERE clause?

Also, is DATE your field or CalendarYear? DATE is a reserved word,
because it is a VBA function.

I would never use LIKE for date selections. Instead use BETWEEN:

DateField Between #1/1/2007# and #12/31/2007#

That will use any index on the date field, whereas Like won't
(though like "string*" will partially use an index, any selection
that is "*string*" will not, and not LIKE will use an index on a
date field, since date fields are actually stored as doubles, with
the integer part indicating the day, and the decimal part the time).