Compartilhar via

Paginando uma consulta com SQL Server

  • Artigo

Introdução

Em alguns momentos é necessário otimizar os dados retornados em uma consulta para o usuário, removendo colunas desnecessárias de uma instrução SELECT e incluindo condições na sua cláusula WHERE para que o usuário possa obter apenas os dados que realmente precisa visualizar.

Em situações críticas de pequenas empresas, com limitações de hardware e/ou software, além de locais onde o acesso à Internet são limitados também temos a necessidade de reduzir o conteúdo de tráfego de rede.

Estes são fatores que influenciam na necessidade de tornar a exibição de dados simples e com um pequeno processamento na apresentação dos dados para o usuário.
  
Um dos recursos que podem ser utilizados para atender estes requisitos é a Paginação no SQL Server.

O maior objetivo deste artigo é mostrar como um simples script T-SQL pode ser útil desde que ele seja bem implementado.

Este artigo faz parte da série Paginação de Dados. 

Construindo o ambiente para teste

Para que possamos reproduzir a utilização da paginação em consultas SQL e suas vantagens para todo ambiente onde os dados são trafegados, vamos criar uma tabela para demonstração com um grande volume de registros (um milhão de linhas) para que possamos avaliar melhor a performance em diferentes formas de consulta de dados.

No final deste artigo vamos avaliar a performance de uma consulta ordenada nas seguintes condições:

1 -  Consulta sem paginação no SQL Server (normalmente utilizada por programadores, que deixam a tarefa de "paginar" os dados para um componente "GridView" ou outro semelhante ;
2 -  Consulta com paginação, utilizando ROW_NUMBER (modelo tradicional de paginação em T-SQL);
3 -  Consulta com paginação, utilizando as cláusulas OFFSET e FETCH (novo modelo, usado a partir do SQL Server 2012);

Então vamos criar a tabela com uma amostra de dados !

--CRIANDO A TABELA PARA DEMONSTRAÇÃO
CREATE TABLE dbo.TB_EXAMPLE(
  ID_EXAMPLE int NOT NULL IDENTITY(1,1),
  NM_EXAMPLE varchar(25) NOT NULL,
  DT_CREATE datetime NULL DEFAULT(GETDATE())
);
GO

--INSERINDO 1.000.000 REGISTROS DIFERENTES NA TABELA
INSERT INTO TB_EXAMPLE (NM_EXAMPLE) VALUES ('Item de paginação ' + CONVERT(VARCHAR,ISNULL(@@IDENTITY, 0)))
GO 1000000

--CONSULTANDO OS 1.000.000 REGISTROS
SELECT * FROM TB_EXAMPLE
GO

Veja este script SQL executado na imagem abaixo.

Paginando os registros com limite de linhas

Para que possamos compreender o conceito de paginação em T-SQL, com ROW_NUMBER e com OFFSET e FETCH, vamos criar uma estrutura padrão no desenvolvimento deste script.

Criamos duas variáveis para facilitar a manipulação de dados: 

@PageNumber - Define o número da página que desejamos visualizar

@RowspPage - Define o número de registros de cada página

Com estas variáveis definidas, vamos começar com a função ROW_NUMBER, ordenando os dados pelo campo ID_EXAMPLE.

Veja o script abaixo, onde definimos para ser exibida a "página 2" com "5 registros por página":

--VISUALIZANDO OS REGISTROS DA PÁGINA 2 COM 5 LINHAS POR "PÁGINA"
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 5

SELECT * FROM (
             SELECT ROW_NUMBER() OVER(ORDER BY ID_EXAMPLE) AS NUMBER,
                    ID_EXAMPLE, NM_EXAMPLE, DT_CREATE FROM TB_EXAMPLE
               ) AS TBL
WHERE NUMBER BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage)
ORDER BY ID_EXAMPLE

Veja este script SQL executado na imagem abaixo.
 

Podemos ver que uma simples operação matemática define o registro inicial e final para cada página.

Como este exemplo utiliza a função ROW_NUMBER, é importante saber que o primeiro registro é a posição "um".

Neste caso como definimos a variável @PageNumber com valor 2 e a variável @RowspPage com valor 5. Então temos na página "2":

  • o primeiro registro com o valor do alias "NUMBER" igual a 6
  • o último registro desta página igual ao "NUMBER" 10

Paginação dentro do SQL Server 2012

Com o SQL Server 2012, um novo recurso foi atribuído na cláusula ORDER BY facilitando e otimizando a obtenção de um grupo de dados, isto torna o trabalho de paginação de registros mais simples para quem escreve em T-SQL como também para todo o Plano de Execução interno do SQL Server.

