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 2

16 posts, Page 2 of 2

««1 2

Optimizing a cursor based routine – Part 2

Rate Topic

Display Mode

Topic Options

Author

Message

dennis.sheen

Posted Monday, July 04, 2011 2:42 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, July 26, 2011 9:41 AM
Points: 3, Visits: 7

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 Smile

)

Post #1136148

dennis.sheen

Posted Monday, July 04, 2011 2:59 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, July 26, 2011 9:41 AM
Points: 3, Visits: 7

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.

Post #1136152

Dave Ballantyne

Posted Tuesday, July 05, 2011 12:03 AM

SSCommitted

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

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 http://www.sqlbits.com/Sessions/Event6/High_performance_functions for a full demonstration.

Clear Sky SQL
My Blog
Kent user group

Post #1136223

dennis.sheen

Posted Tuesday, July 05, 2011 7:23 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, July 26, 2011 9:41 AM
Points: 3, Visits: 7

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

D

Post #1136455

David Donovan

Posted Tuesday, July 05, 2011 9:47 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, August 10, 2012 4:18 PM
Points: 19, Visits: 15

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)

Post #1136597

ShawnTherrien

Posted Tuesday, July 05, 2011 9:58 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:08 PM
Points: 61, Visits: 616

Dave Ballantyne (7/5/2011)

I did reference this in Part 1 , but take a look at http://www.sqlbits.com/Sessions/Event6/High_performance_functions for a full demonstration.

lol, your url got a little hijacked.

http://www.sqlbits.com/Sessions/Event6/High_performance_functions

Post #1136610

« Prev Topic | Next Topic »

16 posts, Page 2 of 2

««1 2

Permissions