Related to one of my previous questions: How to select a subset from a query result
I now have this Query:
CREATE TABLE #JourneyLogs
(
JourneyStartId INT,
JourneyEndId INT,
AssetId INT,
[Event] INT,
JourneyStart DATETIME,
JourneyEnd DATETIME,
GeofenceId INT,
JourneyDistanceKM FLOAT,
JourneyTime INT
)
INSERT INTO #JourneyLogs
SELECT
JourneyStartId
, JourneyEndId
, AssetId
, [Event]
, JourneyStart
, JourneyEnd
, GeofenceId
, JourneyDistance = ROUND((SUM(DistanceCoveredK) / 1000), 3, 1)
, JourneyTime
FROM (
SELECT
JourneyStartId = ignOn.iVehicleMonitoringId
,JourneyEndId = ignOff.iVehicleMonitoringId
,AssetId = ignOn.iAssetId
,JourneyStart = DATEADD(hour, @fGmtOffSet, ignOn.dtUTCDateTime)
,JourneyEnd = DATEADD(hour, @fGmtOffSet, ignOff.dtUTCDateTime)
,[Event] = ignOff.eEventCode
,DistanceCoveredK = p.sptGeoLocaitonPoint.STDistance(
LEAD(p.sptGeoLocaitonPoint) OVER(PARTITION BY ignOn.iAssetId, ignOn.dtUTCDateTime ORDER BY ignOff.dtUTCDateTime))
,GeofenceId = ignOn.iGeofenceId
,JourneyTime = DATEDIFF(SECOND, ignOn.dtUTCDateTime, ignOff.dtUTCDateTime)
FROM VehicleMonitoringLog ignOn
CROSS APPLY (
SELECT top(1) iVehicleMonitoringId, eEventCode, dtUTCDateTime, sptGeoLocaitonPoint
FROM VehicleMonitoringLog WHERE
iAssetId = ignOn.iAssetId AND dtUTCDateTime > ignOn.dtUTCDateTime AND eEventCode = 2
ORDER by dtUTCDateTime
) ignOff
INNER JOIN VehicleMonitoringLog p ON p.iAssetId = ignOn.iAssetId AND p.dtUTCDateTime >= ignOn.dtUTCDateTime AND p.dtUTCDateTime <= ignOff.dtUTCDateTime
WHERE
ignOn.dtUTCDateTime > @utcStartDate AND ignOn.dtUTCDateTime < @utcEndDate
AND ignOn.iAssetId in (Select iAssetId FROM #tblAssets)
AND ignOn.eEventCode = 1
) g
GROUP BY AssetId, [Event], GeofenceId, JourneyStart, JourneyEnd, JourneyTime, JourneyStartId, JourneyEndId
This takes 19 seconds and returns 4830 rows, with STATISTICS IO:
Table '#JourneyLogs________________________________________________________________________________________________________000000000148'. Scan count 0, logical reads 4864, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 146540, logical reads 857406, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VehicleMonitoringLog'. Scan count 9719, logical reads 64578, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#tblAssets__________________________________________________________________________________________________________000000000147'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Warning: Null value is eliminated by an aggregate or other SET operation.
(4830 row(s) affected)
Execution Plan: Here
Is there a way to improve this query?
Best Answer
Off the top of my head, these are the improvements I would try:
Add a clustered (unique if possible) index to the temp table,
#tblAssets (iAssetId)
. This will eliminate the Sort operator, and may potentially improve the type of join operator.On the index
IX_VehicleMonitoringLog_iAssetId_eEventCode_NonClustered
, include the columniVehicleMonitoringId
. This will eliminate the KeyLookup operator, which is potentially very expensive.Not so much an optimization, but rather for correctness: Shouldn't this...
PARTITION BY ignOn.iAssetId, ignOn.dtUTCDateTime ORDER BY ignOff.dtUTCDateTime
... rather be...
PARTITION BY ignOn.iAssetId, ignOn.dtUTCDateTime ORDER BY p.dtUTCDateTime
?I'm thinking that for all the records of any given partition, the
ignOff.dtUTCDateTime
should be the same, and you probably want to loop through each "segment", i.e. thep.dtUTCDateTime
, right?You may already get the correct results by accident with the current query because the records may be correctly ordered from the Index Seek on
p
, but I would probably want to make sure by specifying the correct ordering. As a bonus, this might improve performance.