Segue abaixo o script T-SQL com a mesma lógica utilizada no exemplo anterior.

--CREATING A PAGING WITH OFFSET and FETCH clauses IN "SQL SERVER 2012"
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 10 

SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE
FROM TB_EXAMPLE
ORDER BY ID_EXAMPLE
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
FETCH NEXT @RowspPage ROWS ONLY;

Veja este script SQL executado na imagem abaixo.

Podemos ver que esta operação matemática é tão simples quanto a anterior, porém o processo de controle de dados é diferenciado, devido aos novos recursos do SQL Server 2012 para a cláusula ORDER BY.

Como este exemplo utiliza a função OFFSET é importante saber que o primeiro registro é a posição "zero".

Neste caso como definimos a variável @PageNumber com valor 2 e a variável @RowspPage com valor 10. Então temos nesta página:

  • o primeiro registro na posição 11
  • o último registro na posição 20

Como não utilizamos um valor explícito para definir a paginação, a consulta desta tabela torna o trabalho de paginação mais simples e enxuto, mas será que esta nova consulta utilizando **OFFSET **é mais eficiente performaticamente do que a consulta utilizando **ROW_NUMBER **? 

Então, vamos analisar a performance.

Comparando as consultas ROW_NUMBER x OFFSET/FETCH

Podemos ver que a paginação de uma instrução SELECT é simples e a otimização de processamento de dados pelo SQL tem um ganho considerável. Nas situações indicadas como críticas, o recurso de paginação de dados pode ajudar a tornar o trabalho do usuário muito mais simples e rápido.

Agora vamos avaliar a performance de uma consulta ordenada nas seguintes condições:

**1 - ** Consulta simples, sem paginação no SQL Server (a paginação é responsabilidade de uma Aplicação ASP.Net, WinForms ou outras).
2 -  Consulta com paginação, utilizando ROW_NUMBER (modelo tradicional de paginação em T-SQL);
3 -  Consulta com paginação, utilizando as cláusulas OFFSET e FETCH (novo modelo, usado a partir do SQL Server 2012);

-- 1 - SIMPLE QUERY
SELECT * FROM TB_EXAMPLE ORDER BY ID_EXAMPLE
GO

Veja o "script 1" executado na imagem abaixo.

Podemos ver que na consulta simples, sem paginação, todos os dados são utilizados pela consulta e não apenas os dados que o usuário necessita.

Isto é uma enorme perda de recurso: processamento, I/O em disco e memória consumida pela instância no servidor SQL.

Além disto, o tempo de apresentação dos dados para o usuário é muito maior porque é necessário preparar o conteúdo para paginar os dados pela interface do usuário (UI). Em grandes volumes de dados e com a concorrência de uso destes dados, isto pode se tornar um transtorno.

Vamos ver o custo do Plano de Execução desta consulta dentro do SQL Server.

Veja o Plano de Execução do "script 1" na imagem abaixo.

O Plano de Execução mostra o custo em paralelo devido à falta de um índice na tabela numa estrutura simplificada.

**Veja as Estatísticas do Cliente do **"script 1" na imagem abaixo.

Para analisar o desempenho de cada consulta, vamos obter 3 amostras de execução e comparar apenas a Estatística de Tempo ("Client processing time" e "Total execution time") com melhor performance.

No caso da primeira consulta (script 1), que é um SELECT simples, o melhor tempo de execução possui:

- Client processing time (ms) = 1728
- Total execution time (ms)    = 1848

O tráfego de rede é pequeno para o cliente mas muito elevado para o servidor:

- Bytes sent from client         = 310
**- **Bytes **received from server = 39983840
**
Além deste tempo excessivo, será necessário criar um processo de paginação assim que os dados forem recebidos pela aplicação Client/Server ou Web.

Desta forma, o tempo de processamento dos dados e apresentação do conteúdo para o usuário será ainda maior.

Vamos executar agora o script T-SQL utilizando ROW_NUMBER para a paginação de dados. Lembrando que este modelo de paginação em consultas funciona a partir do SQL Server 2000 até as atuais versões do SQL Server 2012.

-- 2 - QUERY USING "ROW_NUMBER"
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 10 

SELECT * FROM (
             SELECT ROW_NUMBER() OVER(ORDER BY ID_EXAMPLE) AS Numero,
                    ID_EXAMPLE, NM_EXAMPLE , DT_CREATE FROM TB_EXAMPLE
               ) AS TBL
WHERE Numero BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage)
ORDER BY ID_EXAMPLE
GO

Veja o "script 2" executado na imagem abaixo.

