SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Dave Ballantyne / Optimizing a cursor based routine – Part 1 / Latest Posts

RE: Optimizing a cursor based routine – Part 1

Dave Ballantyne — Fri, 01 Jul 2011 07:04:26 GMT

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 [url]http://msftdbprodsamples.codeplex.com/releases/view/59211[/url]I *think* that should be the same

RE: Optimizing a cursor based routine – Part 1

jerome.landis — Thu, 30 Jun 2011 15:47:53 GMT

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) adventureworks2) adventureworksDW3) adventureworksDW20084) adventureworksDW2008R25) adventureworksLT6) adventureworksLT2008R2But no database named "AdventureWorks2008R2"?

RE: Optimizing a cursor based routine – Part 1

twillcomp — Thu, 30 Jun 2011 04:04:48 GMT

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.

RE: Optimizing a cursor based routine – Part 1

Dave Ballantyne — Wed, 29 Jun 2011 02:44:31 GMT

[quote][b]wbrianwhite (6/28/2011)[/b]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.[/quote]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.

RE: Optimizing a cursor based routine – Part 1

wbrianwhite — Tue, 28 Jun 2011 18:14:33 GMT

[quote][b]WayneS (6/28/2011)[/b][hr][quote][b]wbrianwhite (6/28/2011)[/b][hr]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.[/quote]Surely you did notice that this article is "Part 1"? I would suspect that "Part 2" takes care of that! ;-)[/quote]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.

RE: Optimizing a cursor based routine – Part 1

WayneS — Tue, 28 Jun 2011 16:21:16 GMT

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

RE: Optimizing a cursor based routine – Part 1

WayneS — Tue, 28 Jun 2011 16:20:33 GMT

[quote][b]wbrianwhite (6/28/2011)[/b][hr]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.[/quote]Surely you did notice that this article is "Part 1"? I would suspect that "Part 2" takes care of that! ;-)

RE: Optimizing a cursor based routine – Part 1

LutzM — Tue, 28 Jun 2011 15:06:51 GMT

Great article, Dave! Very well done!

RE: Optimizing a cursor based routine – Part 1

GilaMonster — Tue, 28 Jun 2011 14:44:34 GMT

For simplicity of example?

RE: Optimizing a cursor based routine – Part 1

wbrianwhite — Tue, 28 Jun 2011 14:32:19 GMT

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.

RE: Optimizing a cursor based routine – Part 1

KWymore — Tue, 28 Jun 2011 11:59:13 GMT

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

RE: Optimizing a cursor based routine – Part 1

Dave Ballantyne — Tue, 28 Jun 2011 07:47:18 GMT

Thanks Jason,It was a long hard slog to get it to this point.Glad you enjoyed itFYI : Part 2 is now active here [url]http://www.sqlservercentral.com/articles/T-SQL/73889/[/url]

RE: Optimizing a cursor based routine – Part 1

SQLRNNR — Mon, 27 Jun 2011 16:27:34 GMT

Nice Job Dave.

Optimizing a cursor based routine – Part 1

Dave Ballantyne — Mon, 27 Jun 2011 00:02:38 GMT

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