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 ?
Aucun commentaire:
Enregistrer un commentaire