Does using a function calling GETDATE() in a view consistently give dramatically worse performance than using GETDATE() directly?

Question

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.

Your title talks about a `VIEW`, but your question only has a `FUNCTION`. A `VIEW` and `FUNCTION` are completely different; especially when the function is a user defined scalar function. — Thom A, Jun 28 '22 at 14:48
One thing that may contribute to a performance difference between the two is that the scalar-valued UDF will force serial execution of the `VIEW`-defining query — paneerakbari, Jun 28 '22 at 14:49
What version of SQL Server are you on as well? Mult-line functions are known to perform poorly, and inlining of scalar functions was only introduced in SQL Server 2019. — Thom A, Jun 28 '22 at 14:50
*"See the first line after the code block"* `CREATE FUNCTION [FOO]`... So where is the **`VIEW`**? — Thom A, Jun 28 '22 at 14:50
@Larnu Either 2012 or 2015. I want to say 2015. I know that I don't have that handy STRING_AGG function but that I do have user-defined types. — J. Mini, Jun 28 '22 at 14:51
Scalar-valued functions almost always suck, unless you're using SQL Server 2019+ *and* you hit none of the bugs and exceptions for inlining them. For anything else, avoid them if there's any possibility of them getting called for more than one row, and either inline the code, or use an inline table-valued function (which definitely seems overkill for this). — Jeroen Mostert, Jun 28 '22 at 14:51
@Larnu Just under the code block, I've written "*I've recently found that if I call this function inside of a view*". To be more specific, I use this code to filter views. For example, `WHERE FOO(2) BETWEEN START_DATE AND STOP_DATE`. — J. Mini, Jun 28 '22 at 14:52
There's no such thing as SQL Server 2015, @J.Mini , so presumably 2012 (which is 2 weeks away from end of complete support). I would suggest that you avoid Multi-line scalar functions; as I mentioned they are known to perform poorly (especially multi-line table value functions). — Thom A, Jun 28 '22 at 14:52
As a separate side note, SQL isn't a programming language; creating lots of functions for mundane tasks often significantly hinders performance (such as your one to hide some simple `DATEADD` logic). You are often far better off putting the logic you want straight into the query. — Thom A, Jun 28 '22 at 14:56
Here's a little [Additional Reading](https://www.red-gate.com/simple-talk/databases/sql-server/learn/tsql-user-defined-functions-ten-questions-you-were-too-shy-to-ask/#fifth) from Redgate on the subject — paneerakbari, Jun 28 '22 at 14:58
Aside: `( @@DateFirst + DatePart( weekday, SampleDate ) - 1 ) % 7 + 1` will always return an integer from `1` to `7` with `1` corresponding to Sunday regardless of the setting of `DateFirst` or `Language`. — HABO, Jun 29 '22 at 13:24
How is my existing answer insufficient? Please clarify what additional information you need. — RBarryYoung, Jul 01 '22 at 16:07

score 4 · Answer 1 · answered Jun 28 '22 at 15:01

4

This is all explained in this SqlServer 2019 article: https://learn.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.

answered Jun 28 '22 at 15:01

RBarryYoung

55,398
14
96
137

