Update com subqueries em colunas

Question

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 )

Answer 1

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

Answer 2

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.**

Answer 3

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]

Answer 4

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