7

I have been tasked to improve the performance of a SQL Server 2008 R2 query (within a stored procedure) shown in the query execution plan link below.

It currently executes in around 7 seconds and needs to complete in 1-2 seconds if possible. Each execution of the slow query is always around 7 seconds. The results of the query trickle through. The tables being queried are large but not billions of rows.

Row counts

Row Counts

Depending on the parameters being passed the results can either be a few hundred rows (which executes sub second) to > 300K rows (this is slow).

I have included the slow query execution plan and indices that are being utilised by the optimiser. This is the plan for the quicker execution and STATISTICS IO, TIME:

AsIs StatsIO_Time

There are two queries being executed. The first one is not the issue. It's the second one that I need assistance with. Although needed, removing the non SARGable predicate (AND FT.TripDistance < ( CONVERT(NUMERIC(10,0),FT.TripTime) * 83.33 )) makes slight difference to the speed

remove non SARG

Removing the function [FN_GetLocalTime_FromUTC_BasedOnTZId] makes slight difference to the speed, here's the STATISTICS IO TIME removing both non SARG where clause and function: Here's the execution plan

remove non SARG and Func

Here's the output for the unchanged query except for adding a LOOP JOIN hint. This is slower.

LoopJoin

I have noticed that in the slow unchanged query plan, the actual number of rows (300330) for the Index Seek on the FACT_trip_Statuses table is close to the final output (299887). However, the actual number of rows (4.87m) on the index seeks on the xFactTrip_Annex, FACT_Trip, and FACT_Trip_Attributes is way out. How do I fix this? Adding OPTION RECOMPILE make little difference

I tried adding trace flag 4199 DBCC TRACEON (4199, -1); and retrying with and without JOIN hints, didn't help.

HASH join hint and TF 4199

HASH join hint and TF4199

LOOP join hint and TF 4199

LOOP join hint and TF4199

No join hints and TF 4199

No JOIN hints and TF4199

Indices

CREATE NONCLUSTERED INDEX IDX_FACT_Trip_StartDateUTC_VehicleKey_Includes ON dbo.FACT_Trip
(
    StartDateUTC ASC,
    VehicleKey ASC,
    EndDateUTC ASC
)
INCLUDE (   DriverKey,
    DrivingTime,
    TripDistance,
    TripTime)

CREATE NONCLUSTERED INDEX IX_xFactTrip_Annex_StartDateUTC_MonthlyProcessing ON dbo.xFactTrip_Annex
(
    StartDateUTC ASC
)
INCLUDE (   VehicleKey,
            Spd20Count, Spd20Distance,
            Spd30Count, Spd30Distance,
            Spd40Count, Spd40Distance,
            Spd50Count, Spd50Distance,
            Spd60Count, Spd60Distance,
            Spd70Count, Spd70Distance,
            SpdCat1,SpdCat2,SpdCat3,
            TotalIdling,
            PTOTime,
            TripFuel) 

CREATE NONCLUSTERED INDEX IX_FACT_Trip_Attributes_StartDateUTC_VehicleKey_Includes ON dbo.FACT_Trip_Attributes
(
    StartDateUTC ASC,
    VehicleKey ASC,
    EndDateUTC ASC
)
INCLUDE (   Attribute0Distance,
    Attribute1Distance,
    Attribute2Distance,
    Attribute3Distance,
    Attribute4Distance,
    Attribute5Distance,
    Attribute6Distance,
    Attribute7Distance,
    Attribute8Distance,
    Attribute9Distance,
    Attribute10Distance)

CREATE NONCLUSTERED INDEX IDX_FACT_Trip_Statuses_StartDateUTC_VehicleKey_Includes ON dbo.FACT_Trip_Statuses
(
    VehicleKey ASC,
    StartDateUTC ASC,
    EndDateUTC ASC
)
INCLUDE (   HarshAccelerationCount,
    HarshBrakeCount,
    HarshBumpCount,
    HarshCorneringCount,
    ExcessIdleDuration,
    ExcessIdleCount,
    OverspeedCount)

