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

RE: Optimizing a cursor based routine – Part 2

ShawnTherrien — Tue, 05 Jul 2011 09:58:47 GMT

[quote][b]Dave Ballantyne (7/5/2011)[/b][hr]I did reference this in Part 1 , but take a look at [url]http://www.sqlbits.com/Sessions/Event6/High_performance_functions for a full demonstration.[/url][/quote]lol, your url got a little hijacked.[url]http://www.sqlbits.com/Sessions/Event6/High_performance_functions[/url]

RE: Optimizing a cursor based routine – Part 2

David Donovan — Tue, 05 Jul 2011 09:47:46 GMT

Just a typo I noticed, in a few of your performance result tables, the actual percentage between run 2 and 3 looks to be inverted (17 and 22 instead of 22 and 17)

RE: Optimizing a cursor based routine – Part 2

dennis.sheen — Tue, 05 Jul 2011 07:23:55 GMT

hhmmm....interesting that the optimizer treats them differently, I will have to get a closer look at that, thanks for the info.D

RE: Optimizing a cursor based routine – Part 2

Dave Ballantyne — Tue, 05 Jul 2011 00:03:38 GMT

Hi Dennis,Thanks for clarifying the filestream issue, I did follow this up in the part 1 thread but didn’t here. Oops.With regard to the functions, you are correct IF I had used scalar or a multi-statement table valued functions. The functionality within Inline table value function ARE seen by the optimizer and the cost can be seen in the plan. So they perform, scale and allow for code reuse.I did reference this in Part 1 , but take a look at [url]http://www.sqlbits.com/Sessions/Event6/High_performance_functions for a full demonstration.[/url]

RE: Optimizing a cursor based routine – Part 2

dennis.sheen — Mon, 04 Jul 2011 14:59:38 GMT

First off, I would like to say good job, I don't think most of us appreciate the hard word that goes into these articles.The only item I will nit-pick is the use of the function with the "customerid" being passed it. Appreciating that with the amount of data you have the difference is negligible, I have had to fix way too many of these since functions came into being in SQL 2000. To your yoda quote "you must unlearn what you have learned", inside a function if you need to select as you do from a table you have effectively created the very cursor you are trying to avoid since SQL will call the function row by row with 1 ID at a time. The queries will generally scale better with more liberal use of temp/variable tables although they also have their limits. However what is the worst thing about the functions is that they are hidden in the execution plans, which can make them a real bugger to find sometimes.

RE: Optimizing a cursor based routine – Part 2

dennis.sheen — Mon, 04 Jul 2011 14:42:06 GMT

If you don't see the DB called AdventureWorks2008R2 then it is because you don't have FILESTREAM installed/running. Go here and follow the instructions.http://msftdbprodsamples.codeplex.com/releases/view/59211(can you tell I did this already too :-))

RE: Optimizing a cursor based routine – Part 2

jerome.landis — Thu, 30 Jun 2011 15:51:20 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 2

Dave Ballantyne — Wed, 29 Jun 2011 13:17:35 GMT

Something tells me you are not a fan of AdventureWorks Joe ;)

RE: Optimizing a cursor based routine – Part 2

CELKO — Wed, 29 Jun 2011 13:03:00 GMT

[quote][b]Dave Ballantyne (6/29/2011)[/b][hr]Thanks all,Enjoyed doing it, now to find the next set of bad code to write about :)[/quote]Actually, you can go back to the DDL in this nightmare. Singular names for tables, vague names for columns, etc. Tables for the same data model split into different locations. My favor was "FROM Store.Store" -- I was waiting for a column "Store.Store.store" to complete the pain for the bastard that has to maintian this. The idiot that did this thinks that a customer is an attribute of a store, and not in a relationship with the store. Etc. There is so little in this schema. Basically this is an EAV and OO design mashed into SQL. You even have functions to mimic methods! Is it too much to ask that MS actually follow basic ISO standards and data modeling? Rant, rant.

RE: Optimizing a cursor based routine – Part 2

Dave Ballantyne — Wed, 29 Jun 2011 02:54:02 GMT

Thanks all,Enjoyed doing it, now to find the next set of bad code to write about :)

RE: Optimizing a cursor based routine – Part 2

spaghettidba — Tue, 28 Jun 2011 17:02:47 GMT

Top-notch contributions from you Dave, as usual!Keep up the great job.

RE: Optimizing a cursor based routine – Part 2

WayneS — Tue, 28 Jun 2011 16:18:38 GMT

Good job Dave - very well written.

RE: Optimizing a cursor based routine – Part 2

KWymore — Tue, 28 Jun 2011 11:56:09 GMT

Very nice article. We have very few cursors but I have learned other performance improving tips from this article regardless.

RE: Optimizing a cursor based routine – Part 2

ShawnTherrien — Tue, 28 Jun 2011 11:03:08 GMT

Thank you. I understand the topic of optimizing a cursor based routine pretty well and yet again another day comes where I learn how much more I have to learn. I can't wait to try out some of these techniques on some more complex reports!

RE: Optimizing a cursor based routine – Part 2

SQLRNNR — Tue, 28 Jun 2011 09:55:37 GMT

Just to nitpick something. The adventureworks sales were understated by ~ $22M, not $28M.;-)Nice article Dave.

Optimizing a cursor based routine – Part 2

Dave Ballantyne — Mon, 27 Jun 2011 21:20:33 GMT

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