none
Update com subqueries em colunas RRS feed

  • Pergunta

  • Bom dia Srs.,

    a alguns dias peguei um procedure que em uma parte dela esta consumindo muito do meu hardware (update cheio de subqueries), gostaria de saber se alguém saberia como posso estar melhorando o mesmo, segue abaixo:

    UPDATE

    HISTORICO.bel.TB_EST_ESTOQUE

    SET

    qtddis = isnull( ( SELECT SUM(qtddis)

     

    FROM [widl].[Z_ESTOQUE]

     

    WHERE procod = HISTORICO.bel.TB_EST_ESTOQUE.procod AND

    filial

    = HISTORICO.bel.TB_EST_ESTOQUE.filial ), 0 ),

    qtdalocada

    = isnull( ( SELECT SUM(qtdalocada)

     

    FROM [widl].[Z_ESTOQUE]

     

    WHERE procod = HISTORICO.bel.TB_EST_ESTOQUE.procod AND

    filial

    = HISTORICO.bel.TB_EST_ESTOQUE.filial ), 0 ),

    qtdpedcom

    = qtdpedcom + isnull( ( SELECT SUM( pdcproqtdp - pdcproqtde )

     

    FROM [widl].[PED01] a1,

    [widl]

    .[PEDC01] a2

     

    WHERE a2.procod = HISTORICO.bel.TB_EST_ESTOQUE.procod AND

    a2

    .filcgc = HISTORICO.bel.TB_EST_ESTOQUE.filial AND

    a1

    .pdcsituaca IN ( '0', '3' ) AND

    a1

    .filcgc = a2.filcgc AND

    a1

    .pdcnro = a2.pdcnro AND

    a2

    .pdcproflag <> '2' AND

    a2

    .pdcproqtdp > a2.pdcproqtde

     

    GROUP BY a2.filcgc, a2.procod ), 0 ),

    qtdestseg

    = isnull( ( SELECT SUM( a2.proalmqtdm )

     

    FROM [widl].[PROD01G2] AS a2

     

    WHERE a2.procod = HISTORICO.bel.TB_EST_ESTOQUE.procod AND

    a2

    .profilcod = HISTORICO.bel.TB_EST_ESTOQUE.filial

     

    GROUP BY a2.procod, a2.profilcod ), 0 ),

    qtdmedven

    = isnull( ( SELECT SUM( a1.nfsitqtd ) / 3

     

    FROM [widl].[NFC003] a1,

    [widl]

    .[NFC001] a2,

    [widl]

    .[MOV01] a3

     

    WHERE a1.nfsitcod = HISTORICO.bel.TB_EST_ESTOQUE.procod AND

    a1

    .nfsfilcgc = HISTORICO.bel.TB_EST_ESTOQUE.filial AND

    a1

    .nfsfilcgc = a2.nfsfilcgc AND

    a1

    .nfsnfnro = a2.nfsnfnro AND

    a1

    .nfsnfser = a2.nfsnfser AND

    a2

    .nfsdtemiss > '20110616' AND

    a2

    .nfscancela <> '*' AND

    a2

    .nfsmovcod = a3.movcod AND

    a3

    .movtipo = 'S' AND

    a3

    .movest = 'S' ), 0 )
    quarta-feira, 6 de julho de 2011 14:54

