Need help in performance tuning the query. The execution plan shows the scan count is too high on SResourceHierarchy and grouprelationship tables around in millions. I created covering indexes on sresource and matrix goal that reduced the time majorly. But i want to know if there is any other way of writing query to improve the overall performance even by 1%.
select * from (SELECT sr.rsrcnum, sr.fllnm, isnull(mgr.fllnm,'') as mgrnm, d.actid, d.goaldesctxt , d.cmpltstscd, d.strtdt, d.enddt, dta, (select TOP 1 itmtxt from tm.Code where catcd = '12103' and itmcd = d.cmpltstscd and lngcd = 0 and delflg = 0) as Status from TM.matrixgoal d inner join TM.sresource sr on d.rsrcid=sr.rsrcid and sr.delflg=0 and sr.stscd<>1 and sr.rsrcid > 7 left join TM.sresource mgr on mgr.rsrcid=sr.mgr1id and mgr.delflg=0 and mgr.stscd<>1 inner join TM.resume1 r1 on r1.rsrcid=sr.rsrcid where d.stscd in (3,4) and d.typcd=5009 and d.activeflg=1 AND (sr.rsrcid IN (SELECT node.empid FROM tm.SResourceHierarchy node JOIN tm.SResourceHierarchy parent ON node.lft > parent.lft AND node.lft < parent.rgt AND node.mgrnum = 1 AND parent.mgrnum = 1 ) or sr.RsrcID IN (select r.rsrcid from tm.resume1 r join tm.grouprelationship g on (g.hierarchylvl0 = r.hierarchylvl0 or (g.hierarchylvl0 is null and r.hierarchylvl0 is null)) and (g.hierarchylvl1=r.HierarchyLvl1 or (g.hierarchylvl1 is null and r.HierarchyLvl1 is null)) and (g.hierarchylvl2=r.HierarchyLvl2 or (g.hierarchylvl2 is null and r.HierarchyLvl2 is null)) and (g.hierarchylvl3=r.HierarchyLvl3 or (g.hierarchylvl3 is null and r.HierarchyLvl3 is null)) and g.delflg=0 and g.AllowDenyCD='a')) ) temp ORDER BY fllnm, goaldesctxt
It should be use sub query in your query. So it should be performance wise poor. It is simple sub query run to per main query row. so it is taken much time. If you improve performance avoid sub query. You use join instead of sub query.
To me it look like one big ass single line statement, the only optimation I know if is to try to add a index to the varies tables you are using. that should speed up it's ability to find the data faster
Try to use
EXISTS instead of