CREATE NONCLUSTERED INDEX IX_StartDateUTC_VehicleKey_IsBP ON dbo.FACT_TripComments
(
    StartDateUTC ASC
)
INCLUDE (   VehicleKey, IsBusinessPrivate) 
  • All of those unions. could you not use WHERE ... OR... OR instead of hitting those tables 4 separate times? Also, any way of getting rid of that function in the cross join? Also, do you need that MAXDOP hint to stop it going parallel? – Rich Benner Oct 12 '17 at 14:05
  • 1
    If I'm reading the plan right, it looks like the FN_GetLocalTime_FromUTC_BasedOnTZId function is where the bulk of the time goes. It also looks like the values from that function are only used in the SELECT list. Could you post that function? Also: instead of executing in the function, could you perform that date transformation directly in the SELECT list itself (the posting of the function may answer this question)? – RDFozz Oct 12 '17 at 17:15
  • Looks like a Leica database. We had an identical problem with that exactly named UDF. We used another method to convert to local time and got better performance. – Nick.McDermaid Dec 14 '17 at 14:11
  • @Nick.McDermaid this is not a Leica database. The name of the function being the same is just a coincidence – Mazhar Dec 18 '17 at 13:52
  • Sorry my mistake. Is the function using procedural code to apply a timezone offset to a UTC time? Anyway if you've removed it with little difference it probably doesn't matter – Nick.McDermaid Dec 18 '17 at 13:56
1

Probably a clustered index over the #xMobiles temp table can help you in the last Join

CREATE CLUSTERED INDEX IDX_xMobiles_VehicleKey ON #xMobiles
(
    VehicleKey ASC,
) 

The best option is to create the index after populating the table.

0

Now, this query still has faults and I can't test it against your data, but would this produce the same results and run a little faster?

