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)
asked Oct 12, 2017 at 12:53
5
  • 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? Commented Oct 12, 2017 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)? Commented Oct 12, 2017 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. Commented Dec 14, 2017 at 14:11
  • @Nick.McDermaid this is not a Leica database. The name of the function being the same is just a coincidence Commented Dec 18, 2017 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 Commented Dec 18, 2017 at 13:56

2 Answers 2

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.

answered Dec 18, 2017 at 15:31
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 )
 )
answered Oct 12, 2017 at 14:18
1
  • 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. Commented Oct 12, 2017 at 14:45

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.