3

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?

  • @Molenpad VehicleMonitoringLog is a Table with Clustured Index on iAssetId amd dtUtcDateTime – Dawood Awan Mar 26 '16 at 19:01
  • Why is degree of parallelism 1? – stacylaray Mar 27 '16 at 1:32
  • Have you run it through DTA? – stacylaray Mar 27 '16 at 1:39
  • @stacylaray, DOP is probably 1 because the query has an estimated cost of only 20. I would not recommend anyone to use DTA, but rather focus on tuning the query and/or indexes. – Daniel Hutmacher Mar 27 '16 at 20:57
  • @stacylaray, also, I think CLR datatypes, like the geography datatype will force MAXDOP=1, though I may be wrong. – Daniel Hutmacher Mar 27 '16 at 21:03
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.