Slow searching on Attached Tables

[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

 

[Rick Brandt]

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.

I use DAO against linked SS tables and have had no problems but admittedly
have only limited use of FindFirst.

Doing a FindFirst against a RecordSet with more than a few hundred rows
should be extremely unusual. Can you explain what your process is actually
trying to accomplish? Very often what you should be doing is using the
WHERE clause of the query your RecordSet is based on to "find" the records
you want rather than using FindFirst.

 

[Dirk Goldgar]

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.

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.

 

[Tom Collins]

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)

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