Hindrance in TSQL SVF Call

I have a simply Products data table with 100k records (just generates some random data):

set nocount on

create table dbo.temp_Products
(
    [ProductID] int,
    [Type] tinyint,
    [Price] float,
    [Weight] float 
)

declare @rowcnt int = 0
while (@rowcnt <= 100000)
begin
    insert into dbo.temp_Products
    select @rowcnt, 1+rand()*4, 1+rand()*100, 1+rand()*10

    set @rowcnt = @rowcnt + 1
end

And a scalar value function which does a simple logical calculation:

create function dbo.usvf_CalculateShipping
(
    @PricePerKG float = 1,
    @Type tinyint,
    @WeightInKG float = 1
)
returns float
as
begin

    return  /*get the appropriate factor to apply*/
            case
                when @Type = 1 then 0.1
                when @Type = 2 then 0.2
                when @Type = 3 then 0.35
                when @Type = 4 then 0.43
            end * @PricePerKG * @WeightInKG

end

However, when I run a query to call the SVF the resulting performance is impaired relative to the query calling the logic inline. The executed SQL statements and results are as follows:

SQL:

select ProductID, case
                when [Type] = 1 then 0.1
                when [Type] = 2 then 0.2
                when [Type] = 3 then 0.35
                when [Type] = 4 then 0.43
            end *Price*[Weight] from temp_Products
where [Weight] between 2 and 8

select ProductID, dbo.usvf_CalculateShipping(Price, [Type], [Weight]) from temp_Products
where [Weight] between 2 and 8

Results:

```
Table 'temp_Products'. Scan count 1, logical reads 390, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 103 ms.

(59938 row(s) affected)
Table 'temp_Products'. Scan count 1, logical reads 390, physical reads 0, read- ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Why does SQL server run ths inline SVF query slower - both in CPU and
elapsed time?

Scalar valued functions are executed in a different context than the main query and setting that up for each call takes time.


By centralising some simple logic it appears I impede performance
through code reuse.

Yes, for scalar valued functions that is true.


If SQL server always impedes performance when using SVF why would I
ever use them

Good question, not sure if I can provide a use case where a scalar valued function would be the obvious answer.


is there a different solution?

Yes, you can use an inline table valued function instead.

create function dbo.usvf_CalculateShipping2
(
    @PricePerKG float = 1,
    @Type tinyint,
    @WeightInKG float = 1
)
returns table as return 
select case
         when @Type = 1 then 0.1
         when @Type = 2 then 0.2
         when @Type = 3 then 0.35
         when @Type = 4 then 0.43
       end * @PricePerKG * @WeightInKG as Shipping

You also have to change the way you call the function.

select ProductID, 
       (
       select Shipping 
       from dbo.usvf_CalculateShipping2(Price, [Type], [Weight])
       ) 
from temp_Products
where [Weight] between 2 and 8

StackExchange Database Administrators Q#96584, answer score: 10