Sql-server – Hindrance in TSQL SVF Call

performancequery-performancesql servert-sql

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)

 SQL Server Execution Times:
   CPU time = 609 ms,  elapsed time = 629 ms.

But if I get the execution plan of running both queries at once, the relative cost of each query is 50%, even though the inline SVF is considerably slower.

  1. Why does SQL server run ths inline SVF query slower – both in CPU and elapsed time?
  2. By centralising some simple logic it appears I impede performance through code reuse. My problem is I need to call this SVF in numerous places without duplicating logic. If SQL server always impedes performance when using SVF why would I ever use them and is there a different solution?

Best Answer

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
Related Question