6

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?
10

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
  • Many thanks for the answer - this actually runs almost identical to the inline SQL logic in regards to performance! – user978139 Mar 30 '15 at 11:16
  • 1
    @user978139 Yes, that is to be expected. The inline TVF is inlined before a plan is generated so the TVF version is the same as doing select ProductID, (select case when [Type] = ..... end *Price*[Weight]) from temp_products. Not exactly the same as having the case statement directly in the column list but almost. – Mikael Eriksson Mar 30 '15 at 12:03
  • 1
    @user978139, the best use case for SVFs is when there is more value in code reuse than the performance cost. A 500ms performance hit for 100K rows might be a show-stopper in some workloads but acceptable in others. The response time degradation would be imperceptible on a system with 10K rows and few users. – Dan Guzman Mar 30 '15 at 12:19
2

SQL Server is atrocious when it comes to the performance of scalar functions as well as the reporting of its impact. (a very useful article with details as to why: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 1))

You are correct that the Query plan (actual) does not reflect the true performance of the two different approaches, this is because the query plan does not include inline scalar function calls in it's results.

A good* example of SQL Server doing this is if you reference a table in your scalar function, ie:

create function dbo.usvf_CalculateShipping2
(
    @Type tinyint, 
    @ProductID int
)
returns float
as
begin
    declare @result float

    select @result = 
            /*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 * t.PricePerKG * t.WeightInKG
    from ProductTable
    where ProductID = @ProductID

    return @result
end

Now if you call this usvf in your test query and show the query plan, you will not see the retrieval of records from ProductTable (even though it is actually hitting the table).

To see the true impact you will need to run a trace (open SQL Profiler and monitor your calls).. What you will see is that for every record you return you will see the same number of executions of the scalar function.

The same is true for Table value functions when used with CROSS APPLY calls.

As a rule we state:

  • do NOT use scalar functions inside queries

  • they are ONLY to be used to set local variables

`* good in the sense that this is an example of SQL Server showing bad behaviour

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.