Primeiro, fornecemos vários métodos principais de paginação e instruções básicas e, em seguida, apresentamos diretamente a conclusão. Os leitores interessados podem dar uma olhada nos dados a seguir para vários métodos de paginação de procedimento armazenado comumente usados.
Método TopN
selecione Top(@PageSize) em TableName onde ID Not IN
(Selecione o ID superior (@PageIndex-1)*@PageSize) no nome da tabela, onde .... ordenar por ... )
onde .... encomendar por ...
mesa temporária
Copie o código do código da seguinte forma:
declare tabela @indextable(id int identidade(1,1),nid int,PostUserName nvarchar(50))
declare @PageLowerBound int
declare @PageUpperBound int
definir @PageLowerBound=(@pageindex-1)*@pagesize--limite inferior
definir @PageUpperBound=@PageLowerBound+@pagesize--limite superior
definir contagem de linhas @PageUpperBound
insira em @indextable(nid,PostUserName) selecione ReplyID,PostUserName da ordem TableName por ......
selecione * em TableName p,@indextable t onde p.ID=t.nid
e t.id>@PageLowerBound e t.id<=@PageUpperBound ordem por t.id
CTE--2005 nova sintaxe, semelhante às tabelas temporárias, mas o ciclo de vida é um pouco diferente. Aqui está apenas uma aplicação dela.
com cte_temp--Define a tabela de tempo zero. PageIndex é um campo calculado que armazena o número da página dos resultados da pesquisa.
As (ceiling((Row_Number() over(order by .... )-1)/@pagesize as int) as PageIndex,* from TableName where.....)
selecione * de cte_temp onde pageindex=@pageindex-1;
para concluir:
TopN é o mais rápido quando o número de páginas é pequeno. Se for menor que 10 páginas, você pode considerar usá-lo. O CTE e os tempos da tabela temporária consomem mais tempo do que a tabela temporária, mas não causará. tempdb disparará e IO aumentará.
Comparação de desempenho
Ambiente de teste: win2003server, Sqlserver2005, tamanho da biblioteca 2.567.245 linhas, nenhuma cláusula where, tamanho da página 50 durante o teste, número da página como variável
Pegue 0, 3, 10, 31, 100, 316, 1000, 3162... páginas, que é o índice de 10. Os resultados do teste são os seguintes
Número de páginasTopN CTE Tabela temporária (com cache) Tabela temporária (sem cache)
Melhorias no CTE do procedimento armazenado que a empresa está usando
1 3 12 10 101 457 7302
3 15 7 79 5524 464 7191
10 127 5504 88 3801 464 6116
32 588 9672 122 3601 976 7602
100 4680 9738 166 4235 486 7151
316 45271 9764 323 3867 522 7255
1000 Nulo 9806 869 2578 635 8948
3162 Nulo 9822 2485 4110 12460 8210
10000 Nulo 9754 7812 11926 14250 7359
31623 Nulo 9775 18729 33218 15249 7511
100000 Nulo Nulo 31538 55569 17139 6124
Interpretação e análise de dados
As tabelas temporárias são divididas em dois tipos de tempo: cache ou não é o método acima. A melhoria do CTE apenas reduz o número de colunas selecionadas na tabela temporária do CTE. o tempo não pode ser calculado (o tempo é muito longo), a unidade de dados é milissegundos.
A partir dos dados acima, podemos perceber que o TopN tem vantagens nas primeiras 32 páginas, mas à medida que o número de páginas aumenta, o desempenho diminui rapidamente. A melhoria do CTE é melhor que a do CTE, com uma melhoria média de cerca de dois segundos, mas. ainda é melhor que a tabela temporária Lento, mas considerando que a tabela temporária aumentará o tamanho do arquivo de log e causará muito IO, o CTE também tem suas próprias vantagens. O procedimento armazenado atualmente utilizado pela empresa é muito eficiente, mas o desempenho diminuirá quando o número da página for posterior.