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 2 Expand / Collapse
Author
Message
Posted Monday, July 4, 2011 2:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 )
Post #1136148
Posted Monday, July 4, 2011 2:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, July 5, 2011 12:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:33 AM
Points: 1,949, Visits: 8,313
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
Posted Tuesday, July 5, 2011 7:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, July 5, 2011 9:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:56 PM
Points: 19, Visits: 16
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
Posted Tuesday, July 5, 2011 9:58 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 6, 2014 8:23 PM
Points: 73, Visits: 761
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 »

Add to briefcase ««12

Permissions Expand / Collapse