0

I am trying to execute below code which is taking too much time to execute.

SELECT  
    ROW_NUMBER() OVER (PARTITION BY a.DeviceID, a.SubscriberLoginID ORDER BY a.GPSCommandID) Row,
    a.DeviceId,
    SubscriberLoginID,
    CabLatitude, CabLongitude,
    LEAD(CabLatitude) OVER (PARTITION BY a.DeviceId, SubscriberLoginID ORDER BY CONVERT(DATETIME, GPSDateTime)) NextLat,
    LEAD(CabLongitude) OVER (PARTITION BY a.DeviceId, SubscriberLoginID ORDER BY CONVERT(DATETIME, GPSDateTime)) NextLong,
    MeterStatusId,
    LEAD(MeterStatusId) OVER (PARITITON BY a.DeviceId, SubscriberLoginID ORDER BY CONVERT(DATETIME,GPSDateTime)) NextStatus,
    a.GPSDateTime,
    LEAD(GPSDateTime) OVER (PARTITION BY a.DeviceId, SubscriberLoginID ORDER BY CONVERT(DATETIME, GPSDateTime)) NextGPSDateTime,
    a.LocationName,
    LEAD(LocationName) OVER (PARTITION BY a.DeviceId, SubscriberLoginID ORDER BY CONVERT(DATETIME, GPSDateTime)) NextLocationName
FROM    
    tblGPSDataDetail a WITH(NOLOCK) 
JOIN
    dbo.tblDeviceMaster tdm WITH(NOLOCK) ON a.DeviceID = tdm.DeviceID 
                                         AND CityID = 3
WHERE 
    CONVERT(VARCHAR, CONVERT(DATETIME, GPSDateTime), 101) = CONVERT(VARCHAR(20), GETDATE() - 1, 101)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Maddy11
  • 1
  • How do you know that it is the `lead()` that is causing the performance issue? Why would you be storing `GPSDateTime` as a string? That is probably the root cause of the performance issues. – Gordon Linoff Jul 16 '20 at 11:50
  • Hi @GordonLinoff. In my table GPSDateTime Column is varchar. Cause of that I was storing into varchar. Now I came to know my Mistake. I have made changes into my code. CONVERT(DATE,GPSDateTime) = CONVERT(DATE, GETDATE()-1) – Maddy11 Jul 17 '20 at 03:53
  • [WITH(NOLOCK) is not a turbo button](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) – Zohar Peled Jul 19 '20 at 13:58
  • Also: 1. [Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type), 2. [Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3) – Zohar Peled Jul 19 '20 at 14:03
  • Which column belong to which table? – Zohar Peled Jul 19 '20 at 14:05

0 Answers0