Can this SQL Query be improved

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

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 column iVehicleMonitoringId. 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. the p.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.

StackExchange Database Administrators Q#133433, answer score: 2