If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query on SQL Server is fast, while Access is crawling
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? |
#2
|
|||
|
|||
Query on SQL Server is fast, while Access is crawling
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) "Mike G[raser]" wrote in message ... 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? |
#3
|
|||
|
|||
Query on SQL Server is fast, while Access is crawling
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. -- AG Email: npATadhdataDOTcom "Mike G[raser]" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|