Very slow performance as a result of functions. What can I do?

Transact-SQL
1

I have a handful of functions that are dead simple but make it easier to read the queries. Examples are below. However I've noticed significant differences quen querying large datasets between these ranges. A 10 second query may take 3 minutes.

I've tried UPDATE STATISTICS on the relevant tables. I've also tried running the queries with OPTION (RECOMPILE). But the result is always the same.

I can just write out the code each time. But I'm wondering if there's anything I can do to keep these functions in place.

Also when running sp_whoisactive I see that each time it runs, the same CREATE function defined below also runs. It doesn't make sense since it is already there.

Any help is appreciated. Thanks!!

--- startofmonth
CREATE FUNCTION [dbo].[startofmonth] ( @date_param datetime)
RETURNS datetime AS
BEGIN
    RETURN CAST( DATEFROMPARTS(YEAR(@date_param), MONTH(@date_param), 1) AS DATE )
END;

--- endofmonth
CREATE FUNCTION [dbo].[endofmonth] ( @date_param datetime)
RETURNS datetime AS
BEGIN
    RETURN CAST(CAST(CONVERT(DATE, @date_param  ) AS NVARCHAR(MAX)) + ' 23:59:59.997' AS DATETIME)
END;

--- startofday
CREATE FUNCTION [dbo].[startofday] ( @date_param datetime)
RETURNS datetime AS
BEGIN
    RETURN CAST( @date_param AS DATE ) 
END;

--- endofday
CREATE FUNCTION [dbo].[endofday] ( @date_param datetime)
RETURNS datetime AS
BEGIN
    RETURN CAST(CAST(CONVERT(DATE, @date_param  ) AS NVARCHAR(MAX)) + ' 23:59:59.997' AS DATETIME)
END;
2

Not much to change there, maybe these. It's likely just the overhead of repeated calls to functions.

--- endofmonth
ALTER FUNCTION [dbo].[endofmonth] ( @date_param datetime)
RETURNS datetime AS
BEGIN
    RETURN DATEADD(MS, 86399997, CAST(CAST(@date_param AS date) AS datetime))
END;
GO
--- endofday
ALTER FUNCTION [dbo].[endofday] ( @date_param datetime)
RETURNS datetime AS
BEGIN
    RETURN DATEADD(MS, 86399997, CAST(CAST(@date_param AS date) AS datetime))
END;
3

That's too bad. Still it's good to know rather than to waste time on something I can't fix.

As an aside, your function brought it down from 3 minutes to a little over a minute. But keeping the conversion outside of a function is 10 seconds so, I'll write it each time. Thanks though!

4

Refactor SQL Server scalar UDF to inline TVF to improve performance (mssqltips.com)

5

Btw, your end-of-month function doesn't get you the last day of the month (like the first of month does), it just sets the time to max.

6

You could very likely get some nice performance gains by changing from scalar to inline-table functions, but you would have to rewrite the calling code. Maybe develop those functions and put them in place over time?

7

Also, I want to ring a little warning bell here. Anytime I see someone working around the time 23:59:59.997 I suspect they're trying too hard to make use of SomeDate BETWEEN StartDate AND EndDate with an inclusive EndDate, rather than use the safer pattern of (SomeDate >= StartDate AND SomeDate < EndDate) with an exclusive EndDate.