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

Entretanto, 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 A

O 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()