Left Join or Sub-Select? which one has the best performance?

Question

Left Join or Sub-Select? which one has the best performance?

Asked 6 years, 7 months ago

Viewed 663 times

0

I’m studying which one of the fairs below would perform better. So I wrote them both and ran them in the same database with the same data:

Query 1:

                                select a.numeroOp as OP, a.codigo as CÓDIGO, (a.qntAhProduzir + a.qntParaEstoque) as QNT, 
                        (CONVERT(varchar(100), a.numeroProjeto) + '_' + CONVERT(varchar(100), a.itemProjeto)) as keyColumnOp, 
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.montagem) as decimal(18,2)) as MONT, 
                            case when d.maq = 'MONT' then 1 else 0 end as MONTsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.bancada) as decimal(18,2)) as BANC, 
                            case when e.maq = 'BANC' then 1 else 0 end as BANCsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trP1) as decimal(18,2)) as 'TP 1',
                            case when f.maq = 'TP 1' then 1 else 0 end as TP1semana, 
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trP2) as decimal(18,2)) as 'TP 2',
                            case when g.maq = 'TP 2' then 1 else 0 end as TP2semana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trQ1) as decimal(18,2)) as 'TQ 1', 
                            case when h.maq = 'TQ 1' then 1 else 0 end as TQ1semana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trQ2) as decimal(18,2)) as 'TQ 2', 
                            case when i.maq = 'TQ 2' then 1 else 0 end as TQ2semana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trCarvao) as decimal(18,2)) as 'TCAR',
                            case when j.maq = 'TCAR' then 1 else 0 end as TCARsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.corte) as decimal(18,2)) as 'CORTE', 
                            case when k.maq = 'CORTE' then 1 else 0 end as CORTEsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.fr1) as decimal(18,2)) as 'F 1',
                            case when l.maq = 'F 1' then 1 else 0 end as F1semana, 
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.fr2) as decimal(18,2)) as 'F 2',
                            case when m.maq = 'F 2' then 1 else 0 end as F2semana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.retifica) as decimal(18,2)) as 'RET',
                            case when n.maq = 'RET' then 1 else 0 end as RETsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trcnc1) as decimal(18,2)) as 'TC 1', 
                            case when d.maq = 'TC 1' then 1 else 0 end as TC1semana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trCnc2) as decimal(18,2)) as 'TC 2',
                            case when d.maq = 'TC 2' then 1 else 0 end as TC2semana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.cnc) as decimal(18,2)) as 'CNC', 
                            case when d.maq = 'CNC' then 1 else 0 end as CNCsemana, case 
                                                                                                        when a.processoTempoPlan Is null then 0
                                                                                                        else a.processoTempoPlan
                                                                                                        end as PROD, a.idNo

                        from Pcp_Op as a
                        left join vendas_itensEntregar as b
                        on a.numeroProjeto = b.projeto and a.itemProjeto = b.item
                        left join pcp_tempoProcessos as c
                        on a.codigo = c.codigo
                        left join Pcp_DistribuiSemanaProcessos as d
                        on a.numeroProjeto = d.projeto and a.itemProjeto = d.item and a.idNo = d.idNo and d.maq = 'MONT'
                        left join Pcp_DistribuiSemanaProcessos as e
                        on a.numeroProjeto = e.projeto and a.itemProjeto = e.item and a.idNo = e.idNo and e.maq = 'BANC'
                                left join Pcp_DistribuiSemanaProcessos as f
                        on a.numeroProjeto = f.projeto and a.itemProjeto = f.item and a.idNo = f.idNo and f.maq = 'TP 1'
                                left join Pcp_DistribuiSemanaProcessos as g
                        on a.numeroProjeto = g.projeto and a.itemProjeto = g.item and a.idNo = g.idNo and g.maq = 'TP 2'
                                left join Pcp_DistribuiSemanaProcessos as h
                        on a.numeroProjeto = h.projeto and a.itemProjeto = h.item and a.idNo = h.idNo and h.maq = 'TQ 1'
                                left join Pcp_DistribuiSemanaProcessos as i
                        on a.numeroProjeto = i.projeto and a.itemProjeto = i.item and a.idNo = i.idNo and i.maq = 'TQ 2'
                                left join Pcp_DistribuiSemanaProcessos as j
                        on a.numeroProjeto = j.projeto and a.itemProjeto = j.item and a.idNo = j.idNo and j.maq = 'TCAR'
                                left join Pcp_DistribuiSemanaProcessos as k
                        on a.numeroProjeto = k.projeto and a.itemProjeto = k.item and a.idNo = k.idNo and k.maq = 'CORTE'
                                left join Pcp_DistribuiSemanaProcessos as l
                        on a.numeroProjeto = l.projeto and a.itemProjeto = l.item and a.idNo = l.idNo and l.maq = 'F 1'
                                left join Pcp_DistribuiSemanaProcessos as m
                        on a.numeroProjeto = m.projeto and a.itemProjeto = m.item and a.idNo = m.idNo and m.maq = 'F 2'
                                left join Pcp_DistribuiSemanaProcessos as n
                        on a.numeroProjeto = n.projeto and a.itemProjeto = n.item and a.idNo = n.idNo and n.maq = 'RET'
                                left join Pcp_DistribuiSemanaProcessos as o
                        on a.numeroProjeto = o.projeto and a.itemProjeto = o.item and a.idNo = o.idNo and o.maq = 'TC 1'
                                left join Pcp_DistribuiSemanaProcessos as p
                        on a.numeroProjeto = p.projeto and a.itemProjeto = p.item and a.idNo = p.idNo and p.maq = 'TC 2'
                                left join Pcp_DistribuiSemanaProcessos as q
                        on a.numeroProjeto = q.projeto and a.itemProjeto = q.item and a.idNo = q.idNo and q.maq = 'CNC'
                        where b.statusSql <> 'Ok'

