Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2008

Posted on 2012-12-26
8
Medium Priority
?
278 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 900 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 18

Assisted Solution

by:x-men
x-men earned 900 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 900 total points
ID: 38721197
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 600 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 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