2

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.

5

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)

  • 1
    Right - I expect it will be ... but my point is you need to analyze the explain plan WITHOUT the TOP 1000 to figure out where the performance hit is happening.. Once that is figured out, adding the TOP 1000 back in is fine - but anytime you're limiting the rows returned like such, the query will -usually- run slower as more requirements on the data returned are added. – Joishi Bodio Feb 26 '15 at 23:31
  • 2
    Depending on how they're consuming the rows (e.g. in SSMS over a really slow network connection), there's going to be a tipping point somewhere, where not filtering and returning more rows will actually be slower - I have no idea where that is but a query that returns a billion rows to SSMS is not necessarily going to be faster than a filtered query that returns 1000 of those rows. There are no absolutes about whether a filtered query will take longer than an unfiltered query or vice-versa. – Aaron Bertrand Feb 27 '15 at 3:45
  • @JoishiBodio Okay, thanks, it was a valid thing to point out but it isn't "the answer". – BenCr Feb 27 '15 at 10:43
  • @AaronBertrand Both test and live dbs were running on the same cluster and I was using the same instance of SSMS to test both. – BenCr Feb 27 '15 at 10:44
  • 1
    @BenCr I know that, I wasn't suggesting that is your problem, I was merely questioning Joishi's assertion that not filtering and blindly returning more rows will always be faster. – Aaron Bertrand Feb 27 '15 at 12:19
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.