Optimizing a cursor based routine – Part 2

  • Comments posted to this topic are about the item Optimizing a cursor based routine – Part 2



    Clear Sky SQL
    My Blog[/url]

  • Just to nitpick something. The adventureworks sales were understated by ~ $22M, not $28M.;-)

    Nice article Dave.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thank you.

    I understand the topic of optimizing a cursor based routine pretty well and yet again another day comes where I learn how much more I have to learn. I can't wait to try out some of these techniques on some more complex reports!

  • Very nice article. We have very few cursors but I have learned other performance improving tips from this article regardless.

  • Good job Dave - very well written.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Top-notch contributions from you Dave, as usual!

    Keep up the great job.

    -- Gianluca Sartori

  • Thanks all,

    Enjoyed doing it, now to find the next set of bad code to write about 🙂



    Clear Sky SQL
    My Blog[/url]

  • Something tells me you are not a fan of AdventureWorks Joe 😉



    Clear Sky SQL
    My Blog[/url]

  • Dave, excellent job with the post! Very informative and good example to demo the technology.

    Big Question: Where can I find the AdventureWorks2008R2 database?

    I was running it against the AdventureWorks database but some of the columns/tables are missing....

    I have downloaded almost everything off codeplex, and I have found these databases:

    1) adventureworks

    2) adventureworksDW

    3) adventureworksDW2008

    4) adventureworksDW2008R2

    5) adventureworksLT

    6) adventureworksLT2008R2

    But no database named "AdventureWorks2008R2"?

  • If you don't see the DB called AdventureWorks2008R2 then it is because you don't have FILESTREAM installed/running. Go here and follow the instructions.

    http://msftdbprodsamples.codeplex.com/releases/view/59211

    (can you tell I did this already too :-))

  • First off, I would like to say good job, I don't think most of us appreciate the hard word that goes into these articles.

    The only item I will nit-pick is the use of the function with the "customerid" being passed it. Appreciating that with the amount of data you have the difference is negligible, I have had to fix way too many of these since functions came into being in SQL 2000.

    To your yoda quote "you must unlearn what you have learned", inside a function if you need to select as you do from a table you have effectively created the very cursor you are trying to avoid since SQL will call the function row by row with 1 ID at a time. The queries will generally scale better with more liberal use of temp/variable tables although they also have their limits. However what is the worst thing about the functions is that they are hidden in the execution plans, which can make them a real bugger to find sometimes.

  • Hi Dennis,

    Thanks for clarifying the filestream issue, I did follow this up in the part 1 thread but didn’t here. Oops.

    With regard to the functions, you are correct IF I had used scalar or a multi-statement table valued functions. The functionality within Inline table value function ARE seen by the optimizer and the cost can be seen in the plan. So they perform, scale and allow for code reuse.

    I did reference this in Part 1 , but take a look at http://www.sqlbits.com/Sessions/Event6/High_performance_functions for a full demonstration.



    Clear Sky SQL
    My Blog[/url]

  • hhmmm....interesting that the optimizer treats them differently, I will have to get a closer look at that, thanks for the info.

    D

  • Just a typo I noticed, in a few of your performance result tables, the actual percentage between run 2 and 3 looks to be inverted (17 and 22 instead of 22 and 17)

  • Dave Ballantyne (7/5/2011)


    I did reference this in Part 1 , but take a look at http://www.sqlbits.com/Sessions/Event6/High_performance_functions for a full demonstration.

    lol, your url got a little hijacked.

    http://www.sqlbits.com/Sessions/Event6/High_performance_functions

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

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