Problem with sp_cursorfetch performance

  • 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

  • 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)

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply