none
Como eliminar o Operador Hash Match (inner join) RRS feed

  • Pergunta

  • Pessoal, estou na dúvida sobre como resolver um problema de custo alto do operador físico Hash Match (inner join), pois tenho dois deste no Execution Plan da querie abaixo.

    Ajustei um índice que existia e reduzir consideravelmente a quantidade de leitura lógica na table3, onde o tempo de execução estava com 13s e caiu pra 7s. Queria entender como posso reduzir mais e eliminar esse operador.

     set statistics io on
     SELECT Table1.Table1_cod,
           Table1.Table1_tit,
           Table1.Table1_rec,
           Table1.Table1_sld,
           Table2.Table2_descr,
           Table1.Table1_dini,
           Sum (CASE
                  WHEN Table4.Table4_forma_pag = 'X' THEN 0
                  ELSE Table4.Table4_valor
                END) AS v_recep_valor
    FROM   Table1,
           Table2,
           Table3 ,
           Table4,
           Table5
    WHERE  ( Table2.Table2_cod = Table1.Table1_Table2_cod )
           AND ( ( Table2.Table2_tipo = 'P' )
                 AND ( Table1.Table1_tipo = 1 )
                 AND ( Table1.Table1_sld IS NOT NULL ) )
           AND ( Table4.Table4_Table3_serie = Table3.Table3_serie )
           AND ( Table4.Table4_Table3_seq = Table3.Table3_seq )
           AND ( Table5.Table5_cod = Table3.Table3_Table5_recep )
           AND ( Table4.Table4_recepcao = 'R' )
           AND ( Table4.Table4_forma_pag IN ( 'P', 'C', 'R', 'N',
                                        'E', 'B', 'X' ) )
           AND ( Table3.Table3_status = 'D' )
           AND ( Table3.Table3_del_logica <> 'S' )
           AND ( Table3.Table3_tipo <> 'D' )
           AND ( ( Table1.Table1_cod = Table5.Table5_Table1_cod_recep
                   AND Table4.Table4_Table1_caixa IS NULL )
                  OR ( Table1.Table1_cod = Table4.Table4_Table1_caixa ) )
    GROUP  BY Table1.Table1_cod,
              Table1.Table1_tit,
              Table1.Table1_rec,
              Table1.Table1_sld,
              Table2.Table2_descr,
              Table1.Table1_dini
    ORDER  BY Table2.Table2_descr ASC,
              Table1.Table1_tit ASC  
              set statistics io off
    --(36 row(s) affected)
    --Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    --Table 'Table3'. Scan count 2, logical reads 1624, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    --Table 'Table5'. Scan count 1, logical reads 184, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    --Table 'Table1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    --Table 'Table2'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    --Table 'Table4'. Scan count 7, logical reads 384, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    terça-feira, 3 de junho de 2014 02:09

Respostas

Todas as Respostas

  • Deleted
    quinta-feira, 5 de junho de 2014 21:47
  • De fato José gerou uma melhor eficiência, porém ainda acho que esse IN deve ser extinto buscando os devs uma outra alternativa. Talvez se usassem BETWEEN não seria melhor?

    Veja usando a alteração sugerida como ficou as leituras:

    Table '#1E178311'. Scan count 0, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    (7 row(s) affected)
    (1 row(s) affected)
    (39 row(s) affected)
    Table 'Worktable'. Scan count 1, logical reads 147882, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Table3'. Scan count 2, logical reads 1714, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Table4'. Scan count 1, logical reads 363, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table '#1E178311'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Table1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Table2'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Table5'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (39 row(s) affected)
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Table3'. Scan count 2, logical reads 1715, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Table5'. Scan count 1, logical reads 184, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Table1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Table2'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Table4'. Scan count 7, logical reads 388, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


    segunda-feira, 16 de junho de 2014 14:59
  • Deleted
    segunda-feira, 16 de junho de 2014 19:18