CODEWITHSUNDEEP
sql-serverfunction
ssahing1
ssahing1

Reputation: 1

Optimizing sql server scalar-valued function

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

Upvotes: 0

Views: 697

Answers (4)

José Margaça Lopes
José Margaça Lopes

Reputation: 149

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'

Upvotes: 0

SouravA
SouravA

Reputation: 5243

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.

Upvotes: 0

Tod Meinke
Tod Meinke

Reputation: 114

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.

Upvotes: 0

Jeroen Mostert
Jeroen Mostert

Reputation: 28769

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

Upvotes: 1

Related Questions