Um Update que não deu certo


E ai galerinha…depois de algum tempo "sem tempo" pra postar, vou postar um update que peguei num projeto que estou agora, diga-se de passagem que estou trabalhando numa média de 31 centígrados (fora da empresa claro) e de frente pro mar..ta chatuuuuuuuuuuuu !!!!!!
 
Este projeto é bem lega..o projeto é bom…vou estar postando bastante coisa…
 
Essa é fácil..
 
Olha o update
 
set statistics io on
update  estpro02
set  pro02_entdat= isnull(( select  top 1  pro00_entdat 
                                       from   estpro00(nolock)                                                     
                                       where  pro00_codfil=pro02_codfil                                                      
                                        and  pro00_codpro=pro02_codpro),null),
  pro02_entqtd= isnull((      select  top 1 pro00_entqtd 
                                        from  estpro00(nolock)                                                     
                                        where  pro00_codfil=pro02_codfil                                                     
                                        and  pro00_codpro=pro02_codpro),0000),                                
  pro02_vendat= isnull((      select  top 1 pro00_vendat
                                         from  estpro00(nolock)                                                     
                                         where  pro00_codfil=pro02_codfil                                                     
                                         and  pro00_codpro=pro02_codpro),null),
  pro02_venmes= isnull(( select  top 1 pro00_venmes 
                                     from  estpro00(nolock)                                                      
                                     where  pro00_codfil=pro02_codfil                                                     
                                     and  pro00_codpro=pro02_codpro),0000)                         
where  pro02_codfil=01
 
(98469 row(s) affected)
42 segundos
 
Table ‘estpro02’. Scan count 1, logical reads 301818, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 393876, logical reads 36329634, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘estpro00’. Scan count 4, logical reads 503388, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
Que foi alterado por este :
 
dbcc dropcleanbuffers
 
update  A
set       A.pro02_entdat= b.pro00_entdat,
            A.pro02_entqtd= isnull(B.pro00_entqtd ,0000),
            A.pro02_vendat= B.pro00_vendat ,
            A.pro02_venmes= isnull(B.pro00_venmes,0000)
from     estpro02 A
left    join estpro00 B
on A.pro02_codfil = B.pro00_codfil
and A.pro02_codpro = B.pro00_codpro
where A.pro02_codfil=01
 
(98469 row(s) affected)
1 segundo
 
Table ‘estpro02’. Scan count 1, logical reads 301818, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘estpro00’. Scan count 0, logical reads 301609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
Eu ja não esperava por um valor muito menor em logical reads, pois mesmo no primeiro update os indices estavam sendo usados e era um numero bom de linhas atualizados.
 
Mas podemos ver que o scan count diminuiu 100% , o logical read continuou praticamente o mesmo e tiramos a worktable.
 
Legal…diminuimos os logical reads e de 42 s passou pra 1.
 
Falowwww

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in SQL SERVER EM GERAL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s