Access Query Speed

[edw]

Assuming two years of HTR data (every track), what might be the typical range of times that it should take for access queries assuming an XP machine of relatively recent vintage?

[Rick]

One way to speed up queries is indexiing some of the fields in your queries. There is a limit to the number of indexes you can create and you may have to experiment around some to see which fields indexing helps the most.

You have to index the fields in your tables.

I will have to look up the max number of indexes allowed.

I don't think the queries should take over a half minute.

[Rick]

Index Specifications

Number of indexes in a table 32
Number of fields in an index 10


That may include Primary Key.

[Donnie]

A good rule of thumb for choosing fields to set the index on in the design view of the table.....

Fields that you normally sort or group by....(TRK, RACE, DATE, GENDER, AGE, PURSE VALUES...etc.)
Fields that you normally will apply criteria against....(DIST, SURFACE, AGE, GENDER, VI, ...etc.) Look inside your queries to help understand what should be indexed.

My understanding is that by setting too many indexes 2 things will happen....your db will double or even triple in size. And if you set too many indexes it actually may slow your process down.

By saying of "relatively recent vintage", you need to understand that a 1.2 ghz processor actually chugs along at turtle speed when compared to a top end processor (3.4 ghz) of today. Back in it's day (2-3 years ago) that 1.2 was the cat's pajamas. In a short time we have moved very fast in the speed dept.

[edw]

Thanks for the insights guys. Greatly appreciated.

[Rick]

If you are running a Primary Key you should not need to index any field that is included in your Primary Key.

You might add indexes one at a time and time them.

[hurrikane]

If you are running a Primary Key you should not need to index any field that is included in your Primary Key.


And if you are not using a PK then we better start there!!!!!!

I would only add that you have to be careful what you index. Indexing is a way of grouping for quick look up. IMO things like VI that have so many ranges that is is not good to index this field and in fact may not even index. Ratings are good to index (but bad to build spot plays on).

Recent vintage isn't indicative of ability. What's the processor speed? What's the processor type?( Celeron, AMD, Pent, etc), ram? free disk space?

when is the last time you compacted your db?

how many joins do you have in your query? how many calculations? A simple select query should not take long at all but if you start calculating fields it will slow things down considerably and you may find it better to move to vba.


Have fun and good luck

[AwolAtHTR]

I do two things to get faster access by my processing.

1. date field as TEXT instead of type Date which is used as part of the Primary key of Trk, Date, Race, Pace Line, BetNumber

2. form one race ID field (AwolID) using Trk (3 characters), Date (8 as YyyyMmDd), Race (2)

so, the Primary key is used to process the data using the imported data. Then the AwolID can be used for a query or VB program and to form other Alternate keys to speed up a special query that is frequently used to get reports.

How much affect on the speed?

I do not know but think about how much work is needed by the processing to fetch and process five fields to form the Primary Key. Also, the Primary Key does not have to call the date convertor to process the stored record's value to a date value. I have always been curious but have not performed a test with Date field as type text or type Date. When the data base has half a million records, I do believe there will be a performance benefit using these rules for the Primary and Alternate keys.

and, expect, only a few hundred thousand records will have a performance benefit.

duane