Solved

SQL Server 2008

Posted on 2012-12-26
8
272 Views
Last Modified: 2012-12-30
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
0
Comment
Question by:Sandeepiii
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 18

Assisted Solution

by:x-men
x-men earned 300 total points
ID: 38721042
have you runned the workload against DTA to check for index and stats suggestions ?
0
 

Author Comment

by:Sandeepiii
ID: 38721045
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.
0
 
LVL 18

Expert Comment

by:x-men
ID: 38721049
you can limit the time DTA runs.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 18

Assisted Solution

by:x-men
x-men earned 300 total points
ID: 38721051
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.
0
 

Author Comment

by:Sandeepiii
ID: 38721081
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 .
0
 
LVL 18

Assisted Solution

by:x-men
x-men earned 300 total points
ID: 38721197
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 total points
ID: 38729414
You need the following indexes:
cpy.RHRH_OrgUnitHierarchy:
                  P_GRUPPE_ID

LLSO_SOR.sor.vwCV_PROD_S:
                  CL_CODE
                  SCM_CODE
0
 

Author Closing Comment

by:Sandeepiii
ID: 38731971
Precise and to the point answer.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question