Hi Experts,
Could you please help mw with the query,its taking long to execute.
If you could advise on where i can tune my query ?Thanks.
SELECT EFF_DT,LoadStatusId,InsertType,SURR_ID,SUM_NAVN,REGNR_TYPE,P_GRUPPE_ID,C_GRUPPE_ID,ORG_KSVAKD,HIER_CODE,TOP_F,LWS_F,ANC_LEVEL,DESC_LEVEL
FROM LLSO_cpy.cpy.vwRHRH_OrgUnitHierarchy_PROD where LoadStatusId=76829178 and C_GRUPPE_ID <> ''
ORDER BY HIER_CODE,ANC_LEVEL,P_GRUPPE_ID,C_GRUPPE_ID
LLSO_cpy.cpy.vwRHRH_OrgUnitHierarchy_PROD--This is view on our DB
-------------------------------------------------
Create statement is below..We are using the Recursive CTE.
CREATE VIEW [cpy].[vwRHRH_OrgUnitHierarchy_PROD]
AS
WITH HierarchyTopToBottom (EFF_DT, LoadStatusId, InsertType, SURR_ID, SUM_NAVN, REGNR_TYPE,
P_GRUPPE_ID, C_GRUPPE_ID, ORG_KSVAKD, HIER_CODE, TOP_F, LWS_F, ANC_LEVEL, DESC_LEVEL)
AS
(
-- Anchor member definition, find the parent nodes.
SELECT DISTINCT ou_hier.EFF_DT,
ou_hier.LoadStatusId,
ou_hier.InsertType,
ou_hier.SURR_ID,
ou_hier.SUM_NAVN,
ou_hier.REGNR_TYPE,
ou_hier.P_GRUPPE_ID,
ou_hier.C_GRUPPE_ID,
ou_hier.ORG_KSVAKD,
cv_s.CL_CODE AS HIER_CODE,
'1' AS TOP_F,
---- Iterate over entire CPY hierarchy to see if current node is a leaf.
LWS_F = CASE
WHEN EXISTS(SELECT ou_hier2.P_GRUPPE_ID
FROM cpy.RHRH_OrgUnitHierarchy_PROD ou_hier2
WHERE ou_hier2.P_GRUPPE_ID = ou_hier.C_GRUPPE_ID) THEN '0'
ELSE '1'
END,
1 AS ANC_LEVEL,
2 AS DESC_LEVEL
FROM cpy.RHRH_OrgUnitHierarchy_PROD ou_hier
INNER JOIN LLSO_SOR.sor.vwCV_PROD_S AS cv_s ON ou_hier.P_GRUPPE_ID = cv_s.CL_CODE
WHERE cv_s.SCM_CODE='IP_NVG_GFS'
UNION ALL
-- Recursive member definition, find the child nodes for each parent nodes
SELECT ou_hier.EFF_DT,
ou_hier.LoadStatusId,
ou_hier.InsertType,
ou_hier.SURR_ID,
ou_hier.SUM_NAVN,
ou_hier.REGNR_TYPE,
ou_hier.P_GRUPPE_ID,
ou_hier.C_GRUPPE_ID,
ou_hier.ORG_KSVAKD,
hier.HIER_CODE AS HIER_CODE,
'0' AS TOP_F,
-- Iterate over entire CPY hierarchy to see if current node is a leaf.
LWS_F = CASE
WHEN EXISTS(SELECT ou_hier2.P_GRUPPE_ID
FROM cpy.RHRH_OrgUnitHierarchy_PROD ou_hier2
WHERE ou_hier2.P_GRUPPE_ID = ou_hier.C_GRUPPE_ID) THEN '0'
ELSE '1'
END,
ANC_LEVEL + 1,
DESC_LEVEL + 1
FROM cpy.RHRH_OrgUnitHierarchy_PROD ou_hier
INNER JOIN HierarchyTopToBottom AS hier on ou_hier.P_GRUPPE_ID = hier.C_GRUPPE_ID
)
-- Get result set by invoking the recursive CTE.
SELECT EFF_DT,
LoadStatusId,
InsertType,
SURR_ID,
SUM_NAVN,
REGNR_TYPE,
P_GRUPPE_ID,
C_GRUPPE_ID,
ORG_KSVAKD,
CAST(HIER_CODE AS NVARCHAR(10)) AS HIER_CODE,
CAST(TOP_F AS NCHAR(1)) AS TOP_F,
CAST(LWS_F AS NCHAR(1)) AS LWS_F,
CAST(ANC_LEVEL AS SMALLINT) AS ANC_LEVEL,
CAST(DESC_LEVEL AS SMALLINT) AS DESC_LEVEL
FROM HierarchyTopToBottom
Iam running DTA but its taking lots of time,to finish.could you help me more please.