Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

SQL Server 2008 R2

I know this is a common problem (eg: http://connect.microsoft.com/SQLServer/feedback/details/524983/user-defined-function-performance-is-unacceptable) - but ive only just learned of it.

We have a live production database that has performed fine for months, specifically a stored procedure that contains a large CTE that selects into a table variable. Then, based on a parameter on the procedure - data is selected from the table variable in various ways (column sequence, order by clause etc).

This temporary table only has a few hundred rows inserted into it from the CTE - the source data is huge, but the resulting rows are always low in number)

The select satements used on the table variable use 2 very simple UDF's:

CREATE FUNCTION [dbo].[format_timeV2] ( @Time INT )
RETURNS VARCHAR(20)
AS 
    BEGIN
        DECLARE @Time_Varchar VARCHAR(20)
        SET @Time_Varchar = CONVERT(VARCHAR, @Time / 3600) + 'h ' + CONVERT(VARCHAR, ROUND(( ( CONVERT(FLOAT, ( @Time % 3600 )) ) / 3600 ) * 60, 0)) + 'm'
        RETURN @Time_Varchar
    END
GO

And

CREATE FUNCTION [dbo].[udf_WeekEndDate] ( @Date DATETIME )
RETURNS DATETIME
AS 
    BEGIN
        RETURN DATEADD(DAY, 7, CONVERT(DATETIME, CONVERT(VARCHAR(10), DATEADD(day, -1 - ( DATEPART(dw, @Date) + @@DATEFIRST - 2 ) % 7, @Date), 103) + ' 23:59:59', 103))
    END
GO

These UDF's have been working fine for months, if not years, the stored procedure in question has ALWAYS ran in under 6 seconds - but as soon as the UDF's above are included on the SELECT statement of the table variable the procedure takes MINUTES!

As soon as we comment out the usage of these functions it returns to executing in 3-6 seconds.

These functions are only being used on the SELECT from @TableVar statement (few hundred rows) - so i dont see why the query optimiser if struggling here?!?!

If we run the following - the UDF's and procedure start to work normally again:

DBCC FreeProcCache
DBCC DropCleanbuffers

So i can only assume the query optimiser is doing something stupid in the background - but it's not clear on the execution plan as to what it is.

I have tried recreating the proc with RECOMPILE - this didnt help, only FreeProcCache & DropCleanbuffers helped.

Any ideas what the root cause of this? we dont really want to go re-writing all the UDFs as table valued function (which i understand is a work around for this problem) as this is a live production database which has been working fine for years.

EDIT 1: Blam requested detail of the actual query that hangs:

    IF @ModeInt = 2 
        SELECT  [DET_NUMBERA] ,
                [Name] ,
                [Branch] ,
                COUNT(*) OVER ( PARTITION BY [DET_NUMBERA] ) AS RowsForThisEmployee ,
                CONVERT(VARCHAR(10), dbo.udf_WeekEndDate([Date]), 103) AS [WeekEnding] ,
                [Date] ,
                [Start Time] ,
                [End Time] ,
                [Duration] ,
                dbo.format_timeV2([Duration] * 60) AS [DurationF] ,
                [EntryCount] ,
                [EntryColour] ,
                [DurationColour] ,
                [DurationComment]
        FROM    @CompletedData
        WHERE   [EntryCount] = 0
        ORDER BY [DET_NUMBERA] ,
                [Date] ,
                [Start Time]

    IF @ModeInt = 3 
        SELECT  [DET_NUMBERA] ,
                [Name] ,
                [Branch] ,
                COUNT(*) OVER ( PARTITION BY [DET_NUMBERA] ) AS RowsForThisEmployee ,
                CONVERT(VARCHAR(10), dbo.udf_WeekEndDate([Date]), 103) AS [WeekEnding] ,
                [Date] ,
                [Start Time] ,
                [End Time] ,
                [Duration] ,
                dbo.format_timeV2([Duration] * 60) AS [DurationF] ,
                [EntryCount] ,
                [EntryColour] ,
                [DurationColour] ,
                [DurationComment]
        FROM    @CompletedData
        WHERE   [EntryCount] > 1
        ORDER BY [DET_NUMBERA] ,
                [Date] ,
                [Start Time]

