Dica de performance: Eliminando cursor com Window Functions + top 1 with ties

 Olá  pessoal, tudo bem? Hoje gostaria de dar uma dica bem legal!! Muitos de vocês já devem saber que cursor na maioria das vezes é um grande vilão de performance no SQL-SERVER, isso porque o SQL não foi feito para processamento linha a linha e sim para um processamento em lote, vamos pensar SET-BASED! Hoje estarei mostrando para vocês um caso muito comum do dia dia.

Vamos ao cenários:

Precisamos pegar a última compra do cliente! Simples assim.

Temos a tabela  cliente_tb, que guarda os dados do cliente e a tabela   venda_tb que guarda os dados da venda, basicamente um cliente pode fazer N  compras e nós precisamos pegar a ultima.

Segue cursor que vejo na maioria dos casos:

drop table if exists #aux

create table #aux (

id_cliente int 

,nome           varchar(250)

,dt_venda datetime 

)

DECLARE @id_cliente int

-- Cursor para percorrer os registros

DECLARE cursor1 CURSOR FOR

select id_cliente from cliente_tb

--Abrindo Cursor

OPEN cursor1 

-- Lendo a próxima linha

FETCH NEXT FROM cursor1 INTO @id_cliente

-- Percorrendo linhas do cursor (enquanto houverem)

WHILE @@FETCH_STATUS = 0

BEGIN

-- Executando as rotinas desejadas manipulando o registro

insert into #aux 

select top 1 

 b.id_cliente

,b.nome

,a.dt_venda

from  venda_tb a 

inner join cliente_tb b

on a.id_cliente = b.id_cliente

where a.id_cliente =  @id_cliente

order by dt_venda desc 

-- Lendo a próxima linha

FETCH NEXT FROM cursor1 INTO @id_cliente

END

CLOSE cursor1

DEALLOCATE cursor1

select * from #aux

o resultado:

vamos verificar o profiler:

Para melhor visualização:

CPU: 77360

READS:  10027875

TIME: 00:01:22

Agora vamos remover esse cursor malvado:

select top 1 with ties a.*,b.dt_venda    from cliente_tb a 

inner join  venda_tb b

on a.id_cliente = b.id_cliente

order by ROW_NUMBER() over (partition by   b.id_cliente order by  dt_venda desc )

option(recompile,maxdop 1 )

Vamos ao profiler:

Para melhor visualização:

CPU: 844

READS:  3415

TIME: 00:00:01

Bastante diferença, não?

Podemos também utilizar o cross apply:

select *    from cliente_tb a 

cross apply(

select top 1 dt_venda from venda_tb b

where a.id_cliente = b.id_cliente

order by dt_venda desc

) as d

option(recompile,maxdop 1 )

vamos ao profiler:

Para melhor visualização:

CPU: 2844

READS:  3317634

TIME: 00:00:03

Nesse caso a solução com Window Functions ficou melhor, mas nem sempre podemos usar ela, pois se quiséssemos pegar  as duas últimas vendas não poderíamos.

Agora tentarei explicar como funciona a lógica da solução com with ties:

Bem, basicamente eu particionei a tabela por cliente e fiz uma ordenação por dt_venda desc, com isso a ultima venda de cada cliente vai ter o id 1, como o with ties traz os empates, ele vai trazer todas as vendas com o novo id gerado = 1 

Query utilizada para o exemplo:

select 

a.nome

,dt_venda

,aux =ROW_NUMBER() over (partition by   b.id_cliente order by  dt_venda desc )  

from cliente_tb a 

inner join  venda_tb b

on a.id_cliente = b.id_cliente

order by 1, 3

Bom pessoal, por hoje é assim, qualquer dúvida estou à disposição!! Tem outra solução? Manda ai, vamos debater!

Grande abraço!