SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Optimizing a cursor based routine – Part 1


Optimizing a cursor based routine – Part 1

Author
Message
Dave Ballantyne
Dave Ballantyne
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12638 Visits: 8370
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
twillcomp
twillcomp
SSC-Addicted
SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)

Group: General Forum Members
Points: 494 Visits: 2633
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
jerome.landis
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 97
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
Dave Ballantyne
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12638 Visits: 8370
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search