Query on SQL Server is fast, while Access is crawling


Mike G[raser]

I have a query that executes a Table-Valued Function in SQL server. If I
execute it withing Query Analyzer in SQL server, it completes in 40 seconds.
If I execute the same query in a pass-through query from Access, it takes
over 5 minutes.

I ran sp_lock to view the locking information during the execution of the
query from both locations. Query Analyzer came back with around 5300 locks
immediately and the number remained constant throughout query execution.
When executed from Access, it would add about 100 locks every 10 seconds. It
would take a very long time to get up to 5300 locks required to complete the

Does anyone know what's happening here? I thought Access would just pass
the command off to SQL and when SQL is done with it's stuff it would send the
info back to Access. But, it looks like there must be some sort of
communication going on there that slows it all down?

Anyone have any idea?


Normally when Access is slow like that, it means Access
is requesting only some of the records. Sometimes, one
at a time, sometimes depending on the ODBC buffer settings.

Are you dynamically creating this pass-through query?
If so, try not doing that.



If you are not modifying any data, try using the WITH(NOLOCK) hint in your
I also find SQL Profiler extremely helpful when tracking down things like

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question