Avatar of Sandeep rathore
Sandeep rathore
Flag for India asked on

SQL Server 2008

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
Microsoft SQL ServerMicrosoft Development

Avatar of undefined
Last Comment
Sandeep rathore

8/22/2022 - Mon
SOLUTION
x-men

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Sandeep rathore

ASKER
Thanks,i can see that there are no indexes in the views and SURR_ID looks to be unique and rest of the columns have lots of duplicate records.
Iam running DTA but its taking lots of time,to finish.could you help me more please.
x-men

you can limit the time DTA runs.
SOLUTION
x-men

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Sandeep rathore

ASKER
Thanks i ran estimated query execution plan and it has given few missing indiex that can improve around 12% , but how reliable would be estimated query execution plan,could you please tell me about extimated query execution plan .
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SOLUTION
x-men

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Anthony Perkins

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Sandeep rathore

ASKER
Precise and to the point answer.