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.