Improve Parameterized Query Performance 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

Depending on the parameters being passed the results can either be a few hundred rows (which executes sub second) to &gt; 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:

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 &lt; ( 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 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

LOOP join hint and TF 4199

No join hints and TF 4199

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