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:
- Funzione
ROW_NUMBER()(assegna un numero univoco ad ogni riga alla quale è applicata). - Clausola
TOPparametrizzabile (ora possiamo scrivereSELECT TOP @numRighe * FROM Tabella). - 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.
