5

Here are my tables :

CREATE TABLE [dbo].[Trackers] (
    [IdTracker]             INT           IDENTITY (1, 1) NOT NULL,
    [IMEI]                  NVARCHAR (16) NULL,
    [CreationDate]          DATETIME      NULL,
    [SuppressionDate]       DATETIME      NULL,
    [LastUpdateDate]        DATETIME      NULL,
    [BuyDate]               DATETIME      NOT NULL,
    [Status]                INT           NOT NULL,
    [LastTrackerUpdateDate] DATETIME      NULL,
    [IdLastTrackerPosition] INT           NULL,
    PRIMARY KEY CLUSTERED ([IdTracker] ASC)
);


CREATE TABLE [dbo].[TrackerPositions] (
    [IdTrackerPosition] INT               IDENTITY (1, 1) NOT NULL,
    [TrackerId]         INT               NOT NULL,
    [Position]          [sys].[geography] NULL,
    [Date]              DATETIME          NOT NULL,
    [Speed]             REAL              NULL,
    [NbSatellites]      TINYINT           NULL,
    [Direction]         REAL              NULL,
    [HDOP]              REAL              NULL,
    PRIMARY KEY CLUSTERED ([IdTrackerPosition] ASC),
    CONSTRAINT [TrackerTrackerPosition] FOREIGN KEY ([TrackerId]) REFERENCES [dbo].     [Trackers] ([IdTracker])
 );

Tracker position is used to store positions send by car trackers. So I have a lot of insert going on. And I often need to get the last position known for a tracker.

So here is a query generated by Entity Framework :

SELECT 
    [Limit1].[IdTrackerPosition] AS [IdTrackerPosition], 
    [Limit1].[TrackerId] AS [TrackerId], 
    [Limit1].[Position] AS [Position], 
    [Limit1].[Date] AS [Date], 
    [Limit1].[Speed] AS [Speed], 
    [Limit1].[NbSatellites] AS [NbSatellites], 
    [Limit1].[Direction] AS [Direction], 
    [Limit1].[HDOP] AS [HDOP]
    FROM   (SELECT DISTINCT 
        [Extent1].[TrackerId] AS [TrackerId]
        FROM [dbo].[TrackerPositions] AS [Extent1]
        WHERE ([Extent1].[Position] IS NOT NULL) AND ([Extent1].[TrackerId] IN (1, 48)) ) AS [Distinct1]
    OUTER APPLY  (SELECT TOP (1) [Project2].[IdTrackerPosition] AS [IdTrackerPosition], [Project2].[TrackerId] AS [TrackerId], [Project2].[Position] AS [Position], [Project2].[Date] AS [Date], [Project2].[Speed] AS [Speed], [Project2].[NbSatellites] AS [NbSatellites], [Project2].[Direction] AS [Direction], [Project2].[HDOP] AS [HDOP]
        FROM ( SELECT 
            [Extent2].[IdTrackerPosition] AS [IdTrackerPosition], 
            [Extent2].[TrackerId] AS [TrackerId], 
            [Extent2].[Position] AS [Position], 
            [Extent2].[Date] AS [Date], 
            [Extent2].[Speed] AS [Speed], 
            [Extent2].[NbSatellites] AS [NbSatellites], 
            [Extent2].[Direction] AS [Direction], 
            [Extent2].[HDOP] AS [HDOP]
            FROM [dbo].[TrackerPositions] AS [Extent2]
            WHERE ([Extent2].[Position] IS NOT NULL) AND ([Extent2].[TrackerId] IN (1, 48)) AND ([Distinct1].[TrackerId] = [Extent2].[TrackerId])
        )  AS [Project2]
        ORDER BY [Project2].[IdTrackerPosition] DESC ) AS [Limit1]

And here an index suggested by SQL Server Studio :

