SQL: CURSORES vs UPDATE FROM

A diferença de desempenho é absurda!

Quando precisamos atualizar muitos dados no banco com base em registros de outra tabela, geralmente recorremos aos CURSORES.

Cursor é uma estrutura que nos permite pegar o resultado de uma consulta e para cada registro realizar uma ação.

Neste artigo, vou mostrar a diferença de desempenho entre o uso de CURSOR e a técnica do UPDATE com a cláusula FROM. Embora a dica também se aplique ao DELETE com FROM, vou focar na comparação entre CURSOR e UPDATE FROM para simplificar.

Vou demonstrar isso usando o banco de dados Adventure Works 2019, um banco de dados fictício frequentemente usado pela Microsoft em seus treinamentos sobre bancos de dados e aplicativos.

CENÁRIO:

Todos os registros da tabela EmailAddress possuem o domínio “@adventure-works.com”. Precisamos atualizar a tabela EmailAddress com base no PersonType. Para isso, vamos relacionar a tabela EmailAddress com a tabela Person para obtermos o PersonType.

Diagrama gerado usando a ferramenta DBeaver com o banco de dados Adventure Works.

Conforme a documentação da tabela Person, a coluna PersonType pode ter os seguintes valores:

• SC = Store Contact
• IN = Individual (retail) customer
• SP = Sales person
• EM = Employee (non-sales)
• VC = Vendor contact
• GC = General contact

A regra que vamos definir é simples:

Se o tipo de pessoa for EM, então o novo domínio será @employee.com.

Se o tipo de pessoa for SC, o novo domínio será @store-contact.com.

Se o tipo de pessoa for IN, o novo domínio será @individual.com.

Caso contrário, o domínio será @else.com.br.

SOLUÇÃO USANDO CURSOR:

Usar cursores parece ser uma ótima solução, já que precisamos unir cada registro da tabela Person com a tabela EmailAddress para identificar o PersonType e, a partir disso, atualizar o domínio do e-mail.

Porém, a complexidade disso é de O(n), onde ‘n’ representa o número de registros a serem processados, ou seja, o tempo de execução está diretamente ligado a quantidade de registros da consulta.

DECLARE 
 
 @Id Int,
 @PersonType NCHAR(2)
   
DECLARE cursorName CURSOR FOR

SELECT
 P.BusinessEntityID,
 P.PersonType
FROM
 Person.EmailAddress E 
INNER JOIN Person.Person P ON
 E.BusinessEntityID = P.BusinessEntityID
  
 OPEN cursorName
 
FETCH NEXT FROM cursorName INTO @Id, @PersonType
WHILE @@FETCH_STATUS = 0
BEGIN
  
 UPDATE
  Person.EmailAddress
 SET
  EmailAddress = CASE
   WHEN @PersonType = 'EM' THEN REPLACE(EmailAddress, '@adventure-works.com', '@employee.com')
   WHEN @PersonType = 'SC' THEN REPLACE(EmailAddress, '@adventure-works.com', '@store-contact.com')
   WHEN @PersonType = 'IN' THEN REPLACE(EmailAddress, '@adventure-works.com', '@individual.com')
   ELSE REPLACE(EmailAddress, '@adventure-works.com', '@else.com')
  END
 WHERE
  BusinessEntityID = @Id
 
FETCH NEXT FROM cursorName INTO @Id, @PersonType
END  
CLOSE cursorName
DEALLOCATE cursorName

Resultado:

O script foi executado em 14654 milissegundos (14,6 segundos) e ao todo 19972 registros foram atualizados.

Dividindo o tempo gasto pela quantidade de registros atualizados, teremos:

• 1,36 registros por microssegundo.
• 1362,90 (+1,3 Mil) registros por segundo.
• 81774,26 (+81 Mil) registros por minuto.
• 4906455,58 (+4,9 Milhões) registros por hora.

SOLUÇÃO USANDO UPDATE FROM:

UPDATE
	Person.EmailAddress
SET
	EmailAddress = 
	CASE
		WHEN P.PersonType = 'EM' THEN REPLACE(EmailAddress, '@adventure-works.com', '@employee.com')
		WHEN P.PersonType = 'SC' THEN REPLACE(EmailAddress, '@adventure-works.com', '@store-contact.com')
		WHEN P.PersonType = 'IN' THEN REPLACE(EmailAddress, '@adventure-works.com', '@individual.com')
		ELSE REPLACE(EmailAddress, '@adventure-works.com', '@else.com')
	END
FROM
		Person.EmailAddress E
INNER JOIN Person.Person P ON
		E.BusinessEntityID = P.BusinessEntityID

Resultado:

O script foi executado em 210 milissegundos (0,21 segundo) e mesma quantidade de registros foi alterada.

Dividindo o tempo gasto pela quantidade de registros atualizados, teremos:

• 95,10 registros por microssegundo.
• 95104,76 (+95 Mil) registros por segundo.
• 5706285,71 (+5,7 Milhões) registros por minuto.
• 342377142,86 (+342,3 Milhões) registros por hora.

Conclusão

Embora os cursores possam ser úteis em cenários complexos, é crucial considerar se as tarefas podem ser executadas de modo mais eficiente usando UPDATE/DELETE com a cláusula FROM.

contato: fabriciosiqp@gmail.com