Optimizing sql server scalar-valued function

Question

Here is my question,

I have a view calling another view. And that second view has a scalar function which obviously runs for each row of the table. For only 322 rows, it takes around 30 seconds. When I take out the calculated field, it takes 1 second.

I appreciate if you guys give me an idea if I can optimize the function or if there is any other way to increase the performance?

Here is the function:

ALTER FUNCTION [dbo].[fnCabinetLoad] (
@site    nvarchar(15),
@cabrow  nvarchar(50),
@cabinet nvarchar(50))

RETURNS float
AS BEGIN
    -- Declare the return variable here
    DECLARE @ResultVar float

    -- Add the T-SQL statements to compute the return value here
    SELECT @ResultVar = SUM(d.Value)
    FROM 
    (
      SELECT dt.*, 
      ROW_NUMBER() 
          OVER (PARTITION BY dt.tagname ORDER BY  dt.timestamp DESC) 'RowNum'
      FROM vDataLog dt
     WHERE dt.Timestamp BETWEEN dateadd(minute,-15,getdate()) AND GetDate()
    ) d 
    INNER JOIN [SKY_EGX_CONFIG].[dbo].[vPanelSchedule] AS p
        ON p.rpp = left(d.TagName,3) + substring(d.TagName,5,5) 
             + substring(d.TagName,11,8)
       AND right(p.pole,2) = substring(d.TagName,23,2)
       AND p.site = @site
       AND p.EqpRowNumber  = @cabrow
       AND p.EqpCabinetName= @cabinet
    WHERE d.RowNum = 1
    AND Right(d.TagName, 6) = 'kW Avg'

RETURN @ResultVar

END

Ouch. You have a timebomb of performance issues here. Let's back up to the beginning. You have a view calling a view. This is a recipe for poor performance. Nested views should be avoided. Then let's move to your scalar function. You could change this into an inline table valued function which would help. Unfortunately you also have some nonSARGable predicates in your where clause of the function which is also going to kill performance. You need to rewrite the query so it isn't using nested views and drop the scalar function. — Sean Lange
– Sean Lange, Commented Feb 25, 2015 at 14:58
Check for parameter sniffing execute your function with OPTION (RECOMPILE) — Mihai
– Mihai, Commented Feb 25, 2015 at 14:59
Is this field indexed and does the function use that index? dt.Timestamp BETWEEN dateadd(minute,-15,getdate()) AND GetDate() I would assume this is what limits most of the data if you only fetch the last 15 minutes. — James Z
– James Z, Commented Feb 25, 2015 at 15:14
BETWEEN dateadd(minute,-15,getdate()) AND GetDate()..this is computed for each and every row even through it will be the same value always!! Oh, the performance! — SouravA
– SouravA, Commented Feb 25, 2015 at 15:26

Jeroen Mostert · Accepted Answer · 2015-02-25 15:00:10Z

1

Scalar-valued functions have atrocious performance. Your function looks like an excellent candidate for an inline table-valued function that you can CROSS APPLY:

CREATE FUNCTION [dbo].[fnCabinetLoad]
(
@site    nvarchar(15),
@cabrow  nvarchar(50),
@cabinet nvarchar(50)
)
RETURNS TABLE
AS RETURN
    SELECT SUM(d.Value) AS [TotalLoad]
    FROM 
    (
      SELECT dt.*, ROW_NUMBER() OVER (PARTITION BY dt.tagname ORDER BY  dt.timestamp DESC) 'RowNum'
      FROM vDataLog dt
     WHERE dt.Timestamp BETWEEN dateadd(minute,-15,getdate()) AND GetDate()) d INNER JOIN [SKY_EGX_CONFIG].[dbo].[vPanelSchedule] AS p
    ON p.rpp           = left(d.TagName,3) + substring(d.TagName,5,5) + substring(d.TagName,11,8)
   AND right(p.pole,2) = substring(d.TagName,23,2)
   AND p.site = @site
   AND p.EqpRowNumber  = @cabrow
   AND p.EqpCabinetName= @cabinet
 WHERE d.RowNum = 1
   AND Right(d.TagName, 6) = 'kW Avg'

In your view:

SELECT ..., cabinetLoad.TotalLoad
FROM ... CROSS APPLY dbo.fnCabinetLoad(.., .., ..) AS cabinetLoad

answered Feb 25, 2015 at 15:00

Jeroen Mostert

29k2 gold badges60 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

usr Over a year ago

The connect items to fix performance of scalar UDFs: connect.microsoft.com/SQLServer/feedback/details/524983/…, connect.microsoft.com/SQLServer/feedback/details/273443/…. Voting is easy. A Microsoft account is all that is needed.

usr Over a year ago

My observation is that they do not touch areas of the product after v2 is done. But a very long time later they sometimes revisit entire areas and solve them comprehensively. Example: AG's supersede mirroring comprehensively. Mirroring essentially wasn't touched for 7 years before AGs came along.; So they might eventually decide to take on this issue as part of a general overhaul of some parts of the engine. It is good to indicate customer interest for that rare occasion.

Tod Meinke · Accepted Answer · 2015-02-25 15:03:34Z

0

My understanding is the returned result set is 322 rows, but if the vDataLog table is significantly larger, I would run that subquery first and dump that result set into a table variable. Then, you can use that table variable instead of a nested query.

Otherwise, as it stands now, I think the joins are being done on all rows of the nested query and then you're stripping them off with the where clause to get the rows you want.

answered Feb 25, 2015 at 15:03

Tod Meinke

941 gold badge1 silver badge7 bronze badges

Comments

SouravA · Accepted Answer · 2015-02-25 15:07:25Z

You really don't need a function and get rid of nested view(very poor performant)! Encapsulate the entire logic in a stored proc to get the desired result, so that instead of computing everything row by row, it's computed as a set. Instead of view, use the source table to do the computation inside the stored proc.

Apart from that, you are using the functions RIGHT, LEFT AND SUBSTRING inside your code. Never have them in WHERE OR JOIN. Try to compute them before hand and dump them into a temp table so that they are computed once. Then index the temp tables on these columns.

Sorry for the theoretical answer, but right now code seems a mess. It needs to go through layers of changes to have decent performance.

José Margaça Lopes · Accepted Answer · 2015-02-27 12:25:26Z

Turn the function into a view.

Use it by restraining on the columns site, cabrow and cabinet and Timestamp. When doing that, try storing GetDate() and dateadd(minute,-15,getdate()) on a variable. I think not doing so can prevent you from taking advantage on any index on Timestamp.

SELECT SUM(d.Value) AS [TotalLoad],
       dt.Timestamp,
       p.site,
       p.EqpRowNumber AS cabrow,
       p.EqpCabinetName AS cabinet
FROM 
( SELECT dt.*, 
  ROW_NUMBER() OVER (PARTITION BY dt.tagname ORDER BY dt.timestamp DESC)'RowNum'
  FROM vDataLog dt) d 
INNER JOIN [SKY_EGX_CONFIG].[dbo].[vPanelSchedule] AS p
ON p.rpp = left(d.TagName,3) + substring(d.TagName,5,5) + substring(d.TagName,11,8)
AND right(p.pole,2) = substring(d.TagName,23,2)
WHERE d.RowNum = 1
AND d.TagName LIKE '%kW Avg'

Collectives™ on Stack Overflow

Optimizing sql server scalar-valued function

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related