mardi 21 juin 2016

Calling a Recursive CTE on every record

I have a store procedure query that returns all Electrical Panels in relation to their position to a given electrical circuit. To make the query as informative as possible I also return the loading (sum of circuits power requirement) on each panel and the quantity of circuits used.

The sum of loading of each panel has to take into account a parent child relationship between panels, as some panels supply other panels which also have load.

To achieve this parent child load calculation I use a recursive CTE which works well to find the load of 1 panel. However, when I want to use the first query, it calls the CTE calculation query on every row, resulting in a significant performance hit.

What is the best practice in order to speed up this query? I have tried table value functions which have helped but I feel that I require a completely different approach.

Main query

select p.Panel
,(abs(gcX.COOR-t.TX)+ abs(gcY.COOR-t.TY))/1000 as Length
,'TBD' as Load
,'TBD' as [Available Circuits]
,round(v.kVA/p.kVA * 100,0) as LOAD
--,cc.cc as [Available Circuits]
,p.[Status]
,p.PanelID from

(select poc.*,tc.X,tc.Y  from

tblPowerCircuits poc left join
SI_EquipCoordinates tc on poc.EntityCode = tc.Entity_Code and poc.Life = tc.Life where poc.POCID = @POCID) t join
 tblPanels p on  p.voltage = case when t.voltage = 230 then 400 when t.voltage = 120 then 208 else t.voltage end and t.utility = p.Utility and t.train = p.train and t.Filt = p.Filt and p.BP_DP = case when t.FLA < 61 then 1 else 2 end 

 left join 
SI_GridCoordinates gcX on p.x = gcX.GRID and p.Facility = gcX.FAB left join 
SI_GridCoordinates gcY on p.y = gcY.GRID and p.Facility = gcY.FAB

cross apply dbo.fnPH_GetPanelLoad (p.PanelID) v

--cross apply dbo.fnPH_GetPanelCct(p.PanelID) cc
 where 

p.VisibleToFilter = 1

 order by Length,panel

Load per panel query

SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fnPH_GetPanelLoad](
@PanelID int
)
returns table
as

return
( Select vpc.SourceID, round((Sum(poc.KVA * (CASE WHEN vpc.BP_DP = 1 THEN 0.8 * POWER(CAST(0.8 AS float), Depth) WHEN vpc.BP_DP = 2 THEN 0.64 * POWER(CAST(0.8 AS float), Depth) END))),0) as kVA from vwPanelClosure vpc join
PH_PanelCircuits pc on vpc.PanelID = pc.PanelID join
(Select * from PH_POCPanJoin where Active = 1) pj on pc.PanCctID = pj.PanCctID join
(SELECT        POCID, Circuit, L,F, KVA, Active
                               FROM            dbo.tblPowerCircuits) AS poc ON pj.POCID = poc.POCID LEFT OUTER JOIN                
                             (SELECT        Circuit, InstallDate,DemoDate
                               FROM            dbo.PH_POC_InstallDeinstallDate) AS s ON poc.Circuit = s.[Circuit] AND poc.L= s.L where (s.DemoDate > Dateadd(d,830,getdate()) or s.DemoDate is null) and vpc.sourceID = @PanelID
 group by

vpc.SourceID)

And here is the recursive query vwPanelClosure

WITH category_cte AS (SELECT        PanelID AS SourceID, PanelID, BP_DP, 0 AS depth
                                                  FROM            dbo.tblPanels
                                                  UNION ALL
                                                  SELECT        CTE.SourceID, C.PanelID, C.BP_DP, CTE.depth + 1 AS depth
                                                  FROM            dbo.tblPanels AS C INNER JOIN
                                                                           category_cte AS CTE ON C.SCID = CTE.PanelID)
    SELECT        SourceID, PanelID, BP_DP, depth
     FROM            category_cte AS category_cte_1

Should I be using something like a #Temp table to store the CTE values for repeat processing?

Aucun commentaire:

Enregistrer un commentaire