CREATE NONCLUSTERED INDEX [_dta_index_TrackerPositions_5_352108395__K2_3] ON [dbo].[TrackerPositions]
(
    [TrackerId] ASC
)
INCLUDE (   [Position]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

Performances are way better with the index, but most of my insert got locked...

Do you guys have any idea about a better index/select I could use ?

Edit : The index you guyrs gave me was fine until then. But I have some isues regarding performance now that my table is much bigger.

I tried this query, but the results are even worst :

    SELECT 
        [IdTrackerPosition] AS [IdTrackerPosition], 
        [TrackerId] AS [TrackerId], 
        [Position] AS [Position], 
        [Date] AS [Date], 
        [Speed] AS [Speed], 
        [NbSatellites] AS [NbSatellites], 
        [Direction] AS [Direction], 
        [HDOP] AS [HDOP]
FROM
(
    SELECT 
        IdTrackerPosition,
        [TrackerId] AS [TrackerId], 
        [Position] AS [Position], 
        [Date] AS [Date], 
        [Speed] AS [Speed], 
        [NbSatellites] AS [NbSatellites], 
        [Direction] AS [Direction], 
        [HDOP] AS [HDOP],
        row_number() OVER (PARTITION BY TrackerId ORDER BY IdTrackerPosition DESC) AS rank_row
    FROM (
        SELECT
            IdTrackerPosition,
            [TrackerId] AS [TrackerId], 
            [Position] AS [Position], 
            [Date] AS [Date], 
            [Speed] AS [Speed], 
            [NbSatellites] AS [NbSatellites], 
            [Direction] AS [Direction], 
            [HDOP] AS [HDOP]
            FROM TrackerPositions tp
            WHERE tp.Position IS NOT null 
                AND [TrackerId] IN (9, 2, 24, 41, 10, 7, 1, 17, 16, 20, 3, 40, 13, 14, 18, 11, 12, 5, 6, 4, 8, 23, 19, 15, 42, 22, 39, 21, 33, 37, 38, 34, 26, 28, 36, 25, 29, 43, 27, 30, 35, 31, 32)               
        ) AS A
)  AS Z
WHERE rank_row <= 1

Thanks !

  • I'd say your problem is EF and the horrible queries it produces. I'd prefer an index on (TrackerId, Position) though (even without modifying the query) for this TOP 1 .. ORDER BY Position DESC – ypercubeᵀᴹ Dec 20 '14 at 23:00
  • @PaulWhite thnx, I hadn't noticed that. And the order by is using IdTrackerPosition, not Position, so I was doubly sloppy in my comment. Your answer solves the issue. – ypercubeᵀᴹ Dec 22 '14 at 9:24
3

A better index would be (assuming ORDER BY IdTrackerPosition DESC is correct, and the query should not specify ORDER BY [Date] DESC instead):

CREATE UNIQUE INDEX i -- Choose a better name!
ON dbo.TrackerPositions 
    (TrackerId, IdTrackerPosition DESC) 
INCLUDE 
    (Position, [Date], Speed, NbSatellites, Direction, HDOP);

The execution plan should look something like this:

Execution plan

If the Position IS NOT NULL predicate is frequent, or reasonably selective, you could add a computed column to the base table, and include that in the index - assuming the query can be expressed to reference the computed column rather than saying Position IS NOT NULL explicitly:

-- Computed column
ALTER TABLE dbo.TrackerPositions
ADD PositionIsNull 
AS 
    CASE 
        WHEN Position IS NULL THEN CONVERT(bit, 1) 
        ELSE CONVERT(bit, 0) 
    END;

-- Modified index
CREATE UNIQUE INDEX i 
ON dbo.TrackerPositions 
    (TrackerId, PositionIsNull, IdTrackerPosition DESC) 
INCLUDE 
    (Position, [Date], Speed, NbSatellites, Direction, HDOP)
WITH (DROP_EXISTING = ON);

Modified query:

SELECT 
    [Limit1].[IdTrackerPosition] AS [IdTrackerPosition], 
    [Limit1].[TrackerId] AS [TrackerId], 
    [Limit1].[Position] AS [Position], 
    [Limit1].[Date] AS [Date], 
    [Limit1].[Speed] AS [Speed], 
    [Limit1].[NbSatellites] AS [NbSatellites], 
    [Limit1].[Direction] AS [Direction], 
    [Limit1].[HDOP] AS [HDOP]
    FROM   
    (
        SELECT DISTINCT 
            [Extent1].[TrackerId] AS [TrackerId]
            FROM [dbo].[TrackerPositions] AS [Extent1]
            WHERE 
                Extent1.PositionIsNull = CONVERT(bit, 0)
                AND ([Extent1].[TrackerId] IN (1, 48)) 
    ) AS [Distinct1]
    OUTER APPLY  
    (SELECT TOP (1) 
        [Project2].[IdTrackerPosition] AS [IdTrackerPosition], 
        [Project2].[TrackerId] AS [TrackerId], 
        [Project2].[Position] AS [Position], 
        [Project2].[Date] AS [Date], 
        [Project2].[Speed] AS [Speed], 
        [Project2].[NbSatellites] AS [NbSatellites], 
        [Project2].[Direction] AS [Direction], 
        [Project2].[HDOP] AS [HDOP]
        FROM 
        ( 
            SELECT 
                [Extent2].[IdTrackerPosition] AS [IdTrackerPosition], 
                [Extent2].[TrackerId] AS [TrackerId], 
                [Extent2].[Position] AS [Position], 
                [Extent2].[Date] AS [Date], 
                [Extent2].[Speed] AS [Speed], 
                [Extent2].[NbSatellites] AS [NbSatellites], 
                [Extent2].[Direction] AS [Direction], 
                [Extent2].[HDOP] AS [HDOP]
            FROM [dbo].[TrackerPositions] AS [Extent2]
            WHERE 
                Extent2.PositionIsNull = CONVERT(bit, 0)
                AND ([Extent2].[TrackerId] IN (1, 48)) 
                AND ([Distinct1].[TrackerId] = [Extent2].[TrackerId])
        )  AS [Project2]
        ORDER BY 
            [Project2].[IdTrackerPosition] DESC 
    ) AS [Limit1]

Execution plan with computed column:

Plan with computed column

  • 98% of the time Position IS NOT NULL. I'll give your solution a try and I will le tyou know ! Thanks ! – Guillaume Philipp Dec 21 '14 at 13:59
  • @GuillaumePhilipp Ok well the computed column might not be such a good idea in that case. The original index suggestion should work well though. – Paul White Dec 21 '14 at 14:14
4

Your index should be on (TrackerId ASC, [Date] DESC) INCLUDE (Position) so that it can easily locate the most recent one for each Tracker.

But I really don't like the query from EF.

Edit: ...because "most recent" should mean "latest datetime", not "latest identity value"

  • Most recent is latest identity because trackers are configured to work like that ! Thanks ! – Guillaume Philipp Dec 21 '14 at 13:57
  • So you could substitute IdTrackerPosition for [Date] – Rob Farley Dec 21 '14 at 14:05

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.