VB Forum / Database Access / August 2005

I asked this question a couple of days ago in a post with a
perhaps poorly worded Subject header.  It got no replies.  Let's
try again..

I maintain an Acess 2002 database table with 50,000 records with an indexed
field set to Primary Key.  Using Select with a LIKE clause in a VB6
program with the ADO library and the Jet 4 engine, I have noticed that
the retrieval time for records(s) is faster by one or two orders of magnitude
when there are wildcards present than where there aren't.  In other words, the
time to retrieve dozens of records is much faster than the time to retrieve
a single record.

What's going on?

Here's the line from my code that fetches records:

    rs.select "Field1 from Main where Field1='mystring' ", cn

where mystring can contain the wildcard characters % or _ (underscore) and
rs and cn are recordset and connection objects.

Examples of mystring are "1HSL48" and "7zaw29".  They describe
nodes in a routing table.  A typical application might include
100,000 table loopups and have wildcard searches like "1__L__".

Finding one record such as "1HSL48" requires about 800 msec, while finding
25 records matching "1__L__" requires 24 msec, a little less than 1 msec per
record.
I am quite puzzled why the time to retrieve one record takes so much longer.

Anyone have any ideas?  I know I'm overlooking something obvious.

For instance, would it be more efficient perhaps to use Select to select the
entire table for the duration of the table lookups and use Find to move the
recordset cursor back and forth?