Nessun risultato. Prova con un altro termine.
Guide
Notizie
Software
Tutorial

Paginazione dei risultati in SQL Server 2005

Migliorare le prestazioni della paginazione grazie alle novità introdotte in TSQL
Migliorare le prestazioni della paginazione grazie alle novità introdotte in TSQL
Link copiato negli appunti

La parola che più occupa la mente di uno sviluppatore è sicuramente una: performance. Applicazioni Web dotate di strabilianti interfacce grafiche, ergonomiche e funzionali alla fine cadono su una banale query che fa aspettare minuti per visualizzare la lista dei suoi risultati. In questo articolo scopriremo come le nuove funzionalità TSQL in SQL Server 2005 aiutino a semplificare la paginazione lato server dei risultati.

Cos'è la paginazione?

Paginare significa suddividere il set di risultati provenienti da una query SQL in parti più piccole, pagine per l'appunto. Queste, una volta inviate al client, possono essere visualizzate attraverso un meccanismo di navigazione il quale consente all'utente di passare agevolmente da una pagina all'altra di risultati. Per rendere il concetto pensiamo ad una ricerca fatta con Google. Paginare un set di risultati ha senso quando si attendono centinaia o migliaia di righe in risposta da una query. In questo caso non utilizzare la paginazione dei risultati è rischioso perchè:

  • Compromette la leggibilità della pagina web (una pagina "lunga" non verrà mai letta fino in fondo dall'utente).
  • Allunga i tempi di caricamento della pagina web (centinaia di records producono anche molto markup html, quindi la pagina aumenta enormemente le sue dimensioni).
  • Aumenta il quantitativo di dati scambiati tra server e client durante ogni richiesta HTTP.
  • Impegna più a lungo le risorse del server soprattutto nel caso di più richieste concorrenti per la stessa pagina web.

Per questi motivi paginare significa migliorare le performance globali di un'applicazione web, inoltre è bene ricordare che i risultati delle query generalmente viaggiano su reti lente, quindi è buona cosa limitarne il numero. La regola è: chiedere a SQL Server solo ciò che serve veramente.

La precedente versione di SQL Server non offriva molte possibilità per supportare la paginazione in modo "naturale" all'interno delle proprie query. Per questo motivo era necessario usare tecniche più o meno complesse per arrivare allo scopo. In SQL Server 2005 le cose sono cambiate.

Paginazione in SQL Server 2005

L'avvento di SQL Server 2005 ha introdotto nuove funzionalità che semplificano enormemente la paginazione lato server:

  1. Funzione ROW_NUMBER() (assegna un numero univoco ad ogni riga alla quale è applicata).
  2. Clausola TOP parametrizzabile (ora possiamo scrivere SELECT TOP @numRighe * FROM Tabella).
  3. Common Table Expression (permettono di creare dinamicamente in memoria delle "viste" su cui si possono poi effettuare delle query).

Grazie a queste nuove funzionalità possiamo facilmente estrarre da una tabella un numero limitato di record specificando il numero di pagina e il numero di righe per ciascuna pagina. Oltre a queste non dobbiamo dimenticare l'istruzione ROWCOUNT che per definizione serve a limitare il numero di righe restituite al client da una query SQL.

Il listato 1 mostra come effettuare la paginazione di una tabella con l'ausilio della funzione ROW_NUMBER(). Questa non fa altro che aggiungere un contatore univoco per ogni riga della tabella (se una tabella ha 50 righe ROW_NUMBER() applicherà su ogni riga un indice sequenziale da 1 a 50).

L'estrazione dei risultati viene effettuata sulla base della dimensione della pagina N (quante righe deve contenere una pagina) e del numero di pagina desiderata M.

Il calcolo per ottenere l'indice di riga iniziale e finale viene fatto seguendo la formula: N x M a cui si aggiunge: 1 per il calcolo dell'indice iniziale (ricordiamo che l'operatore BETWEEN è inclusivo!) ed invece la dimensione della pagina per il calcolo dell'indice finale. La pagina iniziale parte da 0 ma ha il significato di 1. Esempio:

Pagina Richiesta: Cal. Ind. Iniziale Cal. Ind. Finale Righe Richieste:
1 0*10+1 0*10+10 da 1 a 10
2 1*10+1 1*10+10 da 11 a 20
3 2*10+1 2*10+10 da 21 a 30
4 3*10+1 3*10+10 da 31 a 40

Listato 1. Paginare con ROW_NUMBER()

declare @dimPag int, @numPag int, @inizio int, @fine int;
set @dimPag=5; set @numPag=0;
set @inizio=(@numPag * @dimPag + 1); set @fine=(@numPag * @dimPag + @dimPag);
set rowcount @dimPag
SELECT * FROM
(
--Tramite row_numer() creo un indice univoco per ogni riga da accodare alla tabella
SELECT ROW_NUMBER()OVER(ORDER BY [create_date] ASC) AS [indiceRiga], * FROM sys.objects
--WHERE [name] LIKE 'sys%'
) AS T
WHERE T.indiceRiga BETWEEN @inizio AND @fine

Per variare l'ordinamento delle righe della query dobbiamo agire sulla specifica ORDER BY dell'opzione OVER di ROW_NUMBER() la quale non essendo parametrizzabile limita la sfruttabilità di questa query nel caso volessimo ordinare dinamicamente i risultati. Nel listato 2 viene mostrato come superare l'ostacolo facendo ricorso ad istruzioni SQL generate dinamicamente.

Listato 2. Paginare con ROW_NUMBER() con modifica dinamica della clausola order by

declare @sql nvarchar(max), @parmsDef nvarchar(max), @sort varchar(30);
set @sort = 'create_date ASC';
SET @parmsDef = N'@i_inizio int, @i_fine int';
set @sql= 'SELECT * FROM
(
    SELECT ROW_NUMBER()OVER(ORDER BY ' + @sort + ') AS [indiceRiga], * FROM sys.objects
) AS T
WHERE T.indiceRiga BETWEEN @i_inizio AND @i_fine
'

exec sp_executesql @sql,@parmsDef,@i_inizio= @inizio,@i_fine=@fine

Ora possiamo cambiare al volo l'ordinamento dei risultati in entrambe le direzioni (ASC e DESC).

Conclusione

Paginare significa migliorare le performance delle applicazioni. Questa regola dovrebbe essere sempre seguita con scrupolo ed in particolar modo nelle applicazioni di classe enterprise dove scalabilità, prestazioni e disponibilità dei servizi dovrebbero essere garantiti al meglio.

Ti consigliamo anche