There was some discussion lately of the relative speed of different ways of locating a record. I have run some speed tests looking up a single 129 byte record in a table of about 102,000 records.

My computer is a 160 mhz Pentium with 80 MB of memory.

My test routine calls RAND(-1) and then calls RAND() from 1000 to 100,000 times generating a random integer key in the range 1 - 99,999.

The test table has a single candidate index on the first field which is an integer. I set the key field equal to the record number so that every seek or locate would succeed.

Somewhere above 10000 seeks, the times get quite repeatable. These are the times for one 100,000 seek test. (BTW, there was no noticeable disk activity during this test.)

0.61 ms -- SEEK command
0.69 ms -- SEEK() function
0.65 ms -- SEEK() function with SET ORDER TO primary index
2.3 ms -- LOCATE command with Rushmore
6.4 ms -- SQL SELECT

The times for the different SEEK methods varied enough to suggest that there is no significant difference between them.

As a rule of thumb:

LOCATE is 3 times slower than SEEK.
SELECT is 10 times slower than SEEK.

YMMV.

Peter Robinson