Setting Form Filter and/or OrderBy Slow Using SQL Server 2000

[Larry Stinson]

One of my clients recently upgraded to SQL Server 2000. Previously, when the
backend data was in Access 2000 tables on a file server, setting the Filter
and/or OrderBy
properties of forms in my Access 2000 frontend provided virtually
instantaneous results. Now that the data has been moved to their SQL server
and the Filter and/or OrderBy properties are changed on a form, it takes the
form approximately 2-3 seconds before the results are displayed.

I have made sure that the tables on the SQL server have the same indexes as
they did when they were in Access 2000. Has anyone else had a similar
experience, and if so, how can this situation be rectified? I have been
unable to find anything in any of the Knowledge Base articles, including
Q208858, "Optimizing for Client/Server Performance". Using the FindFirst
method of the DAO Recordset is also very slow.

Thank you in advance for any help.

 

[Joe Fallon]

SQL Server is better than Access in many areas.
Speed isn't one of them.
A local Jet database is significantly faster.

You may be seeing network traffic effects.
Try copying the data to a temp table and then basing the form off of that.
Does the filtering, sorting speed up again?