CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2012user-defined-functions
Mihai Tibrea
Mihai Tibrea

Reputation: 641

Stored procedure execution taking long because of function used inside

In SQL Server 2012 I have the following user defined function:

CREATE FUNCTION [dbo].[udfMaxDateTime]()
RETURNS datetime
AS 
BEGIN 
    RETURN '99991231';
END;

This is then being used in a stored procedure like so:

DECLARE @MaxDate datetime = dbo.udfMaxDateTime();

DELETE FROM TABLE_NAME
WHERE 
    ValidTo = @MaxDate
AND
    Id NOT IN
    (
        SELECT 
            MAX(Id)
        FROM 
            TABLE_NAME 
        WHERE
            ValidTo = @MaxDate
        GROUP 
        BY 
            COL1
        );

Now, if I run the stored procedure with the above code, it takes around 12 seconds to execute. (1,2 million rows)

If I change the WHERE clauses to ValidTo = '99991231' then, the stored procedure runs in under 1 second and it runs in Parallel.

Could anyone try and explain why this is happening ?

Upvotes: 1

Views: 52

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32685

It is not because of the user-defined function, it is because of the variable.

When you use a variable @MaxDate in the DELETE query optimizer doesn't know the value of this variable when generating the execution plan. So, it generates a plan based on available statistics on the ValidTo column and some built-in heuristics rules for cardinality estimates when you have an equality comparison in a query.

When you use a literal constant in the query the optimizer knows its value and can generate a more efficient plan.

If you add OPTION(RECOMPILE) the execution plan would not be cached and would be always regenerated and all parameter values would be known to the optimizer. It is quite likely that query will run fast with this option. This option does add a certain overhead, but it is noticeable only when you run a query very often. 

DECLARE @MaxDate datetime = dbo.udfMaxDateTime();

DELETE FROM TABLE_NAME
WHERE 
    ValidTo = @MaxDate
AND
    Id NOT IN
    (
        SELECT 
            MAX(Id)
        FROM 
            TABLE_NAME 
        WHERE
            ValidTo = @MaxDate
        GROUP BY 
            COL1
    )
OPTION(RECOMPILE);

I highly recommend to read Slow in the Application, Fast in SSMS by Erland Sommarskog.

Upvotes: 3

Related Questions