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.
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).
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
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
Here's the output for the unchanged query except for adding a
LOOP JOIN hint. This is slower.
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
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
LOOP join hint and TF 4199
No join hints and TF 4199
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)