Oi! Seja bem-vindo!

Hoje vamos comparar a performance das 5 formas de gerar números sequenciais mais populares do mercado. Além de aprender como gerar sequências numéricas, nesse artigo você verá como comparar o tempo de execução e o custo computacional de códigos SQL.

 

 

Como medir o tempo de execução de códigos SQL

Cronometro

Uma das medidas mais diretas de performance é o tempo de execução de um determinado código. Para medir isso basta começar salvando a data hora em uma variável datetime, e após a execução do código, demonstrar o tempo transcorrido, diminuindo a data hora atual da data hora salva na variável:

-- Salvar data hora na variável @cronometro
declare @cronometro datetime = getdate()

-- Executar o código que você deseja medir
;with cte as (
	select 1 as sequencia
	union all
	select sequencia + 1 as sequencia from cte where sequencia < 100000
)
select * from cte option (maxrecursion 0)

-- Demonstrar o tempo transcorrido
print convert(varchar, getdate() - @cronometro, 114)
go

/*
Resultado:
(100000 row(s) affected)
00:00:00:830
*/

 

Plano de Execução

Outra medida efetiva de performance, é o custo relativo de execução. Quem gera essa informação é o próprio SQL, e para determinarmos que ele deve fazer isso, precisamos ativar a geração através da tecla de atalho “CONTROL + M”, ou então do menu “Query -> Include Actual Execution Plan”.

 

Ativado o plano de execução e rodando o script abaixo:

use master
select * from sys.objects
select * from sys.tables

Você terá como resultado uma nova aba chamada “Execution Plan”, que demonstrará o custo relativo dos comandos executados de forma similar a essa imagem:

A informação que nos interessa aqui é o custo relativo de cada consulta em relação ao BATCH (todo o script executado). No exemplo acima, 30% do custo computacional foi gasto com “select * from sys.objects” e 70% com “select * from sys.tables”.

 

5 formas de gerar números sequenciais

Agora que já sabemos comparar a performance, vamos as metodologias para gerar números sequenciais. Em minha experiência, as 5 formas que testaremos a seguir, são as mais populares. Caso você não conheça alguma delas, no vídeo expliquei em detalhes como cada uma delas funciona.

 

Para compara-las lado a lado, desligue a demonstração do plano de execução e rode o script abaixo:

------------------------------------------------------------------
-- Performance na geração de números sequencias
------------------------------------------------------------------
-- Observação: Ativar o Execution Plan: Control + M

--------------------------------------------------
-- Loop tradicional
--------------------------------------------------
set nocount on
--/*
declare @sequencia int = 1
declare @retorno table (numero int)
declare @cronometro datetime = getdate()
while @sequencia <= 100000
	begin
		insert into @retorno values (@sequencia)
		set @sequencia = @sequencia + 1
	end
select * from @retorno
print 'WHILE: ' + convert(varchar, getdate() - @cronometro, 114)
go
--*/

--------------------------------------------------
-- CTE recursiva
--------------------------------------------------
declare @cronometro datetime = getdate()
;with cte as (
	select 1 as sequencia
	union all
	select sequencia + 1 as sequencia from cte where sequencia < 100000
)
select * from cte option (maxrecursion 0)
print 'CTE Recursiva: ' + convert(varchar, getdate() - @cronometro, 114)
go

--------------------------------------------------
-- CTEs Agrupadas
--------------------------------------------------
declare @cronometro datetime = getdate()
;with 
	cte_ancora (sequencia) as ( -- gera 10 números 1
			select 1 union all select 1 union all select 1 union all select 1 union all select 1 
			union all 
			select 1 union all select 1 union all select 1 union all select 1 union all select 1
	),
	cte_sequencia (sequencia) as ( -- usa o cross join para combinar todos os registros de um resultset com todos os registros de outro resultset
		select 1 from cte_ancora
		cross join cte_ancora as [10*10=100]
		cross join cte_ancora as [100*10=1K]
		cross join cte_ancora as [1000*10=10K]
		cross join cte_ancora as [10000*10=100K])
select 
	row_number() over (order by (select null)) linha
from cte_sequencia
print 'CTEs Agrupadas: ' + convert(varchar, getdate() - @cronometro, 114)
go



--------------------------------------------------
-- Row constructor
--------------------------------------------------
declare @cronometro datetime = getdate()
select 
	1 + ate9.n + 10*ate99.n + 100*ate999.n + 1000*ate9999.n + 10000*ate99999.n as sequencia
from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ate9(n), -- até 9
     (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ate99(n), -- até 99
     (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ate999(n), -- até 999
     (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ate9999(n), -- até 9999
	 (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ate99999(n) -- até 99999
order by 1
print 'Row Constructor: ' + convert(varchar, getdate() - @cronometro, 114)
go

--------------------------------------------------
-- Select em tabelas de sistema
--------------------------------------------------
declare @cronometro datetime = getdate()
select top (100000) 
	row_number() over (order by (select null)) sequencia
FROM sys.all_objects a
cross join sys.all_objects b
print 'Select sys.all_objects: ' + convert(varchar, getdate() - @cronometro, 114)
go

 

Resultados do cronometro

Após inúmeras execuções, percebi que os resultados dos 3 primeiros colocados variam. A quarta e quinta posição não. Em minha última execução ficou assim:

técnica	tempo
Select sys.all_objects	00:00:00:567
CTEs Agrupadas	00:00:00:570
Row Constructor	00:00:00:670
CTE Recursiva	00:00:00:843
WHILE	00:00:02:880

Resultados do custo relativo

Para comparar o custo computacional relativo, ligue o plano de execução (atalho CONTROL+M, como vimos acima). Comente o código que gera a lista de números via WHILE, pois para 100 mil interações ele provavelmente travará sua máquina.

Diferentemente do que ocorreu com os tempos de execução, o custo relativo se mostrou estável, apresentando constantemente o resultado abaixo:

técnica	Custo relativo
CTE Recursiva	0%
Select sys.all_objects	8%
CTEs Agrupadas	10%
Row Constructor	82%
WHILE	N/A

 

CONCLUSÃO

Achei peculiar o tempo de execução não bater com o custo relativo porque em geral são correlacionados.

Considerando as duas medidas, possivelmente o vencedor seja o “SELECT * from SYSOBJECTS”. O que você acha?

Abraço do seu amigo Josué 🙂