Very Slow SQL Server Query using Functions

Question

This sort of follows on from a previous question:

SQL Server Query time out depending on Where Clause

In which a query would run extremely slowly depending on a where clause. I rewrote that query use CTE and avoided the problem nicely, but never found an answer.

Another, similar, query was tweaked recently to add another field, and suddenly it's performance dropped from taking about ~30 seconds to run and return 10,000 rows, to taking over 10 hours (and eventually returning the same result set). Today I started to troubleshoot this one and found something weird.

I am constantly needing to extract the date only part from datetime values, and so I wrote the logic into a function:

CREATE FUNCTION [dbo].[cDate] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN
        RETURN CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME)
END
GO

I found in this new, inefficient query that if I replaced that function with the CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME) inline in the query, the speed of the query execution dropped from ~10 hours, to just under 2 seconds. I can see no difference in the estimated execution plan. By the way, this was not the field that was added. I assume adding the other field somehow caused the execution plan to change and amplified the above condition.

My question is, is this normal? I make use of functions for repetitive processes, as above, as they are easier to maintain, remember and to update if you find a more efficient way of doing something. Should I be doing something to my functions to improve their performance?

Answer 1

If you must encapsulate this in a function see Scalar functions, inlining, and performance: An entertaining title for a boring post

Rewrite it as follows

CREATE FUNCTION dbo.cDate_Inline
(
    @pInputDate DATETIME
)
RETURNS TABLE
AS
    RETURN
    (
        SELECT DATEADD(day, DATEDIFF(Day, 0, @pInputDate), 0) AS [Date]
    )

Then instead of

SELECT *,
       [dbo].[cDate](modify_date) AS modified
FROM   sys.objects 

Use

SELECT *,
       ca.Date AS modified
FROM   sys.objects
       CROSS APPLY dbo.cDate_Inline(modify_date) ca 

This will be inlined into the plan by the query optimiser.