Improving lots of Left Joins ( SQL Server 2008 - procedure )

Question

This query is running with 8 minutes to complete.

I'm trying to help the programmer with this task.

I've created indexes and stats, update_stats and rebuild indexes but none of this is helping us.

What do you guys do, when you face a query like this??

declare @CodSegmentacao int,
        @CodOndaAtu int, 
        @CodOndaAnt int,
        @CodJobTarget int

set @CodSegmentacao = 9
set @CodOndaAtu = 55
set @CodOndaAnt = 52
set @CodJobTarget = 10771

select 
    TA.Ordem, TA.DescJobRel, TA.CodJob as CodJobAtu, 
    isnull(TB.CodJob, 0) as CodJobAnt,
    isnull(TC.Quant, 0) as AmostraAtu, 
    isnull(TD.Quant, 0) as AmostraAnt,
    isnull(TE.Perc, 0) as TGWTotalAtu, 
    isnull(TF.Perc, 0) as TGWTotalAnt,
    isnull(TG.Perc, 0) as TGWOnlyAtu, 
    isnull(TH.Perc, 0) as TGWOnlyAnt,
    isnull(TI.Perc, 0) as TGWDesigAtu, 
    isnull(TJ.Perc, 0) as TGWDesigAnt,
    isnull(TK.Perc, 0) as DesvioAtu, 
    isnull(TL.Perc, 0) as DesvioAnt,
    isnull(TM.Perc, 0) as DesvioSDAtu, 
    isnull(TN.Perc, 0) as DesvioSDAnt,
    isnull(TP.Perc, 0) as DesvioDAtu, 
    isnull(TQ.Perc, 0) as DesvioDAnt
from 
    (select 
         T2.CodJobRel, T2.DescJobRel, 
         case when T1.FlagTotal = 1 then 0 else case when T4.CodJob = @CodJobTarget then 1 else 2 end end as Ordem,
         T4.CodJob 
     from 
         tblSegmentacaoJobRel T1 with (nolock)
     inner join 
         tblJobRel T2 with (nolock) on T1.CodJobRel = T2.CodJobRel
     inner join 
         tblJobRelJob T3 with (nolock) on T2.CodJobRel = T3.CodJobRel
     inner join 
         tblJob T4 with (nolock) on T3.CodJob = T4.CodJob
     where 
         T1.CodSegmentacao = @CodSegmentacao and T4.CodOnda = @CodOndaAtu) TA
left join 
   (select 
        T2.CodJobRel, T2.DescJobRel, T4.CodJob 
    from 
        tblSegmentacaoJobRel T1 with (nolock)
    inner join 
        tblJobRel T2 with (nolock) on T1.CodJobRel = T2.CodJobRel
    inner join 
        tblJobRelJob T3 with (nolock) on T2.CodJobRel = T3.CodJobRel
    inner join 
        tblJob T4 with (nolock) on T3.CodJob = T4.CodJob
    where 
        T1.CodSegmentacao = @CodSegmentacao and T4.CodOnda = @CodOndaAnt) TB on TA.CodJobRel = TB.CodJobRel
left join 
    tblConsolidadoJob TC with (nolock) on TA.CodJob = TC.CodJob and TC.TipoCons = 'Amostra'
left join 
    tblConsolidadoJob TD with (nolock) on TB.CodJob = TD.CodJob and TD.TipoCons = 'Amostra'
left join 
    tblConsolidadoJob TE with (nolock) on TA.CodJob = TE.CodJob and TE.TipoCons = 'TGWTotal'
left join 
    tblConsolidadoJob TF with (nolock) on TB.CodJob = TF.CodJob and TF.TipoCons = 'TGWTotal'
left join 
    tblConsolidadoJob TG with (nolock) on TA.CodJob = TG.CodJob and TG.TipoCons = 'TGWOnly'
left join 
    tblConsolidadoJob TH with (nolock) on TB.CodJob = TH.CodJob and TH.TipoCons = 'TGWOnly'
left join 
    tblConsolidadoJob TI with (nolock) on TA.CodJob = TI.CodJob and TI.TipoCons = 'TGWDesig'
left join 
    tblConsolidadoJob TJ with (nolock) on TB.CodJob = TJ.CodJob and TJ.TipoCons = 'TGWDesig'
left join 
    tblConsolidadoJob TK with (nolock) on TA.CodJob = TK.CodJob and TK.TipoCons = 'Desvio'
left join 
    tblConsolidadoJob TL with (nolock) on TB.CodJob = TL.CodJob and TL.TipoCons = 'Desvio'
left join 
    tblConsolidadoJob TM with (nolock) on TA.CodJob = TM.CodJob and TM.TipoCons = 'DesvioSD'
left join 
    tblConsolidadoJob TN with (nolock) on TB.CodJob = TN.CodJob and TN.TipoCons = 'DesvioSD'
left join 
    tblConsolidadoJob TP with (nolock) on TA.CodJob = TP.CodJob and TP.TipoCons = 'DesvioD'
left join 
    tblConsolidadoJob TQ with (nolock) on TB.CodJob = TQ.CodJob and TQ.TipoCons = 'DesvioD'
order by 
    TA.Ordem, TA.DescJobRel

Since now, thanks you very much.

I was looking at the execution plan. I didn't see problems there.

Answer 1

If it's stored procedure, perhaps PIVOT tblConsolidadoJob table and insert results into hash table and then use very same hash table on a left join?

Something like this:

SELECT *
INTO #temporary
FROM (
    SELECT CodJob, Quant, TipoCons, Perc
    FROM tblConsolidadoJob
    ) AS SourceTable
PIVOT
(
    MAX(Perc)
    FOR TipoCoins IN ([Amostra],[TGWTotal],[TGWOnly],[TGWDesig],[Desvio],[DesvioSD],[DesvioD])
) AS Pivoted

and then use #temporary table on your left join instead of tblConsolidadoJob. I might be wrong tho. But this could be worth trying?