奇怪的SQLserver執行

標籤：

 今天工作遇到，下面這段執行超慢，幾乎不出來，在舊伺服器上運行是可以的，一樣代碼，很快，在新虛擬伺服器上，超慢。

　　　　　select * from         (            select RowNum=row_number() over(order by AutoId desc),StartTime,cast(testtime/1000.00 as decimal(10,2)) as testtime,AutoId            from dbo.FTMaster M             WITH(NOLock) Where StartTime >=‘2014-01-01 00:00:00.000‘ and StartTime <‘2014-12-31 23:00:00.000‘ and ModelIc=‘XXX‘ and Result=0 and TestType=3                        and exists            (                    select AutoId from                 (                    select Min(BB.autoid) AutoID 
　　　　　　　　　　　　from                      (Select Max(StartTime) as starttime,SN From dbo.FTMaster WITH(NOLock) 
　　　　　　　　　　　　 Where StartTime >=‘2014-01-01 00:00:00.000‘ and StartTime <‘2014-12-31 23:00:00.000‘ and ModelIc=‘XXX‘ and TestType=3 group by Sn) as AA                      inner join 
　　　　　　　　　　　　(Select AutoID,SN,StartTime From dbo.FTMaster WITH(NOLock) 　　
　　　　　　　　　　　　 Where StartTime >=‘2014-01-01 00:00:00.000‘ and StartTime <‘2014-12-31 23:00:00.000‘ and ModelIc=‘XXX‘ and TestType=3 ) As BB 
　　　　　　　　　　　　 on AA.SN=BB.SN And AA.StartTime=BB.StartTime                     group by BB.SN                ) t where t.AutoId=M.AutoId             )                        )         as MasterTable         WHERE RowNum between (1 - 1) * 12430 + 1 and 1 * 12430

執行裡面的又很快， 為什麼呢。

　　　　　　　select RowNum=row_number() over(order by AutoId desc),StartTime,cast(testtime/1000.00 as decimal(10,2)) as testtime,AutoId            from dbo.FTMaster M             WITH(NOLock) Where StartTime >=‘2014-01-01 00:00:00.000‘ and StartTime <‘2014-12-31 23:00:00.000‘ and ModelIc=‘XXX‘ and Result=0 and TestType=3                        and exists            (                    select AutoId from                 (                    select Min(BB.autoid) AutoID 　　　　　　　　　　　　from                      (Select Max(StartTime) as starttime,SN From dbo.FTMaster WITH(NOLock) 　　　　　　　　　　　　 Where StartTime >=‘2014-01-01 00:00:00.000‘ and StartTime <‘2014-12-31 23:00:00.000‘ and ModelIc=‘XXX‘ and TestType=3 group by Sn) as AA                      inner join 　　　　　　　　　　　　(Select AutoID,SN,StartTime From dbo.FTMaster WITH(NOLock) 　　　　　　　　　　　　　　 Where StartTime >=‘2014-01-01 00:00:00.000‘ and StartTime <‘2014-12-31 23:00:00.000‘ and ModelIc=‘XXX‘ and TestType=3 ) As BB 　　　　　　　　　　　　 on AA.SN=BB.SN And AA.StartTime=BB.StartTime                     group by BB.SN                ) t where t.AutoId=M.AutoId             )    

 

奇怪的SQLserver執行