vendredi 27 février 2015

Why would a query work slower when a where clause is added?


I've got two databases and both have got the same view over the same table which has the same indexes.


The view selects the top location for a given IMEI from a locations table.



CREATE VIEW [dbo].[LatestDeviceLocation]
AS
SELECT DISTINCT t.Imei, t.Accuracy, t.UserId, t.Lat, t.Lng, t.Timestamp
FROM (SELECT Imei, MAX(Timestamp) AS latest
FROM dbo.DeviceLocation
GROUP BY Imei) AS m INNER JOIN
dbo.DeviceLocation AS t ON t.Imei = m.Imei AND t.Timestamp = m.latest
GO


I'm querying the view with a very simple select with what seems like a very simple where clause.



SELECT TOP 1000 [Imei]
,[Accuracy]
,[UserId]
,[Lat]
,[Lng]
,[Timestamp]
FROM [dbo].[LatestDeviceLocation]
Where [Timestamp] > '2015-02-19T00:00:00.000Z' AND [Timestamp] < '2015-02-26T23:59:59.999Z'


On my live server when I query my view I get data back in < 1 second. When I add a where clause Where [Timestamp] > '2015-02-19T00:00:00.000Z' AND [Timestamp] < '2015-02-26T23:59:59.999Z' that jumps up to approximately 1 minute.


On my test server which has 10x more data (350k+ locations shared by approximately same number if Imei numbers as the live site, 25) the query returns data in < 1 second with or without the where clause.


I've looked for locks and can't see any.


I've re-created the index incase it was corrupted and no difference.


I've completely removed the index, performance didn't change.


This is the index that I've used on both servers.



/****** Object: Index [GangHeatMapIndex] Script Date: 02/26/2015 22:38:38 ******/
CREATE NONCLUSTERED INDEX [GangHeatMapIndex] ON [dbo].[DeviceLocation]
(
[UserId] ASC,
[Timestamp] ASC,
[Imei] ASC
)
INCLUDE ( [DeviceLocationId],
[Accuracy],
[Lat],
[Lng]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


Edit: I've just realised that I wasn't looking in the right place for locks. It is taking out object locks when querying. I'm trying to work out how to write my view with "no lock" built into the view.


Edit 2: I've attached the execution plans, top on is with the index, bottom is without.


Execution plans


Edit 3: More executions plans, this time all on the live server, with the index re-added, with and without where clauses.


Exectution Plan - with index, without where.


Exectution Plan - with index, with where.


Edit 4:


I've changed the view to use a common table expression as follows and the performance is much better.



WITH cte
AS (SELECT Rank()
OVER (
partition BY dloc.[Imei]
ORDER BY dloc.[Timestamp], devicelocationid DESC) AS arank,
dloc.*
FROM [dbo].[DeviceLocation] AS dloc)
SELECT [Imei], [Accuracy], [UserId], [Lat], [Lng], [Timestamp]
FROM cte
WHERE arank = 1


Including the device DeviceLocationId in the order by prevented any duplicates occurring in the final result.





Aucun commentaire:

Enregistrer un commentaire