The temp table doesnt have an index (didnt think it was needed as it only ever contains 100 - 200 rows:

    DECLARE @CompletedData TABLE
        (
          [DET_NUMBERA] VARCHAR(7) ,
          [Name] VARCHAR(255) ,
          [Branch] VARCHAR(3) ,
          [Date] DATE ,
          [Start Time] TIME(0) ,
          [End Time] TIME(0) ,
          [Duration] INT ,
          [EntryCount] INT ,
          [EntryColour] VARCHAR(15) ,
          [DurationColour] VARCHAR(15) ,
          [DurationComment] VARCHAR(65)
        )

The CTE populates the table in 3 seconds, its only the above SELETS that make use of UDF's that hang, if i remove the UDF's from the SELECT it works instantly.

share|improve this question

migrated from stackoverflow.com Oct 31 '12 at 14:16

This question came from our site for professional and enthusiast programmers.

1  
Does the table variable have an index? Please post a query with bad performance. –  Blam Oct 31 '12 at 13:32
1  
Just curious. Have you tried "SELECT from @TableVar OPTION(RECOMPILE)"? Table variables are known to get an estimated row count of 1 due to not having statistics. Using OPTION(RECOMPILE) may help with the estimated row count on the select statement. sqlservercentral.com/blogs/dave_ballantynes_blog/2011/12/02/… –  brian Oct 31 '12 at 13:35
    
@brian I hadnt brian, i will try that if it happens again (its performing fine now since the use of FreeProcCache) - but as i say, this table var has 100, maybe 200 rows max - even without index or assumption of 1 row in the planner - i dont see why the use of a simple UDF would have such an impact? –  HeavenCore Oct 31 '12 at 13:37
1  
This is far from the solution to your problem (hence a comment), but all the varchar conversion in your weekend date function is superfluous, you can simply use DATEADD(SECOND, -1, DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date) + 1, 0)); –  GarethD Oct 31 '12 at 13:40
1  
The other side is can create an index cheap on 200 rows. Some times an index will get the query optimizer to do the right thing. But you are not even doing a join. A reach but maybe try an index on the order by and when you insert sort by that. –  Blam Oct 31 '12 at 14:01
show 2 more comments

1 Answer 1

up vote 3 down vote accepted

3 things I would do.

  1. Use #temporary tables instead of @table variables. Both get materialized in tempdb, but using #temporary tables gives you greater flexibility and better information to the Query Optimizer.

  2. Perform the COUNT in a classic GROUP BY query prior rather than using it as a windowing function. These are known to work terribly in SQL Server for certain conditions. Feel free to compare the plans generated.

  3. Drop the usage of the functions. The expressions are very simple and can be directly included in the query

e.g.

SELECT  a.[DET_NUMBERA] ,
        a.[Name] ,
        a.[Branch] ,
        G.RowsForThisEmployee ,
        CONVERT(CHAR(10), a.[Date] +6 - (DATEPART(dw, a.[Date]) + @@DATEFIRST -2) % 7, 103) [WeekEnding],
        a.[Date] ,
        a.[Start Time] ,
        a.[End Time] ,
        a.[Duration] ,
        RIGHT(a.[Duration]/60, 10) + 'h ' + RIGHT(a.[Duration]%60,10) + 'm' [DurationF], 
        a.[EntryCount] ,
        a.[EntryColour] ,
        a.[DurationColour] ,
        a.[DurationComment]
FROM    #CompletedData a
JOIN    (SELECT   [DET_NUMBERA], COUNT(*) RowsForThisEmployee
         FROM     #CompletedData
         WHERE    a.[EntryCount] = 0
         GROUP BY [DET_NUMBERA]) G ON a.[DET_NUMBERA] = G.[DET_NUMBERA]
WHERE    a.[EntryCount] = 0
ORDER BY a.[DET_NUMBERA] ,
         a.[Date] ,
         a.[Start Time];
share|improve this answer
    
+1 This is a good answer. One caveat: statistics on the temporary table might not behave how you expect them to. Be careful with this. –  Paul White Nov 2 '12 at 12:19
    
I think option 3 is definitely the way to go :'( these functions are used all over the place :'( –  HeavenCore Nov 2 '12 at 12:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.