Query Tuning… | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query Tuning…

Guess i have posted this already. but this is altogther a different question. so i opened a new one… The below query runs for 8:30 mins. is there any way split the query, replace subqueries with temp tables/table variables) i can tune this query. This was originally a view. now i have the liberty to change this to a function/SP (in case if i need variables and temp tables). Can any one tune this in any way??? 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’
FROMPSTREENODE A
JOINPSTREENODE D
ON D.SETCNTRLVALUE = ‘ ‘
AND D.TREE_NAME = ‘DEPT_SECURITY’
AND D.SETID = ‘STORE’
AND D.EFFDT = A.EFFDT
JOINPSTREENODE F
ON F.SETCNTRLVALUE = ‘ ‘
AND F.TREE_NAME = ‘DEPT_SECURITY’
AND F.SETID = ‘STORE’
AND F.EFFDT = D.EFFDT
AND F.TREE_NODE_NUM = D.PARENT_NODE_NUM
JOINPSTREENODE G
ON G.SETCNTRLVALUE = ‘ ‘
AND G.TREE_NAME = ‘DEPT_SECURITY’
AND G.SETID = ‘STORE’
AND G.EFFDT = F.EFFDT
AND G.TREE_NODE_NUM = F.PARENT_NODE_NUM
JOINPS_DEPT_TBL H
ON H.DEPTID = F.TREE_NODE
AND H.SETID = ‘STORE’
JOINPS_DEPT_TBL I
ON I.SETID = ‘STORE’
AND I.DEPTID = G.TREE_NODE
JOINPS_DEPT_TBL J
ON J.DEPTID = D.TREE_NODE
AND J.SETID = ‘STORE’
WHEREA.TREE_NAME = ‘DEPT_SECURITY’
AND A.SETID = ‘STORE’
AND A.SETCNTRLVALUE = ‘ ‘
AND D.TREE_LEVEL_NUM BETWEEN 3 AND 6
AND D.TREE_NODE_NUM BETWEEN(SELECTB.TREE_NODE_NUM
FROMPSTREENODE B
WHEREB.TREE_NODE = A.TREE_NODE
AND B.TREE_NAME = ‘DEPT_SECURITY’
AND B.EFFDT = A.EFFDT
AND B.SETID = ‘STORE’
)
AND (SELECTC.TREE_NODE_NUM_END
FROMPSTREENODE C
WHEREC.TREE_NODE = A.TREE_NODE
AND C.TREE_NAME = ‘DEPT_SECURITY’
AND C.EFFDT = A.EFFDT
AND C.SETID = ‘STORE’
)
AND A.EFFDT = (SELECTMAX(TD.EFFDT)
FROMPSTREEDEFN TD
WHERETD.SETID = A.SETID
AND TD.TREE_NAME = A.TREE_NAME
AND TD.EFFDT <= CONVERT(CHAR(10), GETDATE(), 110)
)
AND H.EFFDT = (SELECTMAX(C_EFFDT.EFFDT)
FROMPS_DEPT_TBL C_EFFDT
WHEREC_EFFDT.SETID = H.SETID
AND C_EFFDT.DEPTID = H.DEPTID
AND C_EFFDT.EFFDT <= SUBSTRING(CONVERT(CHAR, GETDATE()), 1, 11)
)
AND I.EFFDT = (SELECTMAX(C_EFFDT.EFFDT)
FROMPS_DEPT_TBL C_EFFDT
WHEREC_EFFDT.SETID = I.SETID
AND C_EFFDT.DEPTID = I.DEPTID
AND C_EFFDT.EFFDT <= SUBSTRING(CONVERT(CHAR, GETDATE()), 1, 11)
)
AND J.EFFDT = (SELECTMAX(C_EFFDT.EFFDT)
FROMPS_DEPT_TBL C_EFFDT
WHEREC_EFFDT.SETID = J.SETID
AND C_EFFDT.DEPTID = J.DEPTID
AND C_EFFDT.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE()), 1, 11)
) Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
Have you done proper indexing.
Chaging this to a SP also may increase your performance. but forst check the indexing —————————————-
Hmm… that reply was damn fast. please see the index details and let me know this indexing is ok PS_DEPT_TBL
———–
PS0DEPT_TBL nonclustered located on PRIMARY DESCR, SETID, DEPTID, EFFDT
PS1DEPT_TBL nonclustered located on PRIMARY COMPANY, SETID, DEPTID, EFFDT
PS2DEPT_TBL nonclustered located on PRIMARY SETID_LOCATION, SETID, DEPTID, EFFDT
PS3DEPT_TBL nonclustered located on PRIMARY LOCATION, SETID, DEPTID, EFFDT
PS4DEPT_TBL nonclustered located on PRIMARY BUDGET_DEPTID, SETID, DEPTID, EFFDT
PS_DEPT_TBL clustered, unique located on PRIMARY SETID, DEPTID, EFFDT PSTREENODE
———-
PSAPSTREENODE nonclustered located on PRIMARY SETID, TREE_NAME, EFFDT, TREE_BRANCH, TREE_NODE, TREE_NODE_NUM, TREE_NODE_NUM_END, TREE_NODE_TYPE
PSBPSTREENODE nonclustered located on PRIMARY SETID, TREE_NAME, TREE_BRANCH, TREE_NODE_NUM, TREE_NODE, TREE_NODE_NUM_END, TREE_LEVEL_NUM, TREE_NODE_TYPE
PSCPSTREENODE nonclustered located on PRIMARY TREE_NODE
PSDPSTREENODE nonclustered located on PRIMARY SETID, TREE_NAME, EFFDT, PARENT_NODE_NUM
PSFPSTREENODE nonclustered located on PRIMARY TREE_NAME, EFFDT
PS_PSTREENODE clustered, unique located on PRIMARY SETID, SETCNTRLVALUE, TREE_NAME, EFFDT, TREE_NODE_NUM, TREE_NODE, TREE_BRANCH 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…"
As long as I know, composite indexes aren’t the solution. The best practice is to use one index / column. This way you will be sure that these indexes will be used. Try creating single column indexes for all your columns used in join, where and order.
(My SQL knowledges are at a medium level so I could be wrong. Anybody, correct me if needed) SQL LOVER
Composite indexes might be a good idea to cover the query. However, they come at a cost and shouldn’t be blindly implemented. —
Frank Kalis
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Is there anything i can do to this query other than indexing? Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
I tried changing this query to a function and removed the subquery
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)
) ,placed it outside the query, assigned the output of this subquery to a variable and passed the variable inside the main query. To my surprise, the query is now running in less than 3 minutes. Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
Give SQL Optimizer for Visual Studio a Try. It will rewrite your SQL and find the best performing method to run the SQL. You can download a free trial at extensibles.com The Relentless One
No Bug says alive http://www.extensibles.com
]]>