Query 2:

insirselect a.numeroOp as OP, a.codigo as CÓDIGO, (a.qntAhProduzir + a.qntParaEstoque) as QNT, 
                        (CONVERT(varchar(100), a.numeroProjeto) + '_' + CONVERT(varchar(100), a.itemProjeto)) as keyColumnOp, 
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.montagem) as decimal(18,2)) as MONT, 
                            case 
                            when (select count(id) from Pcp_DistribuiSemanaProcessos as t where t.projeto = a.numeroProjeto and t.item = a.itemProjeto and t.idno = a.idno and t.maq = 'MONT') > 0 then 1
                            else 0 end as MONTsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.bancada) as decimal(18,2)) as BANC, 
                            case 
                            when (select count(id) from Pcp_DistribuiSemanaProcessos as t where t.projeto = a.numeroProjeto and t.item = a.itemProjeto and t.idno = a.idno and t.maq = 'BANC') > 0 then 1
                            else 0 end as MONTsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trP1) as decimal(18,2)) as 'TP 1',
                            case 
                            when (select count(id) from Pcp_DistribuiSemanaProcessos as t where t.projeto = a.numeroProjeto and t.item = a.itemProjeto and t.idno = a.idno and t.maq = 'TP 1') > 0 then 1
                            else 0 end as MONTsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trP2) as decimal(18,2)) as 'TP 2',
                            case 
                            when (select count(id) from Pcp_DistribuiSemanaProcessos as t where t.projeto = a.numeroProjeto and t.item = a.itemProjeto and t.idno = a.idno and t.maq = 'TP 2') > 0 then 1
                            else 0 end as MONTsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trQ1) as decimal(18,2)) as 'TQ 1', 
                            case 
                            when (select count(id) from Pcp_DistribuiSemanaProcessos as t where t.projeto = a.numeroProjeto and t.item = a.itemProjeto and t.idno = a.idno and t.maq = 'TQ 1') > 0 then 1
                            else 0 end as MONTsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trQ2) as decimal(18,2)) as 'TQ 2', 
                            case 
                            when (select count(id) from Pcp_DistribuiSemanaProcessos as t where t.projeto = a.numeroProjeto and t.item = a.itemProjeto and t.idno = a.idno and t.maq = 'TQ 2') > 0 then 1
                            else 0 end as MONTsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trCarvao) as decimal(18,2)) as 'TCAR',
                            case 
                            when (select count(id) from Pcp_DistribuiSemanaProcessos as t where t.projeto = a.numeroProjeto and t.item = a.itemProjeto and t.idno = a.idno and t.maq = 'TCAR') > 0 then 1
                            else 0 end as MONTsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.corte) as decimal(18,2)) as 'CORTE', 
                            case 
                            when (select count(id) from Pcp_DistribuiSemanaProcessos as t where t.projeto = a.numeroProjeto and t.item = a.itemProjeto and t.idno = a.idno and t.maq = 'CORTE') > 0 then 1
                            else 0 end as MONTsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.fr1) as decimal(18,2)) as 'F 1',
                            case 
                            when (select count(id) from Pcp_DistribuiSemanaProcessos as t where t.projeto = a.numeroProjeto and t.item = a.itemProjeto and t.idno = a.idno and t.maq = 'F 1') > 0 then 1
                            else 0 end as MONTsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.fr2) as decimal(18,2)) as 'F 2',
                            case 
                            when (select count(id) from Pcp_DistribuiSemanaProcessos as t where t.projeto = a.numeroProjeto and t.item = a.itemProjeto and t.idno = a.idno and t.maq = 'F 2') > 0 then 1
                            else 0 end as MONTsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.retifica) as decimal(18,2)) as 'RET',
                            case 
                            when (select count(id) from Pcp_DistribuiSemanaProcessos as t where t.projeto = a.numeroProjeto and t.item = a.itemProjeto and t.idno = a.idno and t.maq = 'RET') > 0 then 1
                            else 0 end as MONTsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trcnc1) as decimal(18,2)) as 'TC 1', 
                            case 
                            when (select count(id) from Pcp_DistribuiSemanaProcessos as t where t.projeto = a.numeroProjeto and t.item = a.itemProjeto and t.idno = a.idno and t.maq = 'TC 1') > 0 then 1
                            else 0 end as MONTsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trCnc2) as decimal(18,2)) as 'TC 2',
                            case 
                            when (select count(id) from Pcp_DistribuiSemanaProcessos as t where t.projeto = a.numeroProjeto and t.item = a.itemProjeto and t.idno = a.idno and t.maq = 'TC 2') > 0 then 1
                            else 0 end as MONTsemana,
                        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.cnc) as decimal(18,2)) as 'CNC', 
                            case 
                            when (select count(id) from Pcp_DistribuiSemanaProcessos as t where t.projeto = a.numeroProjeto and t.item = a.itemProjeto and t.idno = a.idno and t.maq = 'CNC') > 0 then 1
                            else 0 end as MONTsemana, case
                                                                                                        when a.processoTempoPlan Is null then 0
                                                                                                        else a.processoTempoPlan
                                                                                                        end as PROD, a.idNo

                        from Pcp_Op as a
                        left join vendas_itensEntregar as b
                        on a.numeroProjeto = b.projeto and a.itemProjeto = b.item
                        left join pcp_tempoProcessos as c
                        on a.codigo = c.codigo                      
                        where b.statusSql <> 'Ok'