Todas as Respostas

  • Andre

    Sem ter os dados da tabela para simular é um pouco complicado... mas olhando rápido vc faz varios joins com a mesma tabela... talvez  se vc fizer um UPDATE FROM com join uma unica vez seria mais performatico...

    Verifique o plano de execução qual o ponto mais custoso? os indices esta cobrindo bem a consulta?

    Att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    quarta-feira, 6 de julho de 2011 15:24
  • Andre,

    Como o Marcelo falou, são muitas coisas que podem estar degradando. Você pode utilizar filtros que não estão sendo utilizado em índices, tipos de dados diferentes entre as tabelas, alguns where's estão utilizando filtros que não são nada performáticos(ex.: nfscancela <> '*', situacao IN ('0','3')). A sua consulta como um todo teria que ser revista.

    Para uma análise completa e minusciosa seria necessário, além do plano de execução, a estrutura de todas as tabelas envolvidas e seus índices.

    Mas de qualquer forma o que o Marcelo falou deve ajudar e eu fiz mais ou menos tentanto unir o que pude em um UPDATE para custar menos do banco.

    Segue abaixo:

    UPDATE
      e
    SET
      qtddis      =  isNull(SUM(z.qtddis),0),
      qtdalocada    =  isNull(SUM(z.qtdalocada),0)
    FROM
      [widl].[Z_ESTOQUE]                z
      inner join HISTORICO.bel.TB_EST_ESTOQUE      e
        on  z.procod = e.procod
        AND z.filial= e.filial
    
    UPDATE
      e
    SET
      qtdpedcom = isNull(qtdpedcom,0) + isNull(SUM( pdcproqtdp - pdcproqtde ),0)
    FROM
      [widl].[PED01] a1
      inner join [widl].[PEDC01] a2
        on  a1.filcgc = a2.filcgc
        AND a1.pdcnro = a2.pdcnro
        AND a1.pdcproqtdp > a2.pdcproqtde
      inner join HISTORICO.bel.TB_EST_ESTOQUE e
        on  e.procod = a2.procod
        and e.filial = a2.filcgc
    WHERE
        a1.pdcsituaca IN ( '0', '3' )
      AND a2.pdcproflag <> '2'  
    GROUP BY
      a2.filcgc,
      a2.procod
    
    UPDATE
      qtdestseg = isNull(SUM( a2.proalmqtdm ),0)
    FROM
      [widl].[PROD01G2] AS a2
      inner join HISTORICO.bel.TB_EST_ESTOQUE e
        on  a2.procod = HISTORICO.bel.TB_EST_ESTOQUE.procod
        AND a2.profilcod = HISTORICO.bel.TB_EST_ESTOQUE.filial
    GROUP BY
      a2.procod,
      a2.profilcod
    
    UPDATE
      qtdmedven = isNull(SUM( a1.nfsitqtd ) / 3,0)
    FROM
      [widl].[NFC003] a1
      inner join [widl].[NFC001] a2
        on  a1.nfsfilcgc = a2.nfsfilcgc
        AND a1.nfsnfnro = a2.nfsnfnro
        AND a1.nfsnfser = a2.nfsnfser
      inner join [widl].[MOV01] a3
        on  a2.nfsmovcod = a3.movcod
      inner join HISTORICO.bel.TB_EST_ESTOQUE e
        on  a1.nfsitcod = e.procod
        AND a1.nfsfilcgc = e.filial
    WHERE
        a2.nfsdtemiss > '20110616'
      AND a2.nfscancela <> '*'
      AND a3.movtipo = 'S'
      AND a3.movest = 'S'
    
    Att,


    Fredy Esmeraldo
    Microsoft MCP, MCTS, MCITP Database Administrator 2008
    Visite o meu blog: http://fredyesmeraldo.wordpress.com
    Me siga no twitter: @fredyesmeraldo
    LinkedIN: Fredy Esmeraldo
    **Ajude a melhorar o sistema de busca do fórum.Marque a(s) resposta(s) que foram úteis**
    **Se esta resposta solucionou a questão, então, por favor, marque-a como resposta.**
    quarta-feira, 6 de julho de 2011 15:35
  • André,

    Outra consideração importante, que devemos analisar, estas subquerys realmente são necessárias?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
    quinta-feira, 7 de julho de 2011 21:33
  • Boa tarde...

    Nós tivemos também situações de UPDATE com bastante join , o que causou um pouco de lentidão....

    Acredito que as suas SUBQUERY são necessárias até mesmo porque estao nas clausulas WHERE.... Bom nós fizemos assim, colocamos os novos valores em variaveis e depois aplicamos o update, e pode acreditar ficou bem mais rapido... nao vou expor o seu SQL INTEIRO, mas veja :

    OLD:

    UPDATE
      e
    SET
      qtdpedcom = isNull(qtdpedcom,0) + isNull(SUM( pdcproqtdp - pdcproqtde ),0)
    FROM
      [widl].[PED01] a1
     
    inner join [widl].[PEDC01] a2
       
    on  a1.filcgc = a2.filcgc
       
    AND a1.pdcnro = a2.pdcnro
       
    AND a1.pdcproqtdp > a2.pdcproqtde
     
    inner join HISTORICO.bel.TB_EST_ESTOQUE e
       
    on  e.procod = a2.procod
       
    and e.filial = a2.filcgc
    WHERE
        a1.pdcsituaca IN ( '0', '3' )
     
    AND a2.pdcproflag <> '2' 
    GROUP BY
      a2.filcgc,
      a2.procod

    NEW:

    DECLARE @qtdpedcom FLOAT

    SELECT @qtdpedcom  = ISNULL(SUM(QTDDIS),0) FROM TB_EST_ESTOQUE WHERE Condicao 1 AND Condicao 2 AND Conidcao .....

    UPDATE Tabela SET coluna =  @qtdpedcom WHERE Condicao 1

     

     

    Acredito que melhore a perfomance ai....


    Isco Sistemas José Luiz Borges
    quarta-feira, 13 de julho de 2011 16:27