Podemos ver que na consulta utilizando ROW_NUMBER, a paginação fornece apenas os dados utilizados pela consulta que o usuário necessita.

Isto torna a consulta dos dados mais objetiva, reduzindo: processamento, I/O em disco e memória consumida pela instância no servidor SQL.

O tempo de apresentação dos dados para o usuário também diminui porque o conteúdo da consulta já está pronto para exibição dos dados pela interface do usuário (UI).

Vamos ver o custo do Plano de Execução desta consulta dentro do SQL Server.

**Veja o Plano de Execução do **"script 2" na imagem abaixo (clique na imagem para ampliar)

Neste Plano de Execução, podemos ver uma estrutura mais complexa onde o SQL Server organiza a obtenção apenas dos dados solicitados na consulta.

Apesar deste processamento ser muito maior do que o exibido no Plano de Execução do "script 1", o tempo de execução será menor devido a quantidade de dados que será retornado ao usuário na aplicação.

Vamos ver as 3 amostras de execução do "script 2", para que possamos comparar este tempo de execução.

**Veja as Estatísticas do Cliente do **"script 2" na imagem abaixo.

No caso da segunda consulta (script 2 - com ROW_NUMBER), o melhor tempo de execução possui:

- Client processing time (ms) = 4
**- Total execution time (ms)    = 156
**
O tráfego de rede se torna maior para o cliente mas é reduzido consideravelmente para o servidor:

- Bytes sent from client         = 988
**- **Bytes received from server = 35352

Além deste tempo reduzido, com pouco mais de 10 vezes o tempo da consulta no "script 1", podemos ver facilmente que a redução de linhas retornadas pela consulta também refletem na diminuição da quantidade de Bytes enviados do Servidor para o Cliente.

Desta forma, o tempo de processamento dos dados e apresentação do conteúdo para o usuário diminui consideravelmente, além de reduzir o tráfego de rede entre o servidor e a Aplicação Cliente.

Vamos executar agora o script T-SQL utilizando OFFSET/FETCH para a paginação de dados. Lembrando que este modelo de paginação em consultas funciona apenas com as atuais versões do SQL Server 2012.

-- 3 - QUERY USING "OFFSET" AND "FETCH NEXT" (SQL SERVER 2012)
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 10

SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE
FROM TB_EXAMPLE
ORDER BY ID_EXAMPLE
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
FETCH NEXT @RowspPage ROWS ONLY
GO

**Veja o "script 3" executado na imagem abaixo.
**

Podemos ver que na consulta utilizando OFFSET/FETCH, a paginação fornece apenas os dados utilizados pela consulta que o usuário necessita, semelhante ao processo do "script 2" (que utiliza ROW_NUMBER).

A consulta dos dados também é reduzida, mas seu processo é mais simplificado.

Vamos ver o custo do Plano de Execução desta consulta dentro do SQL Server.

**Veja o Plano de Execução do **"script 2" na imagem abaixo.

Neste Plano de Execução, podemos ver uma estrutura mais simples onde o SQL Server organiza a obtenção apenas dos dados solicitados na consulta. 

Apesar deste processamento ser semelhante ao exibido no Plano de Execução do "script 1", o tempo de execução será menor devido à quantidade de dados que será retornado ao usuário na aplicação.

Vamos ver as 3 amostras de execução do "script 3", para que possamos comparar este tempo de execução.

**Veja as Estatísticas do Cliente do **"script 3" na imagem abaixo.

No caso da terceira consulta (script 3 - com OFFSET/FETCH), o melhor tempo de execução possui:

- Client processing time (ms) = 4
- Total execution time (ms)    = 130

O tráfego de rede se torna maior para o cliente mas é reduzido consideravelmente para o servidor:

- Bytes sent from client         = 804
**- **Bytes **received from server = 21563
**
Além deste tempo reduzido, um pouco melhor do que o tempo da consulta no "script 2" (ROW_NUMBER), podemos ver a redução nos Bytes enviados e recebidos, que consequentemente impacta na redução do tempo de execução de todo o processo. 

Conclusão

Podemos ver que a paginação de uma instrução SELECT é simples de ser criada e pode ser considerada um excelente recursos para grandes quantidades de dados.

A otimização de processamento de dados pelo T-SQL tem um ganho considerável tanto utilizando o ROW_NUMBER como na utilização de OFFSET/FETCH, porém levando em consideração a análise destes dados podemos afirmar que a consulta do "script 3"(que utiliza OFFSET/FETCH) pode ser a melhor opção em grandes volumes de registros, desde que você utilize um servidor SQL Server 2012.

Referências

Veja Também

Outros Idiomas