Problem with sp_cursorfetch performance

• Gugro

  SSC Journeyman

  Points: 91

  More actions

  October 15, 2011 at 7:24 am

  #1394954

  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

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  October 15, 2011 at 12:17 pm

  #1394985

  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

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions