SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table function execution speed


Table function execution speed

Author
Message
Aaron N. Cutshall
Aaron N. Cutshall
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2791 Visits: 1085
I have a table function that, for some unknown reason, refuses to complete execution. In trying to determine the problem, I have copied the contents of the function to another query window, declared and set the variables to replace the function parameters, and discovered that the function returns the correct data in about a second. Yet, on the same server and using the same parameters, when I execute the function directly (SELECT * FROM dbo.fnMyFunction('ParamA','12/31/2008')), it never returns. The query will continue for literally hours until it's canceled.

When examining the execution plan for both, we discovered that they are quite different. Does anyone have any idea what would be causing this? How can the contents of a function work perfectly, but fail when wrapped in a table function?


"...when ye are in the service of your fellow beings ye are only in the service of your God." -- Mosiah 2:17
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56047 Visits: 9730
Search online for "parameter sniffing" and you'll find some good articles on that exact subject. They include solutions.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95881 Visits: 33013
From the description it sounds like an inline function, but I have to ask, is it? If it's a multi-statement UDF, that could explain the performance issues too.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Aaron N. Cutshall
Aaron N. Cutshall
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2791 Visits: 1085
It is indeed an inline statement that contains 3 CTEs and a final query referencing the CTEs. As I mentioned, when I pull the code out to a separate query, defining and setting the variables that were the parameters, it works great.


"...when ye are in the service of your fellow beings ye are only in the service of your God." -- Mosiah 2:17
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217581 Visits: 46278
That sounds just like parameter sniffing. (http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/)

Post the code and exec plan?
Does it have to be a function?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Aaron N. Cutshall
Aaron N. Cutshall
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2791 Visits: 1085
I investigated the possibility of parameter sniffing, so I changed the function from an Inline Function to a multi-line function yet the problem persists. I've attached the code for review.

I should explain that this function (and yes, it's important that it remains a function for it's referred to several queries within several stored procedures) is to identify the appropriate list of providers (physicians) within a tangled weave of medical groups. Sometimes those medical groups are merely sub-groups of larger ones (hence the recursive CTE). We need to further limit that list to those that had activity for the period in question (hence the 3rd CTE). The final query further translates the providers from identifiers used in the activity data to ones used in their medical group membership (tbAtt_Providers). I hope that this background is sufficient.

I should also point out that this function works splendidly on the development server yet seemingly hangs on the production server. Of course, the development server has only a portion of the data that the production server does. Even then, as mentioned earlier, the code will execute outside of the function in a second on the production server.


"...when ye are in the service of your fellow beings ye are only in the service of your God." -- Mosiah 2:17
Attachments
fnCurrentProviders.txt (18 views, 7.00 KB)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217581 Visits: 46278
Aaron N. Cutshall (2/19/2009)
I investigated the possibility of parameter sniffing, so I changed the function from an Inline Function to a multi-line function yet the problem persists.


How?

Can you, on your prod box, recompile the function (sp_recompile <Function name> ) and then try to run it. See if it comes back any faster.
Is this a recent occurance or has this always been broken on Production?
Can you do stats updates on all tables involved and see if that helps?

You said the plans were different. Can you please post both of those exec plans?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95881 Visits: 33013
I'm still trying to untangle the code (did you really need to put comments at the end of every line?), but if this is functional as an inline UDF, then I'd leave it that way and work on tuning it. Making it a multi-statement UDF is unlikely to do the performance any favors.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Aaron N. Cutshall
Aaron N. Cutshall
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2791 Visits: 1085
The commented lines are what I used to change it from an inline UDF to a multi-line UDF. You're correct in that it did not solve the problem, so I changed it back. Our DBA is going to add some comments of his own.


"...when ye are in the service of your fellow beings ye are only in the service of your God." -- Mosiah 2:17
Dave23
Dave23
SSC Eights!
SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

Group: General Forum Members
Points: 929 Visits: 1806
I've attached the execution plans for both the function and the run-in-console modes.
Attachments
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search