Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Optimizing a cursor based routine – Part 1 Expand / Collapse
Author
Message
Posted Wednesday, June 29, 2011 2:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
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
Kent user group
Post #1133499
Posted Thursday, June 30, 2011 4:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 4:25 AM
Points: 198, Visits: 2,488
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.




Post #1134316
Posted Thursday, June 30, 2011 3:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 6:03 AM
Points: 13, Visits: 67
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"?
Post #1134750
Posted Friday, July 1, 2011 7:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
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
Kent user group
Post #1135002
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse