Slow performance with records towards end of table

[Chad]

We have an MS Access 2003 front-end (MDE) linked to MS SQL 2000 Server
database back end. One of the tables in the SQL server contains
(16700) records. The performance is slow when executing the following
line of VB code to go to a specific record on a form:

CustomerCode.SetFocus

DoCmd.FindRecord yCustomer, , True, , True, acCurrent

This process takes a long time (up to 10 seconds) for records towards
the end of the table. It is rather quick when opening a record that
falls at the beginning of the table. We did not have this problem
when we were using Access to store data. We tried optimizing the
performance by creating indexes on the table and that improved the
performance but the performance is still much slower than it was when
the data was in Access.

I should also point out that this is happening for all tables with a
large amount of records. Old records (towards the top of the table)
come up fast, and new records (towards the bottom) come up very slow.

Thanks in advance for any suggestions.

 

[Brian]

We have an MS Access 2003 front-end (MDE) linked to MS SQL 2000 Server
database back end. One of the tables in the SQL server contains
(16700) records. The performance is slow when executing the following
line of VB code to go to a specific record on a form:

CustomerCode.SetFocus

DoCmd.FindRecord yCustomer, , True, , True, acCurrent

This process takes a long time (up to 10 seconds) for records towards
the end of the table. It is rather quick when opening a record that
falls at the beginning of the table. We did not have this problem
when we were using Access to store data. We tried optimizing the
performance by creating indexes on the table and that improved the
performance but the performance is still much slower than it was when
the data was in Access.

I should also point out that this is happening for all tables with a
large amount of records. Old records (towards the top of the table)
come up fast, and new records (towards the bottom) come up very slow.

Thanks in advance for any suggestions.

What you have discovered here is that taking an Access system and converting
lock stock and barrel to SQL Server rarely gives good results. The system
needs to be specifically designed with SQL Server in mind, and one of the
main considerations is that you do NOT bind forms to an entire table! What
you should do is build some kind of mechanism (e.g. a search criteria form)
that limits the form's recordset to one, or just a few, records.