SQL Server 2008

Sandeepiii
Sandeepiii used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
x-menIT super hero
Commented:
have you runned the workload against DTA to check for index and stats suggestions ?

Author

Commented:
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-menIT super hero

Commented:
you can limit the time DTA runs.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

x-menIT super hero
Commented:
another quick n' easy way, is to show the execution plan on SSMS. It will give you the "missing indexes" that would help the query.

Author

Commented:
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 .
x-menIT super hero
Commented:
Top Expert 2012
Commented:
You need the following indexes:
cpy.RHRH_OrgUnitHierarchy:
                  P_GRUPPE_ID

LLSO_SOR.sor.vwCV_PROD_S:
                  CL_CODE
                  SCM_CODE

Author

Commented:
Precise and to the point answer.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial