Cursor optimization

  • Comments posted to this topic are about the item Cursor optimization


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hmm... I got this wrong, based on the BOL it's supposed to be FAST_FORWARD that is optimized for performance, but apparently, based on your paper, it's not. But I'm happy to lose the point as I learn something new again. I take my hat off to you for doing the testing.


    Urbis, an urban transformation company

  • I lost the point as well, fortunately for the better of learning something new.

    This was very interesting to me, as all the years of just trusted BOL in being correct, without doing my own investigation into performance testing.

    Going forward I will definitely remember this one, but then again I hope I don't have to make use of cursors going forward, thanks for the tip Hugo

  • A very worth while read, but I'm not sure if I agree with the conclusion that STATIC is always faster than FAST_FORWARD. The table fitting in cache and point 5 of the conclusion, "It Depends", being my two main gripes. That said, whenever I do use CURSORS (???) I'll make sure I test them with STATIC.

    Cheers!

  • Does anybody have a ready made query to find out the list of all the stored procedures in a db having the word 'CURSOR' inside their SQL body text? 🙂

  • I think this should do

    SELECT OBJECT_NAME(object_id), * FROM sys.sql_modules WHERE definition LIKE '%CURSOR%'

    🙂

  • i use this to find text in any db object:

    -----------------------------------------------------

    declare @criteria as varchar(32)

    set @criteria = 'tadMSVRAreaDetail'

    --:Utility proc for searching for dependents.":--

    Select 'Checking references for (' + @criteria + ')'

    Select distinct sysobjects.id, name, type

    from syscomments, sysobjects

    where syscomments.id = sysobjects.id

    and ( text like '%' + @criteria + '%' OR text like @criteria + '%' )

    order by name

    --check for dependencies.

    exec sp_depends @criteria

    -----------------------------------------------------

    I found it somewhere on the net, so i 'm not taking credit for this code, but I can tell you it's very handy

  • Learned something new today... STATIC is faster, tested in a sproc I was trying to optimize.

    Will probably end up rewriting it completely, need more speed...

  • Iggy, Marius, Mark: Thanks for the kind words! 🙂

    This is the first time I submitted a QotD, but with reactions like this, I'm sure I'll try to submit some more!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Dave F (7/8/2008)


    A very worth while read, but I'm not sure if I agree with the conclusion that STATIC is always faster than FAST_FORWARD. The table fitting in cache and point 5 of the conclusion, "It Depends", being my two main gripes. That said, whenever I do use CURSORS (???) I'll make sure I test them with STATIC.

    Cheers!

    Dave,

    You actually made a very good point about the influence of the cache. I must admit that I completely overlooked that point when doing my tests. :blush:

    I have just done some tests with a huge table (100,000 rows with 8,016 bytes each - two integer columns and a char(8000)) on a memory-constrained system (simulated by setting the max server memory option to 64 MB and then restarting the service). It shows that you are completely right. If I use a cursor to fetch only the two integer columns, FAST_FORWARD completes in ~ 18 seconds whereas STATIC takes ~21.5 seconds - and if I process the char(8000) column as well, FAST_FORWARD still takes only ~ 18.5 seconds, and STATIC goes up to almost 80 (!) seconds.

    So it seems that for tables larger than the available cache, FAST_FORWARD does indeed deliver as advertised in Books Online. I'll shoot off a mail to Steve, asking him to adjust the question and award back points where appropriate.

    Thanks for putting me straight! 😀


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Schweet..

    Just make sure your QotD isn't confusing, misleading, can be misinterpreted or anything, cause i've seen where the guys were taken to the cleaners because the submitted question was either confusing, misleading, or misinterpreted from whomever's point of view, and yes, the question was submitted with good intentions and then badly received....

    we all should strive to submit at least 1 QotD, one can only benefit from it

    all the best Hugo

  • davidthegray (7/8/2008)


    Does anybody have a ready made query to find out the list of all the stored procedures in a db having the word 'CURSOR' inside their SQL body text? 🙂

    Hi David,

    Emamet already gave a query that works for SQL Server 2005 (and probably 2008 as well).

    For SQL Server 2000, you can use the query posted by Marius, though there are some caveats with it that I'll point out in a seperate reply.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Marius Els (7/8/2008)


    i use this to find text in any db object:

    Hi Marius,

    Mostly good, solid code, but there are a few caveats:

    Select distinct sysobjects.id, name, type

    from syscomments, sysobjects

    where syscomments.id = sysobjects.id

    and ( text like '%' + @criteria + '%' OR text like @criteria + '%' )

    order by name

    Using syscomments and sysobjects is fine for SQL Server 2000, but they are deprecated since SQL Server 2005 introduced the new system views.

    One (minor) problem with this code is that syscomments stores a stored procedure text in chunks of 8000 characters. If the word you are searching for is just on the border of two chunks, it'll be cut in halve and this code won't find it. The chances of that are, of course, prettty slim 🙂

    Finally, you can simplify the fourth line in the query to "and text like '%' + @criteria + '%'", snice the second part (searching for @criteria + '@') will never match rows not matched by the first part.

    --check for dependencies.

    exec sp_depends @criteria

    This is fine when you are looking for table, view, function, or stored procedure names (though sp_depends to be less than accurate under some circumstances), but not when you are looking for procedures that use specific keywords (such as CURSOR).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Mark Horninger (7/8/2008)


    Will probably end up rewriting it completely, need more speed...

    Mark,

    If possible, try eliminating the cursor completely rather than rewriting it using other options.... 🙂


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 🙂 Was a bit surprised at FAST_FORWARD being wrong ... seemed such an 'obvious' answer that there was bound to be some additional complexity somewhere.

    Also always with performance, it depends on context :/ .

    Appreciate the blog entry supplied though. More knowledge always good.

Viewing 15 posts - 1 through 15 (of 65 total)

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