SQL Server Performance Forum – Threads Archive

Tuning a view…

The below given view runs for 520 seconds to returnd 50432 rows. is there an way this can be tuned???
In this query, same table is joined more than once. what is the use of doing this? SELECT ‘STORE’ AS SETID
,D.TREE_NODE AS DEPTID
,A.TREE_NODE AS DEPTID_FILTR
,J.DESCR AS MS_RPT_TREE_DEPT
,H.DESCR AS MS_RPT_TREE_PRNT
,I.DESCR AS MS_RPT_TREE_GRND
,D.TREE_LEVEL_NUM AS ‘CHILD_LEVEL’
,F.TREE_NODE AS ‘REPORTS_TO_DEPT’
,A.TREE_LEVEL_NUM AS ‘PARENT_LEVEL_NUM’
FROM PSTREENODE A — 65595 rows
,PSTREENODE D
,PSTREENODE F
,PS_DEPT_TBL H — 18132 rows
,PS_DEPT_TBL I
,PS_DEPT_TBL J
,PSTREENODE G
WHERE A.TREE_NAME = ‘DEPT_SECURITY’
AND A.SETID = ‘STORE’
AND D.TREE_NAME = A.TREE_NAME
AND D.SETID = A.SETID
AND D.EFFDT = A.EFFDT
AND D.TREE_NODE_NUM BETWEEN (SELECT B.TREE_NODE_NUM
FROM PSTREENODE B
WHERE B.TREE_NODE = A.TREE_NODE
AND B.TREE_NAME = A.TREE_NAME
AND B.EFFDT = A.EFFDT
AND B.SETID = A.SETID)
AND (SELECT C.TREE_NODE_NUM_END
FROM PSTREENODE C
WHERE C.TREE_NODE = A.TREE_NODE
AND C.TREE_NAME = A.TREE_NAME
AND C.EFFDT = A.EFFDT
AND C.SETID = A.SETID)
AND D.TREE_LEVEL_NUM BETWEEN 3 AND 6
AND A.EFFDT = (SELECT MAX(TD.EFFDT)
FROM PSTREEDEFN TD
WHERE TD.SETID = A.SETID
AND TD.TREE_NAME = A.TREE_NAME
AND TD.EFFDT <= CONVERT(CHAR(10), GETDATE(), 110))
AND F.TREE_NAME = D.TREE_NAME
AND F.SETID = D.SETID
AND F.EFFDT = D.EFFDT
AND F.TREE_NODE_NUM = D.PARENT_NODE_NUM
AND G.TREE_NAME = D.TREE_NAME
AND G.SETID = D.SETID
AND G.EFFDT = D.EFFDT
AND G.TREE_NODE_NUM = F.PARENT_NODE_NUM
AND F.TREE_NODE = H.DEPTID
AND H.SETID = A.SETID
AND H.EFFDT = (SELECT MAX(C_EFFDT.EFFDT)
FROM PS_DEPT_TBL C_EFFDT
WHERE C_EFFDT.SETID = H.SETID
AND C_EFFDT.DEPTID = H.DEPTID
AND C_EFFDT.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE()),1,11))
AND G.TREE_NODE = I.DEPTID
AND I.SETID = A.SETID
AND I.EFFDT = (SELECT MAX(C_EFFDT.EFFDT)
FROM PS_DEPT_TBL C_EFFDT
WHERE C_EFFDT.SETID = I.SETID
AND C_EFFDT.DEPTID = I.DEPTID
AND C_EFFDT.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE()),1,11))
AND D.TREE_NODE = J.DEPTID
AND J.SETID = A.SETID
AND J.EFFDT = (SELECT MAX(C_EFFDT.EFFDT)
FROM PS_DEPT_TBL C_EFFDT
WHERE C_EFFDT.SETID = J.SETID
AND C_EFFDT.DEPTID = J.DEPTID
AND C_EFFDT.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE()),1,11))
AND A.SETCNTRLVALUE = ‘ ‘
AND D.SETCNTRLVALUE = A.SETCNTRLVALUE
AND F.SETCNTRLVALUE = A.SETCNTRLVALUE
AND G.SETCNTRLVALUE = A.SETCNTRLVALUE
PS_DEPT_TBL
———–
PS0DEPT_TBLnonclustered located on PRIMARYDESCR, SETID, DEPTID, EFFDT
PS1DEPT_TBLnonclustered located on PRIMARYCOMPANY, SETID, DEPTID, EFFDT
PS2DEPT_TBLnonclustered located on PRIMARYSETID_LOCATION, SETID, DEPTID, EFFDT
PS3DEPT_TBLnonclustered located on PRIMARYLOCATION, SETID, DEPTID, EFFDT
PS4DEPT_TBLnonclustered located on PRIMARYBUDGET_DEPTID, SETID, DEPTID, EFFDT
PS_DEPT_TBLclustered, unique located on PRIMARYSETID, DEPTID, EFFDT PSTREENODE
———-
PSAPSTREENODEnonclustered located on PRIMARYSETID, TREE_NAME, EFFDT, TREE_BRANCH, TREE_NODE, TREE_NODE_NUM, TREE_NODE_NUM_END, TREE_NODE_TYPE
PSBPSTREENODEnonclustered located on PRIMARYSETID, TREE_NAME, TREE_BRANCH, TREE_NODE_NUM, TREE_NODE, TREE_NODE_NUM_END, TREE_LEVEL_NUM, TREE_NODE_TYPE
PSCPSTREENODEnonclustered located on PRIMARYTREE_NODE
PSDPSTREENODEnonclustered located on PRIMARYSETID, TREE_NAME, EFFDT, PARENT_NODE_NUM
PSFPSTREENODEnonclustered located on PRIMARYTREE_NAME, EFFDT
PS_PSTREENODEclustered, unique located on PRIMARYSETID, SETCNTRLVALUE, TREE_NAME, EFFDT, TREE_NODE_NUM, TREE_NODE, TREE_BRANCH
Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

---

One more information… When i read the execution plan, it showed 2 Index pools (costing 38%) on WHERE B.TREE_NODE = A.TREE_NODE
AND B.TREE_NAME = A.TREE_NAME
AND B.EFFDT = A.EFFDT
AND B.SETID = A.SETID and WHERE C.TREE_NODE = A.TREE_NODE
AND C.TREE_NAME = A.TREE_NAME
AND C.EFFDT = A.EFFDT
AND C.SETID = A.SETID is there any way i can avoid this index pool? Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

---

]]>