Em um post anterior, demonstramos como melhorar a performance (1400%+) de um procedimento, modificando a abordagem de linha-a-linha (row by row), para outra baseada em blocos (set-based).
O código proposto na solução final era o seguinte:
use tempDB
go
set nocount on
update A set
A.line = B.line
from dbo.ProcessoDemorado as A
inner join
(
select
A.id,
A.rowNumber - A.eventChange as line
from
(
select
A.ID,
sum(iif(A.typeID not in (1, 2), 0, 1)) over (order by A.orderDate, A.value, A.typeID, A.id rows between unbounded preceding and current row) as eventChange,
row_number() over(order by A.orderDate, A.value, A.typeID, A.id) as rowNumber
from dbo.ProcessoDemorado as A
) as A
) as B
on A.ID = B.IDEntretanto, identificamos outra oportunidade de otimização observando o plano de execução.
Devido ao uso das windows functions (elas só são permitidas nas cláusulas select e order by), se faz obrigatório o uso do join para executar o update.
Esse código habilita o SQL Server para utilizar paralelismo para distribuir o processamento dos dados entre as CPUs, e assim, ganhando ainda mais performance na execução da consulta (sem o paralelismo o código acima demora cerca de 4 segundos no meu ambiente). Porém, nesse plano de execução, chama atenção o custo de dois scans na tabela dbo.ProcessoDemorado, que tem um milhão de linhas, e que poderia ser evitado. Abaixo, demonstramos como contornar essa “limitação” do SQL Server removendo o join obrigatório:
use tempdb
go
set nocount on
;with cteUpdate
as
(
select
A.id,
A.line,
A.rowNumber - A.eventChange as newLine
from
(
select
A.ID,
A.line,
row_number() over (order by A.orderDate, A.value, A.typeID, A.id) as rowNumber,
sum(iif(A.typeID not in ( 1, 2 ), 0, 1)) over (order by A.orderDate, A.value, A.typeID, A.id rows between unbounded preceding and current row) as eventChange
from dbo.ProcessoDemorado as A
) as A
)
update A set
A.line = A.newLine
from cteUpdate as AO código acima gera um plano de execução com apenas um scan na tabela e sem o hash join do original. Ele ficou mais rápido e utiliza menos CPU que o código original.
A consulta original alocava cerca de 461 MBs, utilizando destes apenas 150 MBs. Com a refatoração do código, apenas 272 MBs são alocados e destes, 131 MBs foram realmente utilizados.
Dica Bônus
Essa mesma técnica pode ser utilizada para resolver outro tipo de problema no SQL Server: Executar um update com order by!
use AdventureWorks2019
go
;with cte
as
(
select top (50)
P.ModifiedDate
from Person.Person as P
order by P.ModifiedDate desc
)
update cte set cte.ModifiedDate = getdate()
