Tunning SQL Server Query

Lets assume that you have a big query wherein you are joining 15+ tables and performing aggregation, and outer join operations on same data set.

This whole operation I was having inside a function which was returning a table data type to a sql which was again getting called into a stored proc.

Now, this entire setup was taking hours to fetch the data, since data volume was huge and even number of tables were involved in outer joins.

To overcome this performance issues, I segregated the query into different party [mainly the tables which were involved into inner joins]

Part -1
select PolicyTermKey, LocationNumber, Sublocation, max(CycleDt) as CycleDt, max(DriverKey) as DriverKey
INTO #PVD2
from THZ3.PersonalVehicleDriver P WITH (NOLOCK)
where CycleDt <= @datEOMDt
group by PolicyTermKey, LocationNumber, Sublocation

CREATE INDEX IDX
ON #PVD2(PolicyTermKey, LocationNumber, Sublocation, DriverKey)

Part -2 select p2.PolicyTermKey, p2.driverkey, max(p2.cycledt) cycledt
INTO #p3
from THZ3.PersonalDriver P2 WITH (NOLOCK)
where p2.CycleDt <= @datEOMDt
group by p2.PolicyTermKey, p2.DRIVERKEY

CREATE INDEX IDX
ON #p3(PolicyTermKey, driverkey)

Part -3
select p.PolicyTermKey
, p3.CycleDt as CycleDt
, p.DriverKey as DriverKey into #pd2
from THZ3.PersonalDriver P WITH (NOLOCK)--,INDEX=IX_POLICYLOCATION_5)
join #p3 p3
on p3.PolicyTermKey = p.PolicyTermKey
and p3.driverkey = p.driverkey
where p.CycleDt <= @datEOMDt
group by p.PolicyTermKey, p3.CycleDt, p.DriverKey

CREATE INDEX IDX
ON #pd2(PolicyTermKey, driverkey)

Part -4
SELECT pvd.*
, PAD.POLICYTERMKEY AS PAD_POLICYTERMKEY
, PAD.CYCLEDT AS PAD_CYCLEDT
, PAD.DRIVERKEY AS PAD_DRIVERKEY
, PAD.PADRIVERDIMKEY
, PAD.BIRTHYEAR
, PAD.LICENSEDYEAR
, PAD.HIREDYEAR INTO #BIGJOIN
FROM THZ3.PersonalVehicleDriver pvd WITH (NOLOCK)
JOIN #PVD2 pvd2
on pvd.PolicyTermKey = pvd2.PolicyTermKey
and pvd.LocationNumber = pvd2.LocationNumber
and pvd.Sublocation = pvd2.Sublocation
and pvd.DriverKey = pvd2.DriverKey
JOIN #pd2 pd2
on pd2.PolicyTermKey = pvd.PolicyTermKey
AND PD2.DriverKey = PVD.DRIVERKEY
join THZ3.PADriverDim_lu_eom pad
on pad.PolicyTermKey = pd2.PolicyTermKey
and pad.CycleDt = pd2.CycleDt
and pad.DriverKey = pd2.DriverKey

Part -5
select PolicyTermKey, LocationNumber, SubLocation,
max(PAVDiscountsSurchargesKey) PAVDiscountsSurchargesKEY,
max(PAVehicleDIMKey) PAVehicleDIMKey,
max(PAVEHICLEAGEDIMKEY) PAVehicleAgeDIMKey,
max(PADRIVERDIMKEY) PADriverDIMKey,
max(PADRIVERAGEDIMKEY) PADriverAgeDIMKey,
max(PAPolicyRateDimKey) PAPolicyRateDimKey INTO THZ3.TempPolicyKeys
from (
select distinct
BIGJOIN.PolicyTermKey
,BIGJOIN.LocationNumber
,BIGJOIN.SubLocation
,isnull(pv.PAVDiscountsSurchargesKey, 0) as PAVDiscountsSurchargesKey
,isnull(pv.PAVehicleDIMKey, -1) as PAVehicleDIMKey
,isnull(pavad.PAVEHICLEAGEDIMKEY, -1) as PAVEHICLEAGEDIMKEY
,isnull(BIGJOIN.PADriverDimKey, -1) as PADRIVERDIMKEY
,isnull(pada.PADriverAgeDimKey, -1)as PADRIVERAGEDIMKEY
,isnull(paPr.PAPolicyRateDimKey, -1) as PAPolicyRateDimKey
-----------------------------------------------------------------------
-- Personal Vehicle Driver FROM #BIGJOIN BIGJOIN
------------------------------------------------------------------------- PA Driver Age DIM
left outer join THZ3.PADriverAgeDim pada
on pada.ReportYear = DATEPART(YYYY,BIGJOIN.CycleDt)
AND pada.BirthYear = BIGJOIN.BirthYear
AND pada.LicensedYear = BIGJOIN.LicensedYear
AND pada.HiredYear = BIGJOIN.HiredYear
AND pada.PADriverAgeDimKey != -1
and ReportYear <> '~'
<And many other joins>

If you see above query, it was divided into small parts and output of each part is stored into a Temporary Table (#), which has visibility only for that particular session.
We also have created an index on each Temporary Table. All these tables we have used to join a big query.

All tables involved in Outer Joins I have kept as is, and only Inner Join tables I have moved into Temporary tables.