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:
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
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.
- Why does SQL server run ths inline SVF query slower - both in CPU and elapsed time?
- 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?