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
query!

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?

 

[david]

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.

(david)

 

[AG]

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