jeudi 26 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.





Aucun commentaire:

Enregistrer un commentaire