question

aminfo123 avatar image
aminfo123 asked

The Query is Taking too much time to execute

The Query is : SELECT * FROM ( SELECT TrackerResponse.EventName , TrackerResponse.ReceiveTime , ISNULL(TrackerResponse.InputStatus, 0) AS InputStatus , TrackerResponse.Latitude , TrackerResponse.Longitude , TrackerResponse.Temperature , TrackerResponse.Speed , TrackerResponse.TrackerID , TrackerResponse.OdoMeter , TrackerResponse.Direction , UserCar.CarNo , ROW_NUMBER() OVER ( PARTITION BY UserCar.CarNo ORDER BY ReceiveTime DESC ) AS row FROM TrackerResponse INNER JOIN UserCar ON ( UserCar.TrackerID = TrackerResponse.TrackerID ) WHERE ( TrackerResponse.EventName IS NOT NULL AND TrackerResponse.EventName <> '' ) AND TrackerResponse.TrackerID IN ( 34, 46, 56, 45, 55, 52, 58, 47, 53, 48, 50, 68, 70, 72, 73, 74, 75, 76, 78, 79, 80, 81, 82, 83, 84, 85, 97, 98, 87, 99, 100, 101, 102, 103, 104, 105, 106, 86, 88, 54, 108, 109, 110, 114, 111, 113, 112, 115, 117, 118 ) ) a WHERE row >= 0 AND row <= 20 ![alt text][1] [1]: /storage/temp/3138-usercar.png
sqlsql server
trackerresponse.png (14.3 KiB)
usercar.png (9.2 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What does the query plan look like? How healthy are the indexes? How slow is it? What speed are you expecting? Has it always been too slow, or has it only just started taking too long? How big are the tables? How good is the hardware / VM?
0 Likes 0 ·

1 Answer

·
Gazz avatar image
Gazz answered
One tiny change would be to change: TrackerResponse.EventName IS NOT NULL AND TrackerResponse.EventName '' to just: TrackerResponse.EventName '' as '' excludes NULL values - this might speed it up a tiny tiny bit
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.