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

SOLUTION

x-men

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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 .

SOLUTION

x-men

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sandeep rathore

ASKER

Precise and to the point answer.