>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
Try the SELECT with SET DELETED OFF - we've also done some INTENSE testing (on enormous tables) and came to a similar conclusion - SEEK is faster. THEN we found that SET DELETED OFF increases the select speed. If you have a deleted flag in the table and put HAVING lDeleted=.F. this will do the same but faster!
Your rule of thumb is incorrect in our case. Locate is 10 times slower than seek. Select is 100 times slower than seek. Select with set deleted off is 100 times faster than seek!?!