Sort performance with Access project form -- sql server clients

TLD
2/5/2007 6:26:03 AM

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.

[quoted text, click to view]

Mike C#
2/6/2007 12:14:42 AM

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.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server clients

sql server clients : Sort performance with Access project form