All geek questions in one place

Does the scalar function in the select statement repeatedly run the function several times, and how to get around this, if so

If I have a select statement with a scalar function used in various calculations, does this scalar function call several times? If so, there is a way to optimize this, so it only calls funciton once for each choice, since in my real query it will be called thousands of times, X 6 times per choice.

For instance:

SELECT [dbo].[fn_Days](@Account) + u.[DayRate], [dbo].[fn_Days](@Account) / u.[WorkDays] FROM [dbo].[tblUnit] u

All fn_days does, returns int days.

+6
source share
2 answers

Yes, the scalar is called several times, as you encoded it. One way to make it work is to wrap it in a subquery:

 SELECT t.[days] + t.[DayRate], t.[days] / t.[WorkDays] FROM ( SELECT [dbo].[fn_Days](@Account) as days, u.[DayRate], u.[WorkDays] FROM [dbo].[tblUnit] u) as t

This way fn_Days is only called once per line, not twice, or six times, as you mentioned.

Hope this helps.

+10
source

Functions are deterministic, which means that it will always return the same value for a given parameter. You use the variable as a parameter so that you can call the function once before executing the query and use the result in the query instead of calling the function.

 DECLARE @Days int SET @Days = [dbo].[fn_Days](@Account) SELECT @Days + u.[DayRate], @Days / u.[WorkDays] FROM [dbo].[tblUnit] u
+2
source

Source: https://habr.com/ru/post/912966/

More articles:


All Articles