Does using a function calling GETDATE() in a view consistently give dramatically worse performance t-CodePudding

I have a function that gets the Monday of the week @X weeks in the past/future. I bolted it together from other code on this website. I believe it goes like this:

CREATE FUNCTION [FOO]
    (@X INT)
RETURNS DATE
AS
BEGIN
    RETURN DATEADD(WEEK, @X, DATEADD(d, -((DATEPART(DW, GETDATE()))
END

I've recently found that if I call this function inside of a view, then I get dramatically worse performance than if I copy and pasted the above code instead. In other words, I find that directly using CAST(DATEADD(WEEK, [X VALUE HERE], DATEADD(d, -((DATEPART(DW, GETDATE())) AS DATE) to be far more performant than using FOO([X VALUE HERE]).

If the activity monitor is to be trusted, it's as if the value of GETDATE() is being forgotten when you use the user-defined function; I see my user-defined function being called a great many times when I try to query any views that use it.

Is there any known cause for this behavior? It's as if making functions with GETDATE() is always a bad idea. This question hints towards as much, but it's not a direct answer.

I've checked for any type mismatches of any sort. There is none. @@VERSION reports that I'm on a 2016 version.

CodePudding user response：

This is all explained in this SqlServer 2019 article: https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15. As @JeroenMostert explained, unless you have v2019 or later and can meet all of the requirements, Scalar UDFs tend to suck performance-wise.

The only way around this prior to v2019 would be to change it into an inline Table-valued Function (iTVF) instead. These use the following syntax:

-- Transact-SQL Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

And then would have to be invoked with a JOIN, APPLY or subquery.

CodePudding user response：

@J.Mini, you said "as if making functions with GETDATE() is always a bad idea".

It is not about the GETDATE(). It is about any user-defined scalar function in SQL Server prior to 2019. Any user-defined scalar function in SQL Server prior to 2019 is a bad idea because of likely poor performance. When your code runs 10x or 100x slower your users will notice.

This makes the standard programming idiom "if you see yourself doing the same thing many times, then make it a function with a good name" to be a bad idea in T-SQL.

Other RDBMSs like Postgres and Oracle may behave differently and work perfectly fine performance-wise with user-defined functions.

It is just a "feature" (or, rather, a peculiarity) of SQL Server that you need to be aware of. Especially since you use this function in multiple places. All of these places (queries) are likely much slower than they could have been.

Here is a good article by Aaron Bertrand on this topic:

Encapsulating Common Code Into Scalar UDFs