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