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

Question

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.

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.

Answer 1

Edit - The below is assuming that the number of rows that would be returned by the query (if the limit wasn't present) exceeds the limit. (as in ... it regularly would return 5000 rows, but the limit forces it to return 1000)

Any time you have a limit on the number of rows returned by a query, you should not expect the TIMING on that query to have any sort of relevance to performance.

For example, if you take a simple query as such:

SELECT * FROM table_with_1m_rows;

It will take a while to process because it has to fetch all the rows via a sequential scan..

If I adjust it to:

SELECT TOP 1000 * FROM table_with_1m_rows;

It will return relatively quickly because, while it still does a sequential scan, it can STOP after it gets past 1000 rows.

If I then adjust it to:

SELECT TOP 1000 * FROM table_with_1m_rows WHERE col1 > 100;

It will take LONGER than the previous query because, while it still does a sequential scan, it will most likely have to scan more than 1000 rows before it has 1000 rows to return with.

All of the above holds true whether the DB needs to use a sequential scan or an index scan..

If you truly want to troubleshoot performance of the query, you need to remove the TOP 1000 and then view your query plan and see where the performance hit is... (in this case, most likely an index that would be useful is missing)

Answer 2

I suspect that the DISTINCT is a performance barrier. I suggest you get rid of the duplicates (I would think that no device would ever be in 2 distinct locations at the same time, so a unique index on (imei, timestamp) would work just fine).

Such a change may need changes in your application, so until you do get rid of the duplicates, you could use this view:

CREATE VIEW [dbo].[LatestDeviceLocation]
AS 
WITH cte AS
    ( SELECT rn = ROW_NUMBER() OVER (PARTITION dloc.[Imei]
                                     ORDER BY dloc.[Timestamp] DESC),
             dloc.*
      FROM [dbo].[DeviceLocation] AS dloc
   ) 
SELECT [Imei], [Accuracy], [UserId], [Lat], [Lng], [Timestamp]
FROM   cte 
WHERE  rn = 1 ;

And an index on (Imei, [Timestamp] DESC) would be sufficient.