A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query on SQL Server is fast, while Access is crawling



 
 
Thread Tools Display Modes
  #1  
Old May 19th, 2010, 12:50 AM posted to microsoft.public.access.queries
Mike G[raser]
external usenet poster
 
Posts: 2
Default 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  
Old May 19th, 2010, 08:16 AM posted to microsoft.public.access.queries
david
external usenet poster
 
Posts: 398
Default 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  
Old May 19th, 2010, 07:04 PM posted to microsoft.public.access.queries
AG[_3_]
external usenet poster
 
Posts: 129
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:30 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.