The sort performance of my Access form is very slow. The datasource is a
stored procedure. The sort is changed by setting the OrderBy property.

Please suggest means of speeding up sorts.

If this is the wrong user group, please point me to the right one.

Thanks.

"TLD" <TLD (AT) discussions (DOT) microsoft.com> wrote

I'm assuming you're using Access to access SQL Server. Use "Pass-Through"
queries to make SQL Server do the work instead of that God-awful Jet engine.

The god-awful Jet engine was about twenty times faster in an earlier
incarnation of the system. What turned out to be the problem was the width of
the data returned. Once a certain number of fields were being returned, the
sort time changed from five seconds to over two minutes. This was verified on
a local machine, on a notebook machine connecting via VPN to a server, and on
a client-server system. The results were calculated through Query Analyzer,
leaving Access out of the picture altogether.

None of the data gurus seems to know why this drop in performance (very
sudden, once you add the field that breaks the bank) occurs.

Thanks for your help.


"Mike C#" wrote:

Not sure of your specifics, but a common problem with Access to SQL Server
connectivity is that Access users write queries that pull tens of thousands
of rows with dozens of columns "across the wire" for the God-awful Jet
engine to try to sort out instead of using Pass-Through Queries and letting
SQL Server narrow the results before they are passed back.

If the number of columns being returned appears to be the main issue, and
you've verified the same poor performance in QA, then you're probably a
victim of a poor indexing strategy. Try looking at the Query Execution Plan
in QA and keep an eye out for Sort Operations, Bookmark Lookups, Table
Scans, and high cost operations. That should give you an idea of where the
problem lies if it's with SQL Server itself, and should also give you an
indication of how you might want to approach indexing, etc.

"TLD" <TLD (AT) discussions (DOT) microsoft.com> wrote