Optimizing a cursor based routine – Part 1

Question

Optimizing a cursor based routine – Part 1

Dave Ballantyne

SSC-Dedicated

Points: 33566
More actions
June 27, 2011 at 12:02 am

#253394

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

Clear Sky SQL
My Blog[/url]

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Nice Job 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

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 2

Thanks Jason,

It was a long hard slog to get it to this point.

Glad you enjoyed it

FYI : Part 2 is now active here http://www.sqlservercentral.com/articles/T-SQL/73889/

Clear Sky SQL
My Blog[/url]

Ken SSCoach Points: 17191 More actions · Answer 3

Nice article, well written, very informational, good explanations. I look forward to future articles from you.

wbrianwhite SSC Eights! Points: 954 More actions · Answer 4

I'm not sure why you're keeping this in a cursor at all. The report looks simple enough to just write the entire report as a big select statement. Which will be an order of magnitude faster than a cursor.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

For simplicity of example?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LutzM SSC Guru Points: 107049 More actions · Answer 6

Great article, Dave! Very well done!

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95409 More actions · Answer 7

wbrianwhite (6/28/2011)
I'm not sure why you're keeping this in a cursor at all. The report looks simple enough to just write the entire report as a big select statement. Which will be an order of magnitude faster than a cursor.

Surely you did notice that this article is "Part 1"? I would suspect that "Part 2" takes care of that! 😉

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

WayneS SSC Guru Points: 95409 More actions · Answer 8

Excellent Dave. I really like the progression you take for resolving this.

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

wbrianwhite SSC Eights! Points: 954 More actions · Answer 9

WayneS (6/28/2011)
wbrianwhite (6/28/2011)
I'm not sure why you're keeping this in a cursor at all. The report looks simple enough to just write the entire report as a big select statement. Which will be an order of magnitude faster than a cursor.
Surely you did notice that this article is "Part 1"? I would suspect that "Part 2" takes care of that! 😉

I just looked at it now that it's up. It uses a temp table and a lot of outer apply functions and multiple CTEs. It is a simple enough report to write as just a select. Anything else seems like the wrong tool. Keep it simple and describe what you want in your sql, not how to get it.

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 10

wbrianwhite (6/28/2011)
I just looked at it now that it's up. It uses a temp table and a lot of outer apply functions and multiple CTEs. It is a simple enough report to write as just a select. Anything else seems like the wrong tool. Keep it simple and describe what you want in your sql, not how to get it.

Hi Brian,

And that is really where it ends up, i took the view , that you are completley welcome to disagree with, that it was better and more informative to the general reader to break the routine down in small easy(ish) lumps.

I could of simply stated that A) the original proc is bad and B) the new proc is good and left out a lot of the in between, though i think that would of been of a lesser value.

Clear Sky SQL
My Blog[/url]

twillcomp SSC Eights! Points: 989 More actions · Answer 11

Just a couple of typo points really.

The function SumCustomerTotals returns a table with CustomerTotalDue and shippedtotal but your example function call in the 6th code box selects different column names. (I think the function name was wrong originally but is fixed now).

I also wondered if the last code box should be split to have just the GetMinOrderDates function declaration in one box and the GetSalesReport_Optimize1 procedure in a seperate box.

Avidly reading these two articles as I'm still trying to 'unlearn' the procedural mindset! Thanks.

jerome.landis Old Hand Points: 337 More actions · Answer 12

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

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 13

Hi Jerome,

Glad you enjoyed the series,

I cant confirm right now but im pretty sure the links i gave point to the correct AW database.

It may not of installed correct on your machine due to filestream issues,

try this link http://msftdbprodsamples.codeplex.com/releases/view/59211

I *think* that should be the same

Clear Sky SQL
My Blog[/url]