Dica Rápida: Paginando no MSSQL considerando escala

#sql #database #braziliandevs

Olá!

Este post é uma breve continuação do Dica Rápida: Paginando no MSSQL com Offset e Fetch.

Antes de começar, gostaria de agradecer aos colegas Rafael Ponte e Renato Todorov que fizeram considerações sobre alto volume de dados na Dica Rápida anterior, dando origem a esta continuação! Obrigado, pessoal!

Vamos lá?

Muito embora, para cenários mais simples, com baixo volume de dados, o uso de Offset e Fetch seja suficiente, quando precisamos trabalhar com grandes volumes, a história muda.

Explico.

No último post disse que a instrução Offset vai pular a quantidade de linhas informada. Certo? Pois é! Mas para que o banco saiba quantas e quais linhas pular, ele precisa percorrê-las primeiro. Ou seja, o banco vai percorrer todas as linhas que respeitem o critério BirthDate e, em seguida, vai usar as instruções Offset e Fetch para decidir quais linhas retornar.

E é aí que mora o problema porque, quanto mais linhas sua tabela tiver, e quanto maior for o Offset informado, maior será o número de linhas que deverá ser percorrido antes de retornar a quantidade solicitada pelo uso do Fetch! E este é um baita prejuízo para a performance se for preciso paginar milhares de registros.

Mas, então, como resolver isso? Por incrível que pareça, é fácil! A solução se chama keyset pagination (ou cursor pagination)!

Em vez de informarmos ao banco em qual página estamos, informaremos o último Id que lemos, retornaremos apenas os registros de Id superior, e assim eliminamos o esforço do Offset.

O código anterior seria convertido para o seguinte:

SELECT TOP (@PageSize)
    Id,
    FirstName, 
    LastName
FROM 
    Customer
WHERE 
    Id > @LastId -- Ou Id < @LastId se a intenção for retroceder uma página
AND
    BirthDate >= '2000-01-01'
ORDER BY
    Id

Aparentemente não mudou muita coisa. Não é? Mas, do ponto de vista do banco, essa simples mudança representa um enorme alívio porque, desta forma, não precisamos mais instruir o banco a subdividir a consulta com todos os resultados que atendam ao critério BirthDate informado, pois a informação do TOP no SELECT, e do último Id conhecido na cláusua WHERE, são o suficiente para instruir o banco sobre a partir de qual linha ele deve começar, e até quantas linhas ele precisa percorrer.

Nota: A coluna Id pode combinada com, ou substituída por, qualquer coluna indexada.

Legal. Né?

Gostou? Me deixe saber pelos indicadores. Dúvidas? Me envie pelos comentários que responderei assim que puder.

Até a próxima!

Referências:

Please Don't Use OFFSET and LIMIT For Your Pagination

Top comments (7)

Lucas Silvério • Dec 1 '20

Oi Willian! Isso faz sentido se o Id for um Guid?

William Santos • Dec 1 '20 • Edited

Oi, Lucas! Tudo bom?

Não vejo sentido em usar o GUID como fonte de ordenação, por conta da forma como o SQL Server o ordena. Entendo que faça mais sentido neste caso usar ROW_NUMBER sobre o GUID e fazer a paginação a partir dele.

Seria algo assim:

WITH PagedCustomers AS (
SELECT
ROW_NUMBER() OVER(ORDER BY Id) RowNumber,
Id,
FirstName,
LastName
FROM
Customers
) SELECT
Top (@PageSize) Id,
FirstName,
LastName
FROM
PagedCustomers
WHERE
RowNumber > @LastRowNumber

Lucas Silvério • Dec 1 '20

Boa! Eu acabei chegando nessa conclusão tb, mas nunca tinha usado o WITH😶.
Tem diferença no desempenho nas abordagens abaixo:

-- SUBQUERY
select * from (
select row_number() over (order by CreatedAt desc) as row#, Id,
CreatedAt,
Name,
Email
from [User]) as result
where row# >= @LastRowNumber and row# <= @PageSize;

-- WITH
with pagedUsers as (
select row_number() over (order by CreatedAt desc) as row#, Id,
CreatedAt,
Name,
Email
from [User])
select top @PageSize * from pagedUsers
where row# >= @LastRowNumber;

William Santos • Dec 1 '20

Entendo que não haja diferença no desempenho, não. Optei pelo WITH por fazer parte da sintaxe CTE (Common Table Expressions), que entendo ser mais expressiva.

Na subquery você pode usar também o TOP para obter o número de registros igual a @PageSize, seguindo o exemplo do WITH. E, no caso da cláusula WHERE da subuqery, colocar row# >= @LastRowNumber and row# <= @PageSize vai deixar de funcionar na segunda página! rs