Optimizing a cursor based routine

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Dave Ballantyne » Optimizing a cursor based routine – Part 1

14 posts, Page 2 of 2

««1 2

Optimizing a cursor based routine – Part 1

Rate Topic

Display Mode

Topic Options

Author

Message

Dave Ballantyne

Posted Wednesday, June 29, 2011 2:44 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

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

twillcomp

Posted Thursday, June 30, 2011 4:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:26 AM
Points: 198, Visits: 2,372

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

jerome.landis

Posted Thursday, June 30, 2011 3:47 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 25, 2013 5:32 AM
Points: 13, Visits: 54

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

Dave Ballantyne

Posted Friday, July 01, 2011 7:04 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

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 »

14 posts, Page 2 of 2

««1 2

Permissions