Slow searching on Attached Tables

T

Tom Collins

I'm running a SQl Server backend (recently converted from Access).
Because of this I changed the Seek methods to FindFirst.

I have found that if I do a find on one field, it goes pretty quick
(although a little lag is still detected), but if I do a find on two fields,
it takes a few minutes. Both fields are indexed separately and together as
the Primary Key.

I'm using DAO and the recordset is connected directly to the SQL database. I
also tried creating the recordset through the attached table with the same
results.

I could use some solutions and/or ideas. Should I switch to ADO? It wouldn't
be my first choice as I would have to do quite a bit of rewriting.

Thanks

Tom
 
D

Dirk Goldgar

Tom Collins said:
I'm running a SQl Server backend (recently converted from Access).
Because of this I changed the Seek methods to FindFirst.

I have found that if I do a find on one field, it goes pretty quick
(although a little lag is still detected), but if I do a find on two
fields, it takes a few minutes. Both fields are indexed separately
and together as the Primary Key.

I'm using DAO and the recordset is connected directly to the SQL
database. I also tried creating the recordset through the attached
table with the same results.

I could use some solutions and/or ideas. Should I switch to ADO? It
wouldn't be my first choice as I would have to do quite a bit of
rewriting.
Click to expand...

I could be wrong, but I don't think indexes are used in a FindFirst. If
it is at all possible, you should try opening your recordset on a query
that specifies exactly the criteria you want to find, so that only the
matching records are returned from the server. The indexes *will* be
used in such a case.

Will that solution work for what you are doing? If not, please describe
your situation in more detail.
 
T

Tom Collins

Dirk Goldgar said:
I could be wrong, but I don't think indexes are used in a FindFirst. If
it is at all possible, you should try opening your recordset on a query
that specifies exactly the criteria you want to find, so that only the
matching records are returned from the server. The indexes *will* be
used in such a case.

Will that solution work for what you are doing? If not, please describe
your situation in more detail.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
I had read somewhere that FindFirst was supposed to use indexes if it could
find it, but that looks inaccurate.
I rewrote that section of code to use a query to get several records and
then looped through them. The previous programmer was doing several Seeks
against the entire table of 18,000 records.

Thanks
 
Top