UDF performance suddenly degraded

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

3 things I would do.

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

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

-
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];

StackExchange Database Administrators Q#27957, answer score: 3