Table function execution speed

  • 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?

  • 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

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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.

  • 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
  • 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.

  • 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
  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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.

  • I've attached the execution plans for both the function and the run-in-console modes.

  • OK. This is going to lead to table scans:

    WHERE R.MedGroupID = ISNULL(@MedGroupID,

    R.MedGroupID)

    And you have it all over the place. The DISTINCT is probably causing problems as well. Check the query, the data & the structure to see if there is some better way to store or retrieve it.

    Both the execution plans are estimated plans. It's hard to understand what's happening from those. Can you get the actual plans?

    One thing from the plans though, in the Code_sqlplan, you're getting a key lookup that could probably be changed by using an INCLUDE on the index since it's only returning two columns. It's 88% of the estimated cost (which, I know isn't the same as actual). It's also on the other plan, but is only 4% of the estimated cost. It looks like low-hanging fruit. I'd pick it.

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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I can certainly see your concern about this:

    WHERE R.MedGroupID = ISNULL(@MedGroupID, R.MedGroupID)

    But I'm not sure what else I can do to allow the MedGroupID to be specified yet if it's NULL to allow all values. I initially had something like this:

    WHERE (@MedGroupID IS NULL OR R.MedGroupID = @MedGroupID)

    but it seemed to cause some problems with properly selecting data. Have you another suggestion?

    I agree with you on the DISTINCT and I'm working to resolve that one. Dave did recalc the statistics on the tables used and we finally got the function to execute. It took six times longer than the query by itself, but it did work. We're now doing the recalc on all tables to see if that will solve some other issues as well.

    Sorry for the estimated plans, but since we couldn't get the function to operate, we were unable to get actual plans.

    Could you please elaborate on your comments about the key lookup? I'm not sure what you mean by the INCLUDE on the index.

  • Aaron N. Cutshall (2/19/2009)


    Sorry for the estimated plans, but since we couldn't get the function to operate, we were unable to get actual plans.

    Could you post the actual plan for the one that does run? The ad-hoc query straight in management studio. It would really help to see the actual rows affected by the various seeks and scans.

    Could you please elaborate on your comments about the key lookup? I'm not sure what you mean by the INCLUDE on the index.

    Books Online - "include columns"?

    Included columns are columns that are in the leaf pages of the index, but not in the index key. Since they're in the index SQL doesn't have to do a bookmark lookup/key lookup back to the cluster/heap to find those columns. Bookmark lookups/key lookups are very expensive.

    They're a new feature in SQL 2005.

    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 (2/19/2009)


    Sorry for the estimated plans, but since we couldn't get the function to operate, we were unable to get actual plans.

    The problem is, especially when dealing with multi-statement UDF's, estimated plans lie.

    Could you please elaborate on your comments about the key lookup? I'm not sure what you mean by the INCLUDE on the index.

    I'm sorry I wasn't clear. If you look at the execution plans, you'll see an operator labeled "Key Lookup." This is what used to be called a bookmark lookup. It means that the engine was able to pull some of the data it needed from an index, but couldn't get all the data, so it's doing a lookup on the clustered index key (that's stored with the non-clustered index) to get the last of the data. When you hover your mouse over the operator in question, you'll see two columns are all that is being OUTPUT from the lookup operation, tbAtt_Patient.SourceId & tbAtt_Patient.IsParticipating. These two columns can be added to the nonclustered index, IX_tbAtt_Patient_RIGlobalProviderID, but not to the index itself, which could change how it's stored and accessed, but as INCLUDE columns. You can look that up in Books Online.

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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks for the explanation. Apparently, recalculating the statistics did wonders. The function executes now!! :w00t:

    Still, I'm going to investigate the index issue. While SourceID is indeed on an index, the IsParticipating flag is not, so I'll correct that.

    What are your thoughts about the issue of the optionally specified parameter?

    WHERE (@MedGroupID IS NULL OR R.MedGroupID = @MedGroupID)

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply