I've been asked to improve the performance of the following query. I can't amend any existing Indexes but I can create new ones.
The table structures can be found in this SQL Fiddle
The row sizes of the tables are ( from development - query plans and statistics etc will be provided from development)
dbo.Logs --6620130
dbo.LogMobiles --6620082
For reference, the table sizes in Production are
dbo.Logs --271856036
dbo.LogMobiles --269611865
I updated the statistics on the tables before running the query like so
UPDATE STATISTICS dbo.Logs ;
UPDATE STATISTICS dbo.LogMobiles ;
DBCC UPDATEUSAGE(db1, 'dbo.Logs');
DBCC UPDATEUSAGE(db1, 'dbo.LogMobiles');
The query is as follows
DECLARE
@rpStartDateTime DATETIME = '20180101'
,@rpEndDateTime DATETIME = '20180131' ;
DECLARE
@iStart INT = DATEDIFF(SECOND, '19700101', @rpStartDateTime)
,@iEnd INT = DATEDIFF(SECOND, '19700101', @rpEndDateTime);
SELECT
ROW_NUMBER()OVER(PARTITION BY L.NodeId ORDER BY L.NodeId, L.GPSTime ASC)
, V.NodeId
, V.UnitMaxSpeed
, M.DriverNodeId1
, M.StatusText
, M.Speed
, ISNULL(S.StreetMaxSpeed,200)
FROM
#xMobiles V
INNER JOIN
dbo.Logs L ON L.NodeId = V.NodeId
INNER JOIN
dbo.LogMobiles M ON M.LogId = L.LogId
--Must have Drivers identifiable
INNER JOIN
#xDrivers DN ON DN.DriverNodeId = M.DriverNodeId1
LEFT JOIN
dbo.Streets S ON S.StreetId = M.StreetId
WHERE
L.GPSTime >= @iStart AND L.GPSTime <= @iEnd
ORDER BY L.NodeId, L.GPSTime
OPTION (MAXDOP 1) ;
The LogId
from dbo.LogMobiles
is a FK
that references LogId
(PK) from dbo.Logs
.
Here is the Query Plan as is with no additional indexes
The Logical reads are as follows
From the Plan, the number of Actual Rows from dbo.Logs is 230768 which matches the final output. The number of Actual Rows from dbo.LogMobiles (from a Clustered Index Scan) is 5676246, this is 24x greater.
Time taken to execute ~ 4 seconds.
There should be a one2one relationship between dbo.Logs and dbo.LogMobiles (although other functionality do log rows to dbo.Logs) and as you can see the big difference between the number of logical reads and actual number of rows retrieved from these tables.
I then added the following Index on dbo.LogMobiles
CREATE NONCLUSTERED INDEX IX_LogId_SBR ON dbo.LogMobiles ( DriverNodeId1 ASC, LogId ASC ) INCLUDE ( Speed, StatusText, StreetId )
This is Plan with the new Index
This reduced the number of logical reads on dbo.LogMobiles
But increased the actual number of rows returned on the Index scan to 6620082 ( 28x greater).
Time taken to execute ~ 4 seconds - same.
What should I try next?