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 123»»»

Table function execution speed Expand / Collapse
Author
Message
Posted Wednesday, February 18, 2009 2:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 9:40 AM
Points: 1,090, Visits: 540
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
Post #659911
Posted Wednesday, February 18, 2009 3:02 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #659914
Posted Wednesday, February 18, 2009 6:09 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 15,517, Visits: 27,893
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #659992
Posted Thursday, February 19, 2009 6:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 9:40 AM
Points: 1,090, Visits: 540
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
Post #660234
Posted Thursday, February 19, 2009 6:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 42,436, Visits: 35,490
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 2008, MVP
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

Post #660238
Posted Thursday, February 19, 2009 7:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 9:40 AM
Points: 1,090, Visits: 540
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


  Post Attachments 
fnCurrentProviders.txt (11 views, 7.33 KB)
Post #660263
Posted Thursday, February 19, 2009 7:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 42,436, Visits: 35,490
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 2008, MVP
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

Post #660294
Posted Thursday, February 19, 2009 7:48 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 15,517, Visits: 27,893
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #660329
Posted Thursday, February 19, 2009 7:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 9:40 AM
Points: 1,090, Visits: 540
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
Post #660342
Posted Thursday, February 19, 2009 7:57 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 252, Visits: 1,716
I've attached the execution plans for both the function and the run-in-console modes.

  Post Attachments 
CurrentProviders as Code_sqlplan.txt (11 views, 117.46 KB)
CurrentProviders as TVF_sqlplan.txt (9 views, 78.57 KB)
Post #660353
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse