sql - 如何性能调整此查询？

Question

需要帮助调整查询性能。执行计划显示 SResourceHierarchy 和 grouprelationship 上的扫描计数太高 数以百万计的 table 。我在 sresource 和 matrix 目标上创建了覆盖索引，主要减少了时间。但我想知道是否有任何其他编写查询的方式可以将整体性能提高 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

Answer 1

它应该在您的查询中使用子查询。所以它应该是性能明智的差。它是针对每个主查询行运行的简单子(monad)查询。所以需要很多时间。如果你提高性能避免子查询。您使用连接而不是子查询。