帮忙看看这句怎么优化一下，现在执行速度奇慢

[z_yanjie]

select KDBH, jgclass,
CheckResult = (select top 1 CheckResult from CheckList where KDBH =ww.KDBH and jgclass=ww.jgclass order by CheckTime desc ),
CheckID= (select top 1 CheckID from CheckList where KDBH =ww.KDBH and jgclass=ww.jgclass order by CheckTime desc )
from CheckList ww where datediff(d,CheckTime,getdate())<7 group by KDBH,jgclass

现在执行需要30秒，希望大家给出优化意见

[kingtiy]

 where CheckTime<getdate()-7 --不要在列上加函数

[Mr_Nice]

[Quote=引用楼主 z_yanjie 的回复:]
select KDBH, jgclass,
CheckResult = (select top 1 CheckResult from CheckList where KDBH =ww.KDBH and jgclass=ww.jgclass order by CheckTime desc ),
CheckID= (select top 1 CheckID fro……
[/Quote]


Ctrl + L 看执行计划。

[AcHerat]

select a.KDBH,a.jgclass,b.CheckResult,b.CheckID
from CheckList a left join CheckList b on a.KDBH =b.KDBH and a.jgclass=b.jgclass
where datediff(dd,a.CheckTime,getdate())<7
	and not exists(select 1 from CheckList where KDBH = b.KDBH and jgclass = b.jgclass and CheckTime > b.CheckTime)

--试试看！

[Mr_Nice]

SELECT  ww.KDBH ,
        ww.jgclass ,
        CheckResult = T.CheckResult ,
        CheckID = T.CheckID
FROM    CheckList ww
        INNER JOIN ( SELECT TOP 1
                            CheckID ,
                            CheckResult ,
                            KDBH ,
                            jgclass
                     FROM   CheckList
                     ORDER BY CheckTime DESC
                   ) T ON T.KDBH = ww.KDBH
                          AND T.jgclass = ww.jgclass
WHERE   DATEDIFF(dd, CheckTime, GETDATE()) < 7
GROUP BY ww.KDBH ,
        ww.jgclass ,
        T.CheckResult ,
        T.CheckID

KDBH，jgclass 上加索引，然后Try

[快溜]

select distinct KDBH, jgclass into #tb from CheckList where datediff(d,CheckTime,getdate())<7

select a.KDBH,a.jgclass,
       CheckResult = (select top 1 CheckResult from CheckList where KDBH =ww.KDBH and jgclass=ww.jgclass 
                      order by CheckTime desc ),
  CheckID= (select top 1 CheckID from CheckList where KDBH =ww.KDBH and jgclass=ww.jgclass 
                  order by CheckTime desc )
from #tb a
drop table #tb

[SQL777]

select KDBH, jgclass,
  CheckResult,  CheckID  
from CheckList ww where CheckTime <getdate()-7
and checktime=(select max(checktime) from CheckList  where KDBH=ww.KDBH and  jgclass=ww.jgclass)

--根据你的数据结构建立适合索引