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.
Edit 3: More executions plans, this time all on the live server, with the index re-added, with and without where clauses.
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.