October 15, 2011 at 7:24 am
Todd Engen (3/6/2008)
I've seen that that behavior before and it was an application issue;in query analyzer it took less than a second to return the results, from the application it took over 5 minutes. A profiler trace showed many sp_cursorfetch calls.ADO settings in the application like CacheSize, CursorType, LockType and CursorLocation determine the number and type of API server cursor calls.
I've seen this behaviour today too on a SQL2005 server.
Using an ADO Recordset with CursorLocation will show this issue with the following query:
SELECT PK FROM MyTable WHERE FK='xxxx' ORDER BY PK
It will NOT show the issue with the query:
SELECT PK FROM MyTable WHERE FK='xxxx'
So the ORDER BY clause is the culprit, but WHY ???
( There is a index on PK and a index on FK )
Using CursorLocation = adUseClient will give you the expected performance
October 15, 2011 at 12:17 pm
Gugro (10/15/2011)
Todd Engen (3/6/2008)
I've seen that that behavior before and it was an application issue;in query analyzer it took less than a second to return the results, from the application it took over 5 minutes. A profiler trace showed many sp_cursorfetch calls.ADO settings in the application like CacheSize, CursorType, LockType and CursorLocation determine the number and type of API server cursor calls.
I've seen this behaviour today too on a SQL2005 server.
Using an ADO Recordset with CursorLocation will show this issue with the following query:
SELECT PK FROM MyTable WHERE FK='xxxx' ORDER BY PK
It will NOT show the issue with the query:
SELECT PK FROM MyTable WHERE FK='xxxx'
So the ORDER BY clause is the culprit, but WHY ???
( There is a index on PK and a index on FK )
Using CursorLocation = adUseClient will give you the expected performance
My first inclinations would be "improperly indexed" followed very closely by "parameter sniffing".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply