Discussão sobre a implementação de paginação de grande conjunto de resultados no ASP.NET

Em aplicações web, paginar um grande conjunto de resultados de banco de dados é um problema bem conhecido. Simplificando, você não deseja que todos os dados da consulta sejam exibidos em uma única página, portanto, a exibição com paginação é mais apropriada. Embora esta não seja uma tarefa simples no ASP tradicional, no ASP.NET, o controle DataGrid simplifica esse processo para apenas algumas linhas de código. Portanto, no asp.net, a paginação é muito simples, mas o evento de paginação padrão do DataGrid lerá todos os registros do banco de dados e os colocará no aplicativo da web asp.net. Quando você tem mais de um milhão de dados, isso causará sérios problemas de desempenho (se você não acredita nisso, você pode executar uma consulta em seu aplicativo e observar o consumo de memória do aspnet_wp.exe no gerenciador de tarefas Situação) É por isso é necessário customizar o comportamento da paginação, de forma a garantir que apenas os registros de dados exigidos pela página atual sejam obtidos.

Existem muitos artigos e postagens sobre esse problema na Internet, bem como algumas soluções maduras. Meu objetivo ao escrever este artigo não é mostrar a você um procedimento armazenado que resolverá todos os seus problemas, mas sim otimizar os métodos existentes e fornecer uma aplicação para testar para que você possa fazê-lo de acordo com suas necessidades de desenvolvimento.

Mas não estou muito satisfeito com os métodos actualmente introduzidos online. Primeiro, é usado o ADO tradicional, que obviamente foi escrito para ASP "antigo". Os métodos restantes são procedimentos armazenados do SQL Server, e alguns deles são inutilizáveis ​​devido a tempos de resposta muito lentos, como você pode ver nos resultados de desempenho no final do artigo, mas há alguns que chamaram minha atenção.

Generalização

Quero analisar cuidadosamente os três métodos comumente usados ​​atualmente, que são tabelas temporárias (TempTable), SQL dinâmico (DynamicSQL) e contagem de linhas (Rowcount). A seguir, prefiro chamar o segundo método de método Asc-Desc (ascendente-descendente). Não acho que SQL dinâmico seja um bom nome porque você também pode aplicar lógica SQL dinâmica em outro método. O problema comum com todos esses procedimentos armazenados é que você precisa estimar quais colunas serão classificadas, não apenas as colunas de chave primária (colunas PK), o que pode levar a uma série de problemas - para cada consulta, você precisa exibi-lo por meio de paginação, o que significa que para cada coluna de classificação diferente você deve ter muitas consultas de paginação diferentes, o que significa que você executa um procedimento armazenado diferente para cada coluna de classificação (independentemente de qual método de paginação é usado) ou você precisa coloque essa funcionalidade em um procedimento armazenado com a ajuda de SQL dinâmico. Esses dois métodos têm um pequeno impacto no desempenho, mas aumentam a capacidade de manutenção, especialmente se você precisar usar esse método para exibir consultas diferentes. Portanto, neste artigo tentarei usar SQL dinâmico para resumir todos os procedimentos armazenados, mas por alguns motivos, só podemos alcançar universalidade parcial, então você ainda terá que escrever procedimentos armazenados independentes para consultas complexas.

O segundo problema em permitir a classificação de todos os campos, incluindo colunas de chave primária, é que se essas colunas não forem indexadas corretamente, nenhum desses métodos ajudará. Em todos esses métodos, uma fonte de paginação deve ser classificada primeiro. Para tabelas de dados grandes, o custo da classificação usando colunas que não são de índice é insignificante. Neste caso, todos os procedimentos armazenados não podem ser utilizados em situações reais devido ao longo tempo de resposta. (O tempo correspondente varia de alguns segundos a alguns minutos, dependendo do tamanho da tabela e do primeiro registro a ser obtido). Os índices em outras colunas podem introduzir problemas adicionais de desempenho indesejáveis, por exemplo, podem ficar muito lentos se você importar muitos dados todos os dias.

Tabela temporária

Primeiro, falarei sobre o método da tabela temporária. Esta é uma solução amplamente recomendada que encontrei diversas vezes em meus projetos. Vamos dar uma olhada na essência deste método:

CREATE TABLE #Temp(
ID int IDENTIDADE CHAVE PRIMÁRIA,
PK /*vai aquiPKtype*/
)

INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn

SELECT FROM Table JOIN # Temp temp ON Table.PK = temp .PK ORDER BY temp .ID WHERE ID > @StartRow AND ID< @EndRow

copiando todas as linhas para temporárias no tabela, podemos otimizar ainda mais a consulta (SELECT TOP EndRow...), mas a chave é o pior cenário possível - uma tabela contendo 1 milhão de registros gerará uma tabela temporária com 1 milhão de registros.

ascendente-descendente

em meu teste.

Este método usa a classificação padrão na subconsulta e a classificação reversa na consulta principal.

DECLARE @temp TABELA(
PK /* PKType */
NÃO NULO PRIMÁRIO
)

INSERIR EM @temp SELECIONE TOP @PageSize PK FROM
(
SELECIONE TOPO(@StartRow + @PageSize)
PC,
SortColumn /* Se a coluna de classificação for diferente do PK,SortColumn deve
ser buscado também, caso contrário, apenas o PK será necessário
*/

ORDER POR SortColumn
/*
ordem padrão – normalmenteASC
*/
)

ORDENAR POR SortColumn
/*
ordem padrão invertida – normalmenteDESC
*/

SELECT FROM Tabela JOIN @Temp temp ON Tabela .PK= temp .PK
ORDENAR POR SortColumn
/*
ordem padrão
*/

contagem de linhas

depende da expressão SET ROWCOUNT em SQL, para que linhas desnecessárias possam ser ignoradas e os registros de linha necessários possam ser obtidos:

DECLARE @Sort /* o tipo da coluna de classificação */

SET ROWCOUNT @ StartRow

SELECT @Sort=SortColumn FROM Tabela ORDER BY SortColumn
SET ROWCOUNT @PageSize

SELECT FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn

de subconsulta

que considerei e suas fontes são diferentes. O primeiro é o conhecido método de consulta tripla ou autoconsulta. Neste artigo, também uso uma lógica comum semelhante que abrange todos os outros procedimentos armazenados. A ideia aqui é conectar todo o processo, fiz algumas reduções no código original já que recordcount não é necessário em meus testes)

SELECT FROM Table WHERE PK IN(
SELECIONE TOP @PageSize PK DA tabela ONDE PK NÃO ESTÁ
(
SELECIONE TOP @StartRow PK DA Tabela ORDER BY SortColumn)
ORDENAR POR SortColumn)
ORDER BY SortColumn

Cursor

Enquanto olhava para o grupo de discussão do Google, encontrei o último método. Este método usa um cursor dinâmico do lado do servidor. Muitas pessoas tentam evitar o uso de cursores porque são irrelevantes e ineficientes devido à sua ordem. Mas, olhando para trás, a paginação é na verdade uma tarefa ordenada, não importa o método usado, você deve retorná-lo à linha inicial. No método anterior, você primeiro seleciona todas as linhas antes do início da gravação, adiciona as linhas necessárias para registrar e, em seguida, exclui todas as linhas anteriores. Os cursores dinâmicos possuem uma opção FETCH RELATIVE que realiza saltos mágicos. A lógica básica é a seguinte:

DECLARE @PK /* PKType */

DECLARE @tblPK

TABLE(
PK /*PKType*/ CHAVE PRIMÁRIA NÃO NULA
)

DECLARE PagingCursor CURSOR DYNAMICREAD_ONLY FOR
SELECIONE @PK DA Tabela ORDER BY SortColumn

OPEN PagingCursor
BUSQUE RELATIVO @StartRow DO PagingCursor PARA @PK

ENQUANTO @PageSize>0 E @@FETCH_STATUS =0
COMEÇAR
INSERIR VALORES @tblPK(PK)(@PK)
BUSCAR O PRÓXIMO DO PagingCursor PARA @PK
DEFINIR @PageSize = @PageSize - 1
FIM

FECHAR
Cursor de paginação
DESALOCAR
PagingCursor

SELECT FROM Tabela JOIN @tblPK temp ON Tabela .PK= temp .PK

Generalização de consultas complexas

em ORDER BY SortColumn

Mencionei antes que todos os procedimentos armazenados usam SQL dinâmico para obter generalização, portanto, em teoria, eles podem usar qualquer tipo de consulta complexa. Abaixo está um exemplo de uma consulta complexa baseada no banco de dados Northwind.

SELECT Clientes.ContactName AS Cliente, Clientes.Endereço + ' , ' + Clientes.Cidade + ', '+ Clientes.País
Endereço AS, SOMA([OrderDetails].UnitPrice*[OrderDetails] .Quantidade)
AS [Totalmoneyspent]

FROM Clientes

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

INNER JOIN [ OrderDetails ] ON Orders.OrderID = [ OrderDetails].OrderID

WHERE Customers.Country <> 'EUA' AND Customers.Country <> 'México '

GRUPO POR Clientes.NomeContato,Clientes.Endereço,Clientes.Cidade, Clientes.País
HAVING(SUM([OrderDetails].UnitPrice * [ OrderDetails ] .Quantity)) > 1000

ORDER BY Customer DESC ,Address DESC

retorna a chamada de armazenamento de paginação da segunda página da seguinte forma:

EXEC ProcedureName

/*Tables */

'
Clientes
INNER JOIN Pedidos ON Customers.CustomerID=Orders.CustomerID
INNER JOIN [OrderDetails] ON Orders.OrderID=[OrderDetails].OrderID
'
,
/* PK */

'
Clientes.CustomerID
'
,
/* ORDERBY */

'
Clientes.ContactName DESC,Clientes.EndereçoDESC
'
,

/*Número da página */

2
,

/*TamanhoPágina */

10
,

/*Campos */

'
Clientes. Nome do contato AS Cliente,
Clientes.Endereço+'' , '' +Clientes.Cidade+ '' , '' +Clientes.País ASEndereço, SUM([Detalhes do pedido].PreçoUnit*[Detalhes do pedido].Quantidade)AS[Totaldinheiro gasto]
'
,

/*Filtrar */

'
Customers.Country<>'' EUA '' ANDCustomers.Country<> '' México ''' ,

/*GroupBy */

'
Clientes.CustomerID,Clientes.ContactName,Clientes.Endereço,
Clientes.Cidade,Clientes.País
HAVING(SUM([Detalhes do pedido].PreçoUnit*[Detalhes do pedido].Quantidade))>1000
'

É importante notar que você usou aliases na instrução ORDER BY na consulta original, mas é melhor não fazer isso em um procedimento armazenado paginado, porque pular linhas antes de começar a registrar é demorado. Na verdade, existem muitos métodos de implementação, mas o princípio não é incluir todos os campos no início, mas apenas incluir a coluna de chave primária (equivalente à coluna de classificação no método RowCount), o que pode acelerar a conclusão do tarefa. Somente na página de solicitação todos os campos obrigatórios são obtidos. Além disso, não há aliases de campo na consulta final e, em consultas para pular linha, as colunas de índice devem ser usadas antecipadamente.

Há outro problema com o procedimento armazenado RowCount. Para obter generalização, apenas uma coluna é permitida na instrução ORDER BY. Isso também é um problema com o método ascendente-descendente e o método cursor, embora eles possam classificar várias colunas. deve-se garantir que haja apenas um campo na chave primária. Acho que isso poderia ser resolvido com SQL mais dinâmico, mas na minha opinião não vale a pena. Embora tais situações sejam possíveis, elas não acontecem com muita frequência. Normalmente você pode usar os princípios acima para paginar de forma independente os procedimentos armazenados.

Teste de Desempenho

Nos testes, usei quatro métodos, se você tiver um método melhor, estou interessado em saber. De qualquer forma, preciso comparar esses métodos e avaliar seu desempenho. Em primeiro lugar, minha primeira ideia é escrever um aplicativo de teste asp.net contendo um DataGrid de paginação e, em seguida, testar os resultados da página. Obviamente, isso não reflete o verdadeiro tempo de resposta do procedimento armazenado, portanto, um aplicativo de console é mais adequado. Também incluí uma aplicação web, mas não para teste de desempenho, mas como um exemplo de paginação personalizada do DataGrid e procedimentos armazenados trabalhando juntos.

No teste, usei uma grande tabela de dados gerada automaticamente e inseri cerca de 500.000 dados. Se você não tiver essa tabela para experimentar, clique aqui para baixar um design de tabela e um script de procedimento armazenado para gerar dados. Em vez de usar uma coluna de chave primária com incremento automático, usei um identificador exclusivo para identificar o registro. Se eu usar o script mencionado acima, você pode considerar adicionar uma coluna de incremento automático após gerar a tabela. Os dados de incremento automático serão classificados numericamente com base na chave primária. com classificação de chave primária para obter os dados da página atual.

Para implementar o teste de desempenho, chamei um procedimento armazenado específico várias vezes por meio de um loop e calculei o tempo médio de resposta. Considerando razões de armazenamento em cache, para modelar com mais precisão a situação real - o tempo que leva para a mesma página obter dados para múltiplas chamadas a um procedimento armazenado geralmente não é adequado para avaliação. o número da página solicitada para cada chamada deve ser aleatório. Claro, devemos assumir que o número de páginas é fixo, 10-20 páginas, e dados com números de páginas diferentes podem ser obtidos muitas vezes, mas aleatoriamente.

Uma coisa que podemos notar facilmente é que o tempo de resposta é determinado pela distância dos dados da página a serem obtidos em relação à posição inicial do conjunto de resultados. Quanto mais longe da posição inicial do conjunto de resultados, mais registros estarão. ignorado. Esse também é o motivo pelo qual não incluo os 20 primeiros em minha sequência aleatória.

*

1.000, então cada página é buscada quase 1.000 vezes (com certeza haverá um desvio devido a motivos aleatórios)

. são os resultados do meu teste:






Conclusão

Os testes foram realizados na ordem do melhor para o pior desempenho - contagem de linhas, cursor, ascendente-descendente, subconsulta. Uma coisa interessante é que normalmente as pessoas raramente visitam páginas após as primeiras cinco páginas, então o método de subconsulta pode atender às suas necessidades neste caso, dependendo do tamanho do seu conjunto de resultados e da distância que ele está para prever a frequência de ocorrências da página. , provavelmente você também usará uma combinação desses métodos. Se fosse eu, preferiria o método de contagem de linhas de qualquer forma, funciona muito bem, mesmo para a primeira página, o "qualquer caso" aqui representa alguns casos onde a generalização é difícil, neste caso, eu usaria um cursor. (Eu provavelmente usaria o método de subconsulta para os dois primeiros e o método do cursor depois disso)