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