SQL Server 2012 – How to Improve SQL Query Performance

performancequery-performancesql serversql-server-2012

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 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.