Optimizing a cursor based routine – Part 2

Question

Post reply

Optimizing a cursor based routine – Part 2

Dave Ballantyne

SSC-Dedicated

Points: 33667
More actions
June 27, 2011 at 9:20 pm

#253627

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

Clear Sky SQL
My Blog[/url]

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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

ShawnTherrien Default port Points: 1481 More actions · Answer 2

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!

Ken SSCoach Points: 17191 More actions · Answer 3

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

WayneS SSC Guru Points: 95460 More actions · Answer 4

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Top-notch contributions from you Dave, as usual!

Keep up the great job.

-- Gianluca Sartori

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

Thanks all,

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

Clear Sky SQL
My Blog[/url]

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 7

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

Clear Sky SQL
My Blog[/url]

jerome.landis Old Hand Points: 367 More actions · Answer 8

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"?

dennis.sheen Valued Member Points: 71 More actions · Answer 9

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

dennis.sheen Valued Member Points: 71 More actions · Answer 10

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.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 11

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]

dennis.sheen Valued Member Points: 71 More actions · Answer 12

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

D

David Donovan SSC Rookie Points: 34 More actions · Answer 13

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)

ShawnTherrien Default port Points: 1481 More actions · Answer 14

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