当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - Bad performance with sql query

问题描述:

I have a snippet of SQL that compared the last two records and gives the datediff in seconds, however the way I have it is quite slow taking up to 20 seconds to execute depending how many controllerID's I needs to check.

What would be a more efficient way of do doing this?

select

T.controllerID,

datediff(ss, T.Max_dtReading, T1.Max_dtReading) As ElaspedTime

from

(select

controllerID,

max(dtReading) as Max_dtReading

from

ReaderData

where

CardID = 'FFFFFFF0' AND (controllerID in(2,13,28,30,37,40))

group by

controllerID) as T

outer apply

(select

max(T1.dtReading) as Max_dtReading

from

ReaderData as T1

where

T1.CardID = 'FFFFFFF0' AND (controllerID in(2,13,28,30,37,40))

and T1.controllerID = T.controllerID

and T1.dtReading < T.Max_dtReading) as T1

网友答案:

I might suggest conditional aggregation for this:

select controllerID,
       datediff(second, max(dtReading), min(dtReading)
               ) As ElaspedTime
from (select controllerID, dtReading,
             row_number() over (partition by controllerID order by dtReading desc) as seqnum
      from ReaderData
      where CardID = 'FFFFFFF0' AND 
            controllerID in (2, 13, 28, 30, 37, 40)
     ) r
where seqnum <= 2
group by controllerID
网友答案:

You can use ROW_NUMBER() in order to locate the records with the 2 highest dtReading values, then join these together to calculate the difference:

;WITH CTE AS (
    SLEECT controllerID, dtReading,
           ROW_NUMBER() OVER (PARTITION BY controllerID 
                              ORDER BY dtReading DESC) AS rn        
    FROM ReaderData
    WHERE CardID = 'FFFFFFF0' AND (controllerID IN (2,13,28,30,37,40))
)    
SELECT c1.controllerID, 
       DATEDIFF(ss, c1.dtReading, c2.dtReading) AS ElaspedTime
FROM CTE c1
INNER JOIN CTE c2 ON (c1.controllerID = c2.controllerID) 
                     AND c1.rn = 1 AND c2.rn = 2 
网友答案:
;WITH CTE AS
(select controllerID
        ,dtReading
        ,ROW_NUMBER() OVER (PARTITION BY controllerID ORDER BY dtReading DESC) rn
    from  ReaderData
    where CardID = 'FFFFFFF0' 
     AND  controllerID IN (2,13,28,30,37,40)
) 
select C1.controllerID
      ,datediff(ss, C1.dtReading, C2.dtReading) As ElaspedTime
from CTE C1 
LEFT JOIN CTE C2 ON C1.controllerID = C2.controllerID
                AND C1.rn = 1
                AND C1.rn < C2.rn 
分享给朋友:
您可能感兴趣的文章:
随机阅读: