Páginas

quarta-feira, 13 de junho de 2012

Paginação de registros no SQL Server

Hoje vou comentar uma solução para paginar dados no SQL Server. A paginação é útil quando precisamos retornar uma grande quantidade de informações pouco à pouco.

Vamos supor que tenhamos que trafegar 100.000 registros através de uma chamada de web service. Dependendo da rede e do tamanho dos dados, podemos ter problemas se tentarmos passar todos esses dados de uma vez só (problemas do tipo cair a rede no meio da transferência e perdermos todo o trabalho). Ao invés disso, podemos tentar passar 5.000 em 20 vezes, ou seja, em 20 páginas. Isso também é válido quando precisamos apresentar muita informações na tela do usuário, e ao invés de trazer todos os registros, trazemos eles pouco à pouco de forma que permita a análise da pessoa.


Nas situações acima, poderíamos carregar todos os registros em memória e nós mesmos fazermos a paginação, só que isso é ruim visto que vamos alocar todas as informações na memória sendo que só iremos utilizar uma pequena fração dela. Para resolver isso, podemos aplicar uma paginação no nível onde as informações estão armazenadas: o banco de dados.

Isso pode ser feito com uso da função ROW_NUMBER, disponível desde a versão 2005 do SQL Server. Vamos imaginar uma tabela chamada cidades, com apenas uma coluna chamada nome, como na imagem abaixo.


A paginação é feita usando o exemplo de código abaixo, onde estamos especificando o tamanho da página (quantidade de registros que serão retornados) e o número da página.

DECLARE @TamanhoPagina INT;
DECLARE @NumeroPagina INT;

SET @TamanhoPagina = 10;
SET @NumeroPagina = 25;

WITH Paginado AS (
 SELECT ROW_NUMBER() OVER(ORDER BY nome) AS linha, nome
  FROM cidades WITH (NOLOCK)
 )
SELECT TOP (@TamanhoPagina) linha, nome
FROM Paginado p
WHERE linha > @TamanhoPagina * (@NumeroPagina - 1);

Veja que estamos criando um conjunto de dados temporário chamado Paginado, a partir do SELECT usando a função ROW_NUMBER() de forma que ela retorne o número da linha de cada registro (é importante notar também que essa função exige o uso da instrução OVER, onde especificamos a ordenação a ser usada).

Depois disso, fazemos um simples SELECT sobre esse conjunto de dados temporário, tomando cuidado para retornar apenas a quantidade de acordo com o tamanho da página (instrução TOP). E para finalizar, no WHERE deste último SELECT especificamos qual página será retornada. Por exemplo, supondo que o tamanho da página é 10, se quisermos a página 1 (registros de 1 até 10), o resultado da expressão @TamanhoPagina * (@NumeroPagina - 1) retornará 0, então iremos trazer todos os registros de índice maior que zero (lembre que estamos limitando a quantidade através do TOP). Se quisermos a página 2 (registros de 11 até 20), então a expressão @TamanhoPagina * (@NumeroPagina - 1) retornará 10, e iremos trazer os registros de índice maior que 10, e assim por diante.


Com isso, implementamos uma paginação no nível do banco de dados, sem necessidade de processá-la em memória ou mesmo trazer todos os registros de uma vez ao usuário.

[]'s

4 comentários:

  1. Muito obrigado camarada, eu pesquisei muito aqui nesse Google por uma solução e a maioria dos caras faz a paginação pelo lado cliente(PHP), ou seja, os caras carregam a tabela inteira antes de fazer a paginação, no meu caso não dá porque a tabela tem mais de 10 milhões de registros /usuário(rastreamento de veículos), essa sua aí foi a solução valeu. Abc.

    ResponderExcluir
  2. Legal,

    Agora como mostrar para o usuário o total de registros?

    ResponderExcluir
    Respostas
    1. Olá. Neste caso, você obrigatoriamente tem que retornar na sua query a quantidade total de registros. Eu tive dúvida na maneira de melhor desempenho para fazer isso, e perguntei no Stackoverflow há algum tempo. Aqui está o link

      http://stackoverflow.com/questions/21537511/sql-server-query-with-pagination-and-count

      []'s

      Excluir