Bad performance with sql query

Question

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

What version of SQL Server are you using?

Gordon Linoff
– Gordon Linoff

2015-04-07 13:34:14 +00:00
Commented Apr 7, 2015 at 13:34 — Gordon Linoff
– Gordon Linoff, Commented Apr 7, 2015 at 13:34

Gordon Linoff · Accepted Answer · 2015-04-07 13:39:55Z

3

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

answered Apr 7, 2015 at 13:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josh Kirkpatrick Over a year ago

Thanks alot this took it from 20+ seconds down to about 2 seconds! thanks

Giorgos Betsos · Accepted Answer · 2015-04-07 13:47:30Z

1

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

answered Apr 7, 2015 at 13:47

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Comments

M.Ali · Accepted Answer · 2015-04-07 13:58:20Z

1

;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

edited Apr 7, 2015 at 13:58

answered Apr 7, 2015 at 13:47

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Collectives™ on Stack Overflow

Bad performance with sql query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related