For something as simple as this - the best way around it is to not use a function at all and just code it directly in the view or procedure or other functions. I have seen the same thing done to get midnight of the current date - and all that does is makes your code slower and harder to maintain. – Jeff Jun 28 '22 at 15:29
@Jeff Isn't the standard programming idiom "*if you see yourself doing the same thing many times, then make it a function with a good name*"? I'll believe that this is slower, but there's no way that it's harder to maintain or less clear. – J. Mini Jun 28 '22 at 16:12
@J.Mini Stop "believing" and assuming. To know you must test! But "monday of any given week" never changes so why compute it every time you need to reference it. Search "calendar table" - it solves so many, many problems. – SMor Jun 28 '22 at 18:13
@SMor We've already got one of those, but the call to `GETDATE()` can't be avoided in any task that cares about dates relative to today. – J. Mini Jun 28 '22 at 18:31
@J.Mini - you are working with T-SQL, not a programming language. There is no benefit to creating a function for simple date calculations like this. To make a function like this performant - you would need to convert it to an inline-table valued function and change all calls to that function to use CROSS APPLY. It therefore makes the code more cumbersome to manage and maintain than to simply code it directly. Further - the end goal here is a VIEW which means you are only coding it a single time. – Jeff Jun 29 '22 at 16:01
@Jeff "*There is no benefit*" - Surely you must at least agree that it reduces development time and clarifies intent? What if I use the same function in multiple independent places? – J. Mini Jun 29 '22 at 22:29
@J.Mini - scalar functions must be run against every row in the results. If certain conditions are met then a scalar function may be in-lined in SQL Server 2019 (and later). But that doesn't automatically guarantee better performance either - which generally leads to someone moving the code from the function into the calling set of code anyways. – Jeff Jun 30 '22 at 16:09
You are not saving anything by wrapping a simple expression into a function. The expression is written one-time in that view/procedure/function - even if you repeat the same pattern for multiple columns it is done just one time. You are not saving time or improving performance (in fact, you are causing performance issues) by creating functions like this. – Jeff Jun 30 '22 at 16:14
1

@Jeff "*The expression is written one-time in that view/procedure/function*" - And how do you know that? I get what you're trying to say, but I do in fact use this function in multiple places. Learning the hard way that it was a very bad idea has been painful. – J. Mini Jun 30 '22 at 21:38
Let's say you have 3 columns in that view where you are going to use that function - you either write that function 3 times in the code for the view or you write the expression 3 times in the code for the view. What have you saved here? A couple of keystrokes...maybe? At what cost? – Jeff Jul 02 '22 at 15:39
To repeat - for simple expressions there is no benefit to wrapping the expression into a function. That does not mean we don't use functions - and when there is complex logic involved an inline table-valued function can be the right solution, but for simple expressions it is actually more harmful than using the expression directly. – Jeff Jul 02 '22 at 15:41
1

@J.Mini Inline Table Valued Functions work very nicely for encapsulating complex logic. If you join them using `CROSS APPLY` you will find they are inlined efficiently – Charlieface Jul 07 '22 at 00:59

score 2 · Accepted Answer · answered Jul 02 '22 at 15:02

@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

score 0 · Answer 3 · answered Jul 05 '22 at 15:25

0

Anytime you use GetDate() as part of a row level rowset, your result is going to be dramatically slower the more rows you have. That is because the function is being called for every row. This is almost never needed since the you likely don't need precision of seconds for each separate row. Instead, prior to any query of data, declare a variable called @NowDate and fill it at the beginning with GetDate(). Then, everywhere you would have used GetDate() in the query can now be replaced with NowDate(). You will see a MAJOR performance gain.

answered Jul 05 '22 at 15:25

DrDoomPDX

282
3
12

This question is about views. How would you like to declare `@NowDate` in a view? – J. Mini Jul 05 '22 at 16:47
@J.Mini The question was about using GetDate() in a function. Read it again. You cannot use and populate a variable with getdate in a view. But you could make a stored procedure. – DrDoomPDX Jul 05 '22 at 18:56
Specifically `GETDATE()` is a so-called "runtime constant". It is called once per query, not once per row. "SQL Server has a concept called “runtime constants” where some expressions are pulled out of the tree and executed before rows are fetched. The result is cached and re-used in all places within the query." See https://learn.microsoft.com/en-gb/archive/blogs/conor_cunningham_msft/conor-vs-runtime-constant-functions – Vladimir Baranov Jul 06 '22 at 12:14
Odd. I struggle with what I am reading in that article as it is not consistent with all my queries. I have literally improved stored procedure performance 100+ fold by removing getdate throughout and replacing with a variable that i set with a single getdate action at the beginning of the procedure. – DrDoomPDX Jul 12 '22 at 23:14

Does using a function calling GETDATE() in a view consistently give dramatically worse performance than using GETDATE() directly?

3 Answers3