Post reply

FAST_FORWARD cursor slow for simple SELECT COUNT(*) because sp_cursorfetch() re-scans all index

  • December 5, 2017 at 10:41 am

    #409827

    Hi all,

    We are using MS ODBC 13 (on Linux) to execute a simple SELECT COUNT(*) FROM table query...
    This query is 2 times slower with a FAST_FORWARD cursor as with a STATIC cursor.
    (Using a STATIC cursor is not an option for us, just in case if onoe suggests this)

    We could reproduce the problem in the SSMS query tool by tweaking the parameter @p5.

    The query plan for sp_cursorprepexec() shows an index scan => ok....
    Then the sp_cursorfetch() produces a different query plan depending on the cursor type:
    - When using a STATIC cursor (@p5=8), you see the query plan for sp_cursorfetch() makes an index seek on a single row (aggregate count).

    - When using a FAST_FORWARD cursor (@p5=16), you see the query plan for sp_cursorfetch() makes again an index scan an ALL ROWS!

    Is this normal???

    Seb

  • December 5, 2017 at 11:25 am

    #1970767

    sf 49675 - Tuesday, December 5, 2017 10:41 AM

    Hi all,

    We are using MS ODBC 13 (on Linux) to execute a simple SELECT COUNT(*) FROM table query...
    This query is 2 times slower with a FAST_FORWARD cursor as with a STATIC cursor.
    (Using a STATIC cursor is not an option for us, just in case if onoe suggests this)

    We could reproduce the problem in the SSMS query tool by tweaking the parameter @p5.

    The query plan for sp_cursorprepexec() shows an index scan => ok....
    Then the sp_cursorfetch() produces a different query plan depending on the cursor type:
    - When using a STATIC cursor (@p5=8), you see the query plan for sp_cursorfetch() makes an index seek on a single row (aggregate count).

    - When using a FAST_FORWARD cursor (@p5=16), you see the query plan for sp_cursorfetch() makes again an index scan an ALL ROWS!

    Is this normal???

    Seb

    Yes, it's normal. a FAST_FORWARD cursor won't be STATIC and it has to evaluate which is the next row.
    Two major questions would be:
    1. Why are you using a CURSOR for a single row?
    2. Why are you counting all the rows from a table? Here's a better alternative: HOW TO quickly retrieve row count

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 5, 2017 at 11:40 am

    #1970769

    Luis Cazares - Tuesday, December 5, 2017 11:25 AM

    sf 49675 - Tuesday, December 5, 2017 10:41 AM

    Hi all,

    We are using MS ODBC 13 (on Linux) to execute a simple SELECT COUNT(*) FROM table query...
    This query is 2 times slower with a FAST_FORWARD cursor as with a STATIC cursor.
    (Using a STATIC cursor is not an option for us, just in case if onoe suggests this)

    We could reproduce the problem in the SSMS query tool by tweaking the parameter @p5.

    The query plan for sp_cursorprepexec() shows an index scan => ok....
    Then the sp_cursorfetch() produces a different query plan depending on the cursor type:
    - When using a STATIC cursor (@p5=8), you see the query plan for sp_cursorfetch() makes an index seek on a single row (aggregate count).

    - When using a FAST_FORWARD cursor (@p5=16), you see the query plan for sp_cursorfetch() makes again an index scan an ALL ROWS!

    Is this normal???

    Seb

    Yes, it's normal. a FAST_FORWARD cursor won't be STATIC and it has to evaluate which is the next row.
    Two major questions would be:
    1. Why are you using a CURSOR for a single row?
    2. Why are you counting all the rows from a table? Here's a better alternative: HOW TO quickly retrieve row count

    Thanks for this quick reply and tips!
    A1: We are using server cursors because we implement an SQL interface (driver) without our own API, on top of ODBC. We have to implement instructions such as PREPARE stmt FROM sql-text, EXECUTE stmt USING params, OPEN cursor USING params, FETCH cursor ... etc. One important constraint is to support multiple active cursors at the same time, this is why we use FFO server cursors. We know there is Multiple Active Result Sets (MARS) feature since SQL Server 2005, but so far it was ok to use FFO server-side cursors...
    A2: "We" are not counting rows: it's our customer, implementing its application code, which is doing this SELECT. We will look at the HOW TO.
    Seb

  • December 5, 2017 at 11:57 am

    #1970772

    sf 49675 - Tuesday, December 5, 2017 11:40 AM

    Luis Cazares - Tuesday, December 5, 2017 11:25 AM

    sf 49675 - Tuesday, December 5, 2017 10:41 AM

    Hi all,

    We are using MS ODBC 13 (on Linux) to execute a simple SELECT COUNT(*) FROM table query...
    This query is 2 times slower with a FAST_FORWARD cursor as with a STATIC cursor.
    (Using a STATIC cursor is not an option for us, just in case if onoe suggests this)

    We could reproduce the problem in the SSMS query tool by tweaking the parameter @p5.

    The query plan for sp_cursorprepexec() shows an index scan => ok....
    Then the sp_cursorfetch() produces a different query plan depending on the cursor type:
    - When using a STATIC cursor (@p5=8), you see the query plan for sp_cursorfetch() makes an index seek on a single row (aggregate count).

    - When using a FAST_FORWARD cursor (@p5=16), you see the query plan for sp_cursorfetch() makes again an index scan an ALL ROWS!

    Is this normal???

    Seb

    Yes, it's normal. a FAST_FORWARD cursor won't be STATIC and it has to evaluate which is the next row.
    Two major questions would be:
    1. Why are you using a CURSOR for a single row?
    2. Why are you counting all the rows from a table? Here's a better alternative: HOW TO quickly retrieve row count

    Thanks for this quick reply and tips!
    A1: We are using server cursors because we implement an SQL interface (driver) without our own API, on top of ODBC. We have to implement instructions such as PREPARE stmt FROM sql-text, EXECUTE stmt USING params, OPEN cursor USING params, FETCH cursor ... etc. One important constraint is to support multiple active cursors at the same time, this is why we use FFO server cursors. We know there is Multiple Active Result Sets (MARS) feature since SQL Server 2005, but so far it was ok to use FFO server-side cursors...
    A2: "We" are not counting rows: it's our customer, implementing its application code, which is doing this SELECT. We will look at the HOW TO.
    Seb

    Maybe there is some option in sp_cursorprepexec() to force to fetch the first rows and avoid to re-scan the index at the first sp_cursorfetch() call??
    Seb

  • December 5, 2017 at 12:06 pm

    #1970774

    sf 49675 - Tuesday, December 5, 2017 11:57 AM

    Maybe there is some option in sp_cursorprepexec() to force to fetch the first rows and avoid to re-scan the index at the first sp_cursorfetch() call??
    Seb

    What if the data changes? You need to be prepared to handle concurrency.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 6, 2017 at 2:47 am

    #1970877

    Luis Cazares - Tuesday, December 5, 2017 12:06 PM

    sf 49675 - Tuesday, December 5, 2017 11:57 AM

    Maybe there is some option in sp_cursorprepexec() to force to fetch the first rows and avoid to re-scan the index at the first sp_cursorfetch() call??
    Seb

    What if the data changes? You need to be prepared to handle concurrency.

    My understanding is that FFO cursors are dynamic and sensitive to data changes (inserts/updates/deletes on table should be visible when processing the result set with successive sp_cursorfetch calls)...
    Is this correct?

    It appears that with a SELECT COUNT(*) it is the case. See example below, in a table with 3 rows, I insert a row (#4) between sp_cursorprepexec() and sp_cursorfetch(), and the total of rows shows 4.
    But when fetching the table rows with SELECT * , if I insert a new row #5 and delete row #4, it is not visible in the further sp_cursorfetch() calls ... that is confusing to me... can you explain that?

    Note that sp_cursorfetch is called with last param = 1 to fetch a single row at a time.


    create table tab1 ( pkey integer, name varchar(30));
    insert into tab1 values ( 1, 'Item 1' );
    insert into tab1 values ( 2, 'Item 2' );
    insert into tab1 values ( 3, 'Item 3' );

    declare @p1 int
    set @p1=NULL
    declare @p2 int
    set @p2=0
    declare @p5 int
    set @p5=16
    declare @p6 int
    set @p6=8193
    declare @p7 int
    set @p7=0
    exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'select count(*) from tab1',@p5 output,@p6 output,@p7 output
    insert into tab1 values ( 4, 'Item 4' );
    exec sp_cursorfetch @p2,2,0,1

    set @p1=NULL
    set @p2=0
    set @p5=16
    set @p6=8193
    set @p7=0
    exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'select * from tab1 order by pkey',@p5 output,@p6 output,@p7 output
    exec sp_cursorfetch @p2,2,0,1
    exec sp_cursorfetch @p2,2,0,1
    exec sp_cursorfetch @p2,2,0,1
    insert into tab1 values ( 5, 'Item 5' );
    delete from tab1 where pkey = 4;
    exec sp_cursorfetch @p2,2,0,1
    exec sp_cursorfetch @p2,2,0,1

    Results:

    SELECT COUNT(*)   =  4    (new inserted row is visible)
    sp_cursorfetch() #1 => 1, Item 1
    sp_cursorfetch() #2 => 2, Item 2
    sp_cursorfetch() #3 => 3, Item 3
    sp_cursorfetch() #4 => 4, Item 4     (after insert + delete!)
    sp_cursorfetch() #5 => end of result set / not found

    Tested with SQL Server 2017

  • December 6, 2017 at 2:54 am

    #1970879

    Hummm.... it appears that the ORDER BY is the reason!
    Where removing the ORDER BY clause in the SELECT * , I can see the changes in the table (row #4 disappears, row #5 shows up).
    I assume that this behavior is driven by the different query plans...
    But confusing to me...
    Seb

  • December 6, 2017 at 3:09 am

    #1970882

    Luis Cazares - Tuesday, December 5, 2017 12:06 PM

    sf 49675 - Tuesday, December 5, 2017 11:57 AM

    Maybe there is some option in sp_cursorprepexec() to force to fetch the first rows and avoid to re-scan the index at the first sp_cursorfetch() call??
    Seb

    What if the data changes? You need to be prepared to handle concurrency.

    Reading carefully the API doc about sp_cursorprepexec(), I found that one can use the AUTO_FETCH flag with FAST_FORWARD cursors...
    Need to set @p5 to 8208 (0x2000 AUTO_FETCH + 0x0010 FAST_FORWARD) and @p7 to the number of rows to be fetched.
    Must now check if that can be controlled from ODBC API.
    Seb

Viewing 8 posts - 1 through 7 (of 7 total)

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