WITH cteBusGrps
  AS(
   SELECT
    LTRIM(RTRIM(CAST(A.Value AS INT))) 'BusGrpId', BG.BusinessGroupKey
   FROM
    dbo.FN_SplitString_AB (@nvBusGrpIds_csv, ',') A
   CROSS APPLY
    Warehouse.dbo.DIM_BusinessGroup BG
   WHERE
    vn = 1 
   AND
    BG.CtrackNodeID = CAST(A.Value AS INT)
  )
  INSERT INTO #xMobiles
  (
     NodeId
   , VehicleKey
   , VehicleId
   , CostCentreName
   , BusGrpId
   , BusGrpKey
   , AssignStart
   , AssignEnd
   , vGrpId
   , vGrpName
  )
  SELECT 
    V.vNodeId
   ,V.VehicleKey
   ,CAST(V.VehicleId AS NVARCHAR(50))
   ,V.CostCentreName
   ,V.BusGrpId
   ,V.BusinessGroupKey
   ,V.CreateDate
   ,COALESCE(V.DeletedTime,V.DeInstalled_DT,'2100-12-31 23:59:59') 
   ,V.vGrpId
   ,CAST(V.vGrpName AS NVARCHAR(100))
  FROM
   dbo.xED_Mobiles_OCC_ViewTable V
  INNER JOIN
   cteBusGrps      B ON B.BusinessGroupKey = V.BusinessGroupKey
  WHERE
   (
    ( CreateDate <= @dtStartDate_LT AND DeletedTime IS NULL ) 
    OR ( CreateDate <= @dtStartDate_LT AND DeletedTime BETWEEN @dtStartDate_LT AND @dtEndDate_LT )
    OR ( CreateDate BETWEEN @dtStartDate_LT AND @dtEndDate_LT ) 
    OR ( @dtStartDate_LT BETWEEN CreateDate AND COALESCE(V.DeletedTime,V.DeInstalled_DT,'2100-12-31 23:59:59')
  ) 

   SELECT
     M.BusGrpKey
    ,M.VehicleKey
    ,M.AssignStart
    ,M.AssignEnd
    ,FT.StartDateUTC
    ,D.LocalStartDateTime
    ,D.LocalEndDateTime
    ,CASE WHEN ISNULL(FT.DriverKey,0) < 1 THEN 0 ELSE ISNULL(FT.DriverKey,0) END 
            'DriverKey'
    ,CASE WHEN ISNULL(FT.DriverKey,0) < 1 THEN 1 ELSE 0 END
            'UnknownTrips'
    ,FTS.HarshAccelerationCount
    ,FTS.HarshBrakeCount
    ,FTS.HarshBumpCount
    ,FTS.HarshCorneringCount
    ,FTS.ExcessIdleDuration
    ,FTS.ExcessIdleCount
    ,FTS.OverspeedCount
    ,A.Spd20Count
    ,A.Spd20Distance
    ,A.Spd30Count
    ,A.Spd30Distance
    ,A.Spd40Count
    ,A.Spd40Distance
    ,A.Spd50Count
    ,A.Spd50Distance
    ,A.Spd60Count
    ,A.Spd60Distance
    ,A.Spd70Count
    ,A.Spd70Distance
    ,FT.TripDistance
    ,CONVERT(NUMERIC(10,0),FT.TripTime) * 83.33 'Ignore'
    ,FT.DrivingTime
    ,A.TotalIdling
    ,A.PTOTime
    ,FT.TripTime
    ,CAST( A.TripFuel AS DECIMAL(10,3))
            'TripFuel'
    ,CASE 
      WHEN CAST( A.TripFuel AS DECIMAL(10,3)) > 0 
       THEN FT.TripDistance
      ELSE 0
     END      'Tot_TF_DistTravelled'
    ,A.SpdCat1
    ,CAST(A.SpdCat2 AS INT)  'SpdCat2'
    ,CAST(A.SpdCat3 AS INT)  'SpdCat3'
    ,CASE   --EX-660
     WHEN FTA.Attribute0Distance > 0  THEN 0 
     WHEN FTA.Attribute1Distance > 0  THEN 1
     WHEN FTA.Attribute2Distance > 0  THEN 2
     WHEN FTA.Attribute3Distance > 0  THEN 3
     WHEN FTA.Attribute4Distance > 0  THEN 4
     WHEN FTA.Attribute5Distance > 0  THEN 5
     WHEN FTA.Attribute6Distance > 0  THEN 6
     WHEN FTA.Attribute7Distance > 0  THEN 7
     WHEN FTA.Attribute8Distance > 0  THEN 8
     WHEN FTA.Attribute9Distance > 0  THEN 9
     WHEN FTA.Attribute10Distance > 0 THEN 10
     ELSE 0
     END        'AttributeTypeId'
    ,ISNULL( TC.IsBusinessPrivate,0 ) 'BPOverride'
   FROM
    #xMobiles        M
   INNER JOIN
    Warehouse.dbo.FACT_Trip    FT ON FT.VehicleKey = M.VehicleKey
               AND FT.StartDateUTC BETWEEN @dtStartDate_LT AND @dtEndDate_LT
               AND FT.TripDistance < CONVERT(NUMERIC(10,0),FT.TripTime) * 83.33
   INNER JOIN
    dbo.xFactTrip_Annex      A ON A.VehicleKey = FT.VehicleKey
               AND A.StartDateUTC = FT.StartDateUTC
   INNER JOIN
    Warehouse.dbo.FACT_Trip_Statuses FTS ON FTS.VehicleKey = FT.VehicleKey
               AND FTS.StartDateUTC= FT.StartDateUTC
   INNER JOIN 
    Warehouse.dbo.FACT_Trip_Attributes FTA ON FTA.VehicleKey = FT.VehicleKey 
               AND FTA.StartDateUTC= FT.StartDateUTC
   LEFT JOIN
    Warehouse.dbo.FACT_TripComments  TC ON TC.VehicleKey = FT.VehicleKey
               AND TC.StartDateUTC = FT.StartDateUTC
   CROSS APPLY
    dbo.FN_GetLocalTime_FromUTC_BasedOnTZId(FT.StartDateUTC, FT.EndDateUTC, 2) D
   WHERE
   (
    ( FT.StartDateUTC BETWEEN M.AssignStart AND M.AssignEnd )
    OR ( M.AssignStart <= FT.StartDateUTC AND M.AssignEnd BETWEEN FT.StartDateUTC AND FT.EndDateUTC )
   ) 
  • the MAXDOP hint is added to prevent the query going parallel. Your recent amendment has slowed the query down a little. Also, the CROSS APPLy function is required and removing it makes no difference to the speed. – Mazhar Oct 12 '17 at 14:45

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.