In query 1, I use Left Join in query 2 I use sub-select

So I ran them, and followed in SQL Profiler:

Result Query 1:

Result Query 2:

From what I was able to analyze, query 2 took less time to be executed, but performed a higher number of read (reads) and used less of the processor capacity, correct?

With this we can say that Query 2, would be more efficient than Query 1?

2 answers

0

By Isaac Junior: The mystery of how Sqlserver operates internally is hard to solve.

It is quite possible that in some cases the difference is only in syntax and Sql Server operates in the same way.

But the subquery, theoretically, would have to be executed on each record of the main query while the Join table would be treated differently. Which makes me think Join is more performance-oriented. But, according to the link below, there is no difference in performance when the queries are equivalent. (As in the case of your example)

See in: http://technet.microsoft.com/en-us/library/ms189575(v=sql.105). aspx When the consultations are equivalent there is no difference in performance. But when the condition of existence (EXISTS) has to be checked with each record of the main query, Join has better performance.

In a large and complex query, subquery may make the query more difficult to read. But it is indispensable in other cases.

I only use subquery when the thing cannot be done with Join.

Browser other questions tagged sql performance

You are not signed in. Login or sign up in order to post.

by Marta B • **269** points · Answer 1 · 2018-02-06T11:55:35+00:00

Have you tried a solution of this kind:

select  a.numeroOp as OP, a.codigo as CÓDIGO, (a.qntAhProduzir + a.qntParaEstoque) as QNT, 
        (CONVERT(varchar(100), a.numeroProjeto) + '_' + CONVERT(varchar(100), a.itemProjeto)) as keyColumnOp, 
        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.montagem) as decimal(18,2)) as MONT, 
            MONTsemana,
        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.bancada) as decimal(18,2)) as BANC, 
            BANCsemana,
        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trP1) as decimal(18,2)) as 'TP 1',
            TP1semana, 
        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trP2) as decimal(18,2)) as 'TP 2',
            TP2semana,
        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trQ1) as decimal(18,2)) as 'TQ 1', 
            TQ1semana,
        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trQ2) as decimal(18,2)) as 'TQ 2', 
            TQ2semana,
        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trCarvao) as decimal(18,2)) as 'TCAR',
            TCARsemana,
        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.corte) as decimal(18,2)) as 'CORTE', 
            CORTEsemana,
        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.fr1) as decimal(18,2)) as 'F 1',
            F1semana, 
        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.fr2) as decimal(18,2)) as 'F 2',
            F2semana,
        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.retifica) as decimal(18,2)) as 'RET',
            RETsemana,
        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trcnc1) as decimal(18,2)) as 'TC 1', 
            TC1semana,
        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.trCnc2) as decimal(18,2)) as 'TC 2',
            TC2semana,
        cast(((a.qntAhProduzir + a.qntParaEstoque) * c.cnc) as decimal(18,2)) as 'CNC', 
            CNCsemana, case 
                        when a.processoTempoPlan Is null then 0
                        else a.processoTempoPlan
                        end as PROD, a.idNo

from Pcp_Op as a
    left join vendas_itensEntregar as b
        on a.numeroProjeto = b.projeto and a.itemProjeto = b.item
    left join pcp_tempoProcessos as c
        on a.codigo = c.codigo

    left join (select   projeto, item, idNo,
                        MONTsemana = max(case when maq = 'MONT' then 1 else 0 end),
                        BANCsemana = max(case when maq = 'BANC' then 1 else 0 end),
                        TP1semana = max(case when maq = 'TP 1' then 1 else 0 end),
                        TP2semana = max(case when maq = 'TP 2' then 1 else 0 end),
                        TQ1semana = max(case when maq = 'TQ 1' then 1 else 0 end),
                        TQ2semana = max(case when maq = 'TQ 2' then 1 else 0 end),
                        TCARsemana = max(case when maq = 'TCAR' then 1 else 0 end),
                        CORTEsemana = max(case when maq = 'CORTE' then 1 else 0 end),
                        F1semana = max(case when maq = 'F 1' then 1 else 0 end),
                        F2semana = max(case when maq = 'F 2' then 1 else 0 end),
                        RETsemana = max(case when maq = 'RET' then 1 else 0 end),
                        TC1semana = max(case when maq = 'TC 1' then 1 else 0 end),
                        TC2semana = max(case when maq = 'TC 2' then 1 else 0 end),
                        CNCsemana = max(case when maq = 'CNC' then 1 else 0 end)
                from Pcp_DistribuiSemanaProcessos
                group by projeto, item, idNo)  d on
        a.numeroProjeto = d.projeto and a.itemProjeto = d.item and a.idNo = d.idNo

where b.statusSql <> 'Ok'