Servizi
versione italiana
english versionIl problema di sempre :-) ...
Da anni esiste un problema che assilla gli sviluppatori di applicazioni Web e non solo Web: la paginazione dei record.
Tutte le volte che si parla di questo argomento c’è chi storce il naso e sostiene che è un falso problema. Probabilmente ha spesso ragione! Quello che voglio dire è che molto spesso abbiamo l’esigenza di paginare i record perché non abbiamo fornito ai nostri utenti gli strumenti adatti o non siamo stati abbastanza convincenti :-), per far capire loro che ottenere una lista di 850.000 record da paginare 20 alla volta non serve poi un granché ... o no? In questo senso spesso si dice che paginare migliaia di record non è un problema da risolvere ma è un problema da evitare.
D’altra parte, però, sappiamo anche che non sempre si può realizzare tutto come si vorrebbe. A volte gli utenti dobbiamo accontentarli, soprattutto se sono clienti o responsabili d’ufficio che ci pagano in proporzione a quanto li rendiamo contenti...
Inoltre, molto spesso noi forniamo agli utenti degli strumenti di consultazione dei dati che sono personalizzabili e che in funzione di come vengono personalizzati possono fornire tanto 10 quanto 10.000 record come risultato.
Pensiamo a un caso sicuramente noto a tutti: il motore di ricerca di Google.
Se io cerco <<“XML Schema Definition” XAML>> otterrò un certo risultato (oggi ottengo 822 righe), se invece cerco “XML” ne otterrò un altro (21.900.000 righe). Il risultato sarà diverso sia in termini di qualità che di quantità. Eppure Google ci fornisce una lista paginata e maledettamente veloce da paginare! :-)
Il problema principale, per una tipica applicazione di consultazione dati, è fornire una lista che sia anche ordinabile e ricercabile secondo dei filtri che sono definibili liberamente dall’utente finale dell’applicazione. È infatti relativamente facile restituire una lista di record, ordinati secondo un preciso criterio, che non cambierà mai, consentendo all’utente di spostarsi solo avanti o indietro di una pagina per volta rispetto alla pagina correntemente visualizzata. Un altro discorso è fornire una lista filtrata e ordinata a piacere, paginandola in modo casuale con una finestra di N (di solito 10 o 20) pagine per volta, ciascuna con 20/30/50 record.
Le possibili soluzioni
Da più di un mese (nel nostro newsgroup interno a DevLeap) stiamo discutendo di questa faccenda, perché ci sono N possibili soluzioni. Tutte possono andare bene, ma a seconda dell’obiettivo da raggiungere. Questo significa che non esiste LA soluzione, ma esistono DELLE soluzioni possibili. Sta a noi e alla nostra competenza di analisti e non solo di programmatori capire quando applicare quale soluzione.
L’obiettivo sarà sempre e comunque quello di fornire ai nostri utenti la possibilità di elencare dei record, eventualmente filtrati e/o ordinati a piacere (entro certi limiti di filtro e ordinamento dettati dal buon senso).
Partendo quindi dalle nostre discussioni e mescolando le mie soluzioni a quelle proposte da Marco e Silvano (che mi hanno dato un indispensabile contributo in termini di idee) ho deciso di provare a ricondurre il problema a una serie di casi.
Ho preso come esempio un’applicazione Web, per evidente deformazione professionale, e ho utilizzato quasi sempre una DataGrid ASP.NET, perché è comoda e perché è quella che 9 sviluppatori su 10 utilizzano (magari facendone un abuso) se devono fornire una lista paginata, ordinabile e filtrabile in un’applicazione Intranet/Internet.
Ho utilizzato un database demo con una tabella di 1 milione di record così strutturata:
CREATE TABLE [dbo].[tabCustomers] ( [idCustomer] [int] INDENTITY(1,1) , [CustomerName] [varchar] (30) NOT NULL , [CustomerEMail] [varchar] (50) NOT NULL , [CustomerBirthday] [datetime] NOT NULL, [CustomerSex] [bit] NOT NULL , [SysTimestamp] [timestamp] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[tabCustomers] WITH NOCHECK ADD CONSTRAINT [PK_tabCustomers] PRIMARY KEY CLUSTERED ( [idCustomer] ) ON [PRIMARY] GO ALTER TABLE [dbo].[tabCustomers] ADD CONSTRAINT [DF_tabCustomers_CustomerSex] DEFAULT (0) FOR [CustomerSex] GO
Come si vede ho creato una PrimaryKey con un indice clustered (cioè che definisce l’ordinamento dei record nella tabella) sulla colonna idCustomer, definita come INDENTITY (ho cercato di riprodurre un caso abbastanza tipico).
Con uno script banale ho popolato la tabella con 1 milione di record (senza troppa fantasia, lo so).
Ho predisposto le varie soluzioni e le ho eseguite, visualizzando diverse pagine, con diversi filtri e ordinamenti.
Ciascuna delle tecniche di paginazione è verificabile in termini di codice SQL tramite lo ZIP allegato a questo articolo. Non ho pubblicato direttamente sul nostro solito DevLab la soluzione Web, in quanto volevo evitare che mi uccideste il server SQL e/o il server Web nel testare gli scenari meno efficaci ... :-)
In tutti i casi mi voglio concentrare sul codice SQL e non sul codice ASP.NET, che è solo un dettaglio applicativo. Paginare una griglia ASP.NET non c’entra con i criteri di paginazione sul DB e potete trovare centinaia di articoli e libri che spiegano come farlo in modo più o meno automatico. In tutti i casi costruisco dinamicamente, all’interno di apposite stored procedure (perché non si può usare altro che stored procedure nelle applicazioni Web, e spesso non solo Web, ben fatte :-) !), lo statement SQL di selezione delle righe, in funzione dei criteri di filtro e ordinamento scelti dall’utente, quindi invoco la stored procedure di sistema sp_executesql per ottenere il risultato. Alla prima richiesta avrò prestazioni modeste, ma dalla seconda potrò sfruttare il caching del piano di esecuzione della query costruita dinamicamente, quindi, per ricerche uguali in termini di filtro, avrò probabilmente già in cache i loro piani di esecuzione.
|
Tecnica |
Pro |
Contro |
|
Paginazione automatica |
Non richiede troppo sforzo da parte del programmatore |
Già quando si superano i 1000 record inizia a essere molto lenta. Carica inutilmente il server. |
|
Paginazione con DataAdapter |
Richiede poco sforzo |
Al crescere del numero di pagina, cresce il costo. Non è una soluzione ottimale perché “spreca” tutti i record prima di quelli della pagina corrente |
|
Cursore Server-Side |
Consuma solo i record necessari |
Richiede un cursore lato server |
|
Tabella temporanea |
Non richiede cursori |
La tabella temporanea determina la ricompilazione del piano di esecuzione della stored procedure |
|
Paginazione custom con colonna dinamica |
Ottimi risultati, anche con alto numero di record |
Richiede impegno per la codifica della stored procedure |
|
Paginazione automatica di Whidbey |
È automatica :-) |
Usa un cursore lato server |
Premesse a proposito della sicurezza
Tutte le soluzioni proposte prevedono che le applicazioni che ne fanno uso siano progettate e sviluppate senza trascurare gli aspetti e i problemi legati alla sicurezza.
Alcune delle stored procedure proposte, se alimentate con parametri “grezzi” non controllati e filtrati con regular expression o soluzioni analoghe, potrebbero dare adito a problemi di SQL Injection.
Questo è un articolo che concentra la sua attenzione sugli statement SQL e sulle tecniche di paginazione, non sull’architettura di un’intera applicazione, quindi non avremmo tutti gli elementi necessari a svolgere delle valutazioni serie sul contesto di sicurezza del codice proposto. Prima utilizzare le soluzioni proposte occorre un’attenta valutazione dei rischi e delle problematiche di security, contestualizzandone l’uso.
Tradotto in altre parole:
- Se consento a un utente di eseguire a mano le stored procedure proposte o di caricare del testo libero nelle condizioni di filtro e ordinamento: NON SONO PER NULLA SICURE.
- Se controllo e gestisco con del codice sicuro l’esecuzione delle stored procedure proposte: SONO ASSOLUTAMENTE SICURE.
Paginazione automatica
È la soluzione più semplice e come tale anche la meno efficace. Basta chiedere alla griglia ASP.NET di fare il “lavoro sporco” per noi, utilizzando l’attributo AllowPaging configurato a True. Non mi soffermo più di tanto sui dettagli perché è una soluzione che sicuramente tutti conosciamo e che troviamo documentata ovunque. Mi interessa invece sottolineare come funziona la paginazione automatica della griglia ASP.NET. Si basa sull’intero resultset e richiede che esso sia interamente navigabile. Non accetta quindi come fonte dati un DataReader, digerisce invece di buon grado un DataSet. Nel visualizzare la pagina richiesta dall’utente, scorre autonomamente i record sino al primo della pagina da mostrare e da quello in poi mostra i primi N record che rappresentano la pagina richiesta dall’utente finale. Il problema di una simile tecnicaè che richiede il download di tutti i dati dal server per ogni pagina da visualizzare o, in alternativa, che costringe a tenere in cache l’intero resultset, tra una richiesta e l’altra, per non gravare sulla rete e/o sul server di dati. Risultato: con 1 milione di record andremo a richiedere ogni volta al server esattamente 1 milione di record, per poi mostrarne nella pagina solo 20 (se questa è la dimensione di pagina scelta). In alternativa possiamo tenere in memoria un DataSet con una DataTable di 1 milione di record (sono circa 290 Mbyte di RAM in questo esempio) e se la ricerca è per utente (caso in cui i risultati sono legati alla sessione utente) significa che con 20 utenti connessi avremmo bisogno di circa 6 Gbyte di RAM! Ok. Ci siamo capiti. NON VA USATA SE NON CON RESULTSET DI MODESTISSIMA ENTITÀ!
In ogni caso ecco la stored procedure “dinamica” di selezione:
CREATE PROCEDURE spListCustomersForAutoPaging ( @CustomerName varchar(30) = '', @CustomerEMail varchar(50) = '', @CustomerBirthDayStart datetime = NULL, @CustomerBirthDayEnd datetime = NULL, @CustomerSex bit = NULL, @SortExpression nvarchar(100) = NULL ) AS -- Variabili di appoggio DECLARE @SqlStatement nvarchar(1000) DECLARE @Prefix nvarchar(7) SET @Prefix = ' WHERE ' -- Definisco la parte di SELECT SET @SqlStatement = 'SELECT idCustomer, CustomerName, CustomerEMail, CustomerBirthday, CustomerSex FROM tabCustomers' -- Se ho il filtro sul CustomerName IF (@CustomerName <> '') BEGIN SET @SqlStatement = @SqlStatement + @Prefix + ' CustomerName LIKE ''%' + CONVERT(nvarchar(30), REPLACE(@CustomerName, '''', '''''')) + '%''' SET @Prefix = ' AND ' END -- Se ho il filtro sul CustomerEMail IF (@CustomerEMail <> '') BEGIN SET @SqlStatement = @SqlStatement + @Prefix + ' CustomerEMail LIKE ''%' + CONVERT(nvarchar(30), REPLACE(@CustomerEMail, '''', '''''')) + '%''' SET @Prefix = ' AND ' END -- Se ho il filtro sul CustomerBirthday IF ((NOT @CustomerBirthDayStart IS NULL) AND (NOT @CustomerBirthDayEnd IS NULL)) BEGIN SET @SqlStatement = @SqlStatement + @Prefix + ' CustomerBirthDay BETWEEN ''' + CONVERT(nvarchar(30), @CustomerBirthDayStart) + ''' AND ''' + CONVERT(nvarchar(30), @CustomerBirthDayEnd) + '''' SET @Prefix = ' AND ' END -- Se ho il filtro sul CustomerSex IF (NOT @CustomerSex IS NULL) BEGIN SET @SqlStatement = @SqlStatement + @Prefix + ' CustomerSex = ' + CONVERT(nvarchar(30), @CustomerSex) END -- Se devo applicare l'ordinamento IF (NOT @SortExpression IS NULL) SET @SqlStatement = @SqlStatement + ' ORDER BY ' + @SortExpression -- Eseguo lo statement di SELECT EXEC sp_executesql @SqlStatement GO
Come si vede, concateno i vari criteri di filtro e il criterio di ordinamento, per poi eseguire la query con sp_executesql e passare il risultato al chiamante.
Paginazione custom con DataAdapter
Il DataAdapter di ADO.NET ci fornisce la possibilità di popolare un DataSet indicando da quale record vogliamo iniziare e per quanti record ci vogliamo spostare in avanti.
da.Fill(ds, gridCustomers.PageSize * gridCustomers.CurrentPageIndex, gridCustomers.PageSize, "tabCustomers");
Sappiamo (se non lo sapete vi consiglio di leggere il libro di Silvano che lo spiega molto bene) che il DataAdapter internamente utilizza un DataReader per riempire il DataSet. Ecco che allora risulta immediato capire come si comporti in questo caso: scorre in modalità forward-only e read-only il DataReader e quando arriva al primo record utile del resultset inizia a riempire la DataTable di destinazione; quando sono stati raggiunti gli N record di pagina, annulla la parte restante della richiesta. Questa soluzione carica molto meno il server web, ma non risparmia per nulla la rete e il Server SQL, in particolare se richiediamo numeri di pagina alti. Quindi, ancora una volta, è applicabile su resultset di modeste dimensioni o comunque su pagine basse. Sicuramente è preferibile al caso della paginazione automatica. Dal punto di vista del codice SQL non dobbiamo fare nulla, perché tutto il lavoro sarà a carico del DataAdapter. La nostra stored procedure di selezione dei record fornirà comunque tutti i record:
CREATE PROCEDURE spListCustomersForDataAdapterPaging ( @CustomerName varchar(30) = '', @CustomerEMail varchar(50) = '', @CustomerBirthDayStart datetime = NULL, @CustomerBirthDayEnd datetime = NULL, @CustomerSex bit = NULL, @SortExpression nvarchar(100) = NULL, @RecordCount int OUTPUT ) AS -- Non devo fare altro che richiamare la spListCustomersForAutoPaging aggiungendo il numero di record che ho ottenuto EXEC spListCustomersForAutoPaging @CustomerName, @CustomerEMail, @CustomerBirthDayStart, @CustomerBirthDayEnd, @CustomerSex, @SortExpression SET @RecordCount = @@ROWCOUNT GO
Rispetto al caso precedente, ora devo fornire al chiamante il numero complessivo di record che corrispondono al criterio impostato, per poter valorizzare la proprietà VirtualItemCount della DataGrid ASP.NET .
Paginazione custom con cursore server-side
Mi scuso in anticipo con Silvano e Gianluca Hotz (amico e SQL Server MVP con gli attributi) :-) per quello che sto per dire, ma una possibile soluzione da valutare è quella di utilizzare un cursore lato server, che ci consenta di posizionare il server e non più il client (cioè ASP.NET o lo strato business) sulla pagina corretta, per poi caricare la rete dei soli record di nostro interesse. Se non dovessimo fornire al nostro utente la possibilità di filtrare e ordinare liberamente i record, basterebbe definire un cursore “classico” con DECLARE ... CURSOR FOR e farne la FETCH per spostarci sui record di nostro interesse. Qualcosa tipo:
-- Cursore per scorrere i record di tabCustomers DECLARE curCustomers SCROLL CURSOR FOR SELECT idCustomer, CustomerName, CustomerEMail, CustomerBirthday, CustomerSex FROM tabCustomers FOR READ ONLY -- Apro il cursore OPEN curCustomers -- Mi sposto sul primo record della pagina DECLARE @FirstRecordPosition int SET @FirstRecordPosition = (@PageSize * (@CurrentPage - 1)) + 1 FETCH ABSOLUTE @FirstRecordPosition FROM curCustomers -- Ottengo i record della pagina richiesta DECLARE @CurrentPosition int SET @CurrentPosition = 1 WHILE ((@@FETCH_STATUS = 0) AND (@CurrentPosition < @PageSize)) BEGIN SET @CurrentPosition = @CurrentPosition + 1 FETCH NEXT FROM curCustomers END -- Rilascio le risorse allocate CLOSE curCustomers DEALLOCATE curCustomers SELECT @RecordCount = Count(*) FROM tabCustomers GO
Il problema è che a noi serve poter definire dinamicamente l’oggetto della SELECT su cui aprire il cursore. Osservando come si comporta il buon vecchio Recordset ADODB possiamo però vedere che esistono delle extended stored procedure di sistema come sp_cursoropen, sp_cursorfetch e sp_cursorclose. Usate opportunamente (al di là - per un attimo - delle valutazioni filosofiche sull’opportunità di farne uso) possiamo definire una stored procedure come la seguente:
CREATE PROCEDURE spListCustomersServerSideCursor
(
@CustomerName varchar(30) = '',
@CustomerEMail varchar(50) = '',
@CustomerBirthDayStart datetime = NULL,
@CustomerBirthDayEnd datetime = NULL,
@CustomerSex bit = NULL,
@SortExpression nvarchar(100) = NULL,
@PageSize int = 20,
@CurrentPage int = 1,
@RecordCount int OUTPUT
)
AS
-- Variabili di appoggio
DECLARE @SqlStatement nvarchar(1000)
DECLARE @SqlCondition nvarchar(1000)
DECLARE @Prefix nvarchar(7)
SET @Prefix = ' WHERE '
SET @SqlCondition = ''
-- Definisco la parte di SELECT
SET @SqlStatement = 'SELECT idCustomer, CustomerName, CustomerEMail,
CustomerBirthday, CustomerSex FROM tabCustomers'
-- Se ho il filtro sul CustomerName
IF (@CustomerName <> '')
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerName LIKE ''%' +
CONVERT(nvarchar(30), REPLACE(@CustomerName, '''', '''''')) + '%'''
SET @Prefix = ' AND '
END
-- Se ho il filtro sul CustomerEMail
IF (@CustomerEMail <> '')
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerEMail LIKE ''%' +
CONVERT(nvarchar(30), REPLACE(@CustomerEMail, '''', '''''')) + '%'''
SET @Prefix = ' AND '
END
-- Se ho il filtro sul CustomerBirthday
IF ((NOT @CustomerBirthDayStart IS NULL) AND (NOT @CustomerBirthDayEnd IS NULL))
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerBirthDay BETWEEN ''' +
CONVERT(nvarchar(30), @CustomerBirthDayStart) + ''' AND ''' +
CONVERT(nvarchar(30), @CustomerBirthDayEnd) + ''''
SET @Prefix = ' AND '
END
-- Se ho il filtro sul CustomerSex
IF (NOT @CustomerSex IS NULL)
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerSex = ' + CONVERT(nvarchar(30), @CustomerSex)
END
SET @SqlStatement = @SqlStatement + @SqlCondition
-- Se devo applicare l'ordinamento
IF (NOT @SortExpression IS NULL)
SET @SqlStatement = @SqlStatement + ' ORDER BY ' + @SortExpression
-- Stabilisco qual è il primo record della pagina
DECLARE @FirstRecordPosition int
SET @FirstRecordPosition = (@PageSize * (@CurrentPage - 1)) + 1
-- Dichiaro e uso il cursore dinamico
DECLARE @CursorID int
DECLARE @ScrollOpt int
DECLARE @CCOpt int
DECLARE @RowCount int
SET @ScrollOpt = 4
SET @CCOpt = 1
SET @RowCount = -1
EXEC sp_cursoropen @CursorID OUT, @SqlStatement, @ScrollOpt output,
@CCOpt output, @RowCount output
EXEC sp_cursorfetch @CursorID ,32, @FirstRecordPosition, @PageSize
EXEC sp_cursorclose @CursorID
SET @SqlStatement = 'SELECT @RC = Count(*) FROM tabCustomers' + @SqlCondition
EXEC sp_executesql @SqlStatement, N'@RC int OUTPUT', @RC = @RecordCount OUTPUT
GO
Come si vede sp_cursoropen apre un cursore sulla base di uno statement SQL da noi definibile dinamicamente (proprio quello che ci serve!). Il risultato della sp_cursoropen è l’ID del cursore, con il quale possiamo chiedere a sp_cursorfetch di darci N record (per noi @PageSize) partendo da un certo record (@FirstRecordPosition). Dobbiamo poi ricordarci di chiudere il cursore con sp_cursorclose.
Bene! Dobbiamo essere consapevoli del fatto che non stiamo facendo una bella cosa :-) ! Però funziona, potrebbe dire qualcuno. Sì, funziona, ma non è particolarmente performante. Sicuramente non carica l’applicazione Web ASP.NET o lo strato business che ne fa uso. Carica decisamente di più il Server SQL. Non carica per nulla la rete, visto che passeranno solo N record, dove N sarà pari a @PageSize, e il numero complessivo dei record che corrispondono al criterio di ricerca impostato, per valorizzare la proprietà VirtualItemCount della griglia ASP.NET.
Paginazione custom con tabella temporanea
Proviamo allora a evitare di utilizzare cursori lato server, senza rinunciare a lasciare scarico il client e la rete. Possiamo intanto pensare che se lavoriamo con gli indici giusti e non dobbiamo cambiare l’ordinamento predefinito dei record nella tabella, una buona soluzione è quella che prevede proprio di ragionare in base alla colonna chiave primaria e indice clustered della tabella.
Come posso identificare gli N record della pagina X ragionando sulla base della PrimaryKey?
CREATE PROCEDURE spListCustomersSortedByClusteredIndexedPrimaryKey
(
@CustomerName varchar(30) = '',
@CustomerEMail varchar(50) = '',
@CustomerBirthDayStart datetime = NULL,
@CustomerBirthDayEnd datetime = NULL,
@CustomerSex bit = NULL,
@PageSize int = 20,
@CurrentPage int = 1,
@RecordCount int OUTPUT
)
AS
-- Variabili di appoggio
DECLARE @SqlStatement nvarchar(1000)
DECLARE @SqlCondition nvarchar(1000)
DECLARE @Prefix nvarchar(7)
SET @Prefix = ' WHERE '
SET @SqlCondition = ''
-- Se ho il filtro sul CustomerName
IF (@CustomerName <> '')
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerName LIKE ''%' +
CONVERT(nvarchar(30), REPLACE(@CustomerName, '''', '''''')) + '%'''
SET @Prefix = ' AND '
END
-- Se ho il filtro sul CustomerEMail
IF (@CustomerEMail <> '')
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerEMail LIKE ''%' +
CONVERT(nvarchar(30), REPLACE(@CustomerEMail, '''', '''''')) + '%'''
SET @Prefix = ' AND '
END
-- Se ho il filtro sul CustomerBirthday
IF ((NOT @CustomerBirthDayStart IS NULL) AND (NOT @CustomerBirthDayEnd IS NULL))
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerBirthDay BETWEEN ''' +
CONVERT(nvarchar(30), @CustomerBirthDayStart) + ''' AND ''' +
CONVERT(nvarchar(30), @CustomerBirthDayEnd) + ''''
SET @Prefix = ' AND '
END
-- Se ho il filtro sul CustomerSex
IF (NOT @CustomerSex IS NULL)
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerSex = ' +
CONVERT(nvarchar(30), @CustomerSex)
SET @Prefix = ' AND '
END
-- Costruisco lo statement che seleziona i dati della pagina di mio interesse
SET @SqlStatement = 'SELECT TOP ' + CONVERT(nvarchar, @PageSize) + ' idCustomer,
CustomerName, CustomerEMail, CustomerBirthday, CustomerSex FROM tabCustomers '
+ @SqlCondition
IF (@CurrentPage > 1)
SET @SqlStatement = @SqlStatement + ' ' + @Prefix + ' idCustomer > ' +
'(SELECT Max(idCustomer) FROM tabCustomers ' + @SqlCondition + ' '
+ @Prefix + ' idCustomer IN (SELECT TOP ' +
CONVERT(nvarchar, (@PageSize * (@CurrentPage - 1))) +
' idCustomer FROM tabCustomers ' + @SqlCondition + '))'
-- Riempio la tabella temporanea di appoggio
EXEC sp_executesql @sqlStatement
-- Conto quanti sono i record che corrispondo al criterio di filtro impostato dall'utente (per il VirtualItemCount)
SET @SqlStatement = 'SELECT @RC = Count(*) FROM tabCustomers' + @SqlCondition
EXEC sp_executesql @SqlStatement, N'@RC int OUTPUT', @RC = @RecordCount OUTPUT
GO
La parte significativa, evidenziata in grassetto, è quella che costruisce lo statement SQL di selezione, richiedendo i primi @PageSize record che abbiano un idCustomer maggiore del maggiore idCustomer di tutte le pagine precedenti.
In pratica, la query costruita per la pagina 43.146 di dimensione 20 sarà:
SELECT TOP 20 idCustomer, CustomerName, CustomerEMail, CustomerBirthday, CustomerSex FROM tabCustomers WHERE idCustomer > (SELECT Max(idCustomer) FROM tabCustomers WHERE idCustomer IN (SELECT TOP 862900 idCustomer FROM tabCustomers))
Dal momento che, fino a quando non avremo Yukon, non possiamo eseguire una SELECT TOP @Parametro, ci dobbiamo arrangiare ancora con uno statement costruito a mano e poi eseguito con sp_executesql.
Si tratta di una soluzione normalmente adeguata, in quanto sfruttiamo l’ordinamento fornito dall’indice clustered senza ulteriori overhead. Se non dobbiamo permettere ai nostri utenti di cambiare ordinamento direi che è la soluzione da preferire, perché carica poco sia SQL, sia la rete, sia il client.
Se però la dimensione in byte di una riga è voluminosa, non è detto che la soluzione basata sull’indice clustered sia la più efficiente: in presenza di record con una o più colonne di tipo testuale molto lunghe, potrebbe risultare più performante creare l’indice sulla chiave primaria in modalità NONCLUSTERED, senza alcun indice CLUSTERED; tale configurazione può risultare molto più performante della precedente in caso di query completamente coperte dall’indice (situazione in cui SQL Server legge solo l’indice senza accedere alle pagine che contengono i dati della tabella vera e propria). Purtroppo è difficile stabilire a priori quale sia la soluzione più performante, fondamentalmente ci sono molte variabili in gioco e la cosa migliore è provare le due varianti e fare dei benchmark specifici. Certamente in casi estremi, per esempio una riga con dimensione effettiva di circa 1.000 bytes, può essere estremamente conveniente avere un indice NONCLUSTERED che risolve la query, perché questo significa leggere circa 2.000 record per pagina sull’indice, contro gli 8 record per pagina della tabella reale (parliamo di operazioni di I/O, i risultati sono visibili già a occhio nudo su tabelle con decine di migliaia di record, in assenza di cache).
Spostiamo il problema al caso in cui vogliamo scegliere anche il criterio di ordinamento. Lo statement SQL dovrà essere modificato in modo tale da confrontare i record ordinati secondo il criterio scelto dall’utente e non dall’indice clustered. Ecco che una possibile soluzione è quella di utilizzare una tabella temporanea in cui andremo a inserire solo la chiave primaria di ciascun record, nell’ordine scelto dall’utente, e nella quale definiremo una nostra chiave di servizio, con tanto di indice clustered, che ci permetterà di riapplicare il concetto visto nel caso precedente. Ecco il codice SQL:
CREATE PROCEDURE spListCustomersTempTable
(
@CustomerName varchar(30) = '',
@CustomerEMail varchar(50) = '',
@CustomerBirthDayStart datetime = NULL,
@CustomerBirthDayEnd datetime = NULL,
@CustomerSex bit = NULL,
@SortExpression nvarchar(100) = NULL,
@PageSize int = 20,
@CurrentPage int = 1,
@RecordCount int OUTPUT
)
AS
-- Definisco una tabella temporanea di appoggio
CREATE TABLE #tmpCustomers
(
idRecord int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
idCustomer int
)
-- Variabili di appoggio
DECLARE @SqlStatement nvarchar(1000)
DECLARE @SqlCondition nvarchar(1000)
DECLARE @Prefix nvarchar(7)
SET @Prefix = ' WHERE '
SET @SqlCondition = ''
-- Costruisco lo statement che popola la tabella temporanea
SET @SqlStatement = 'INSERT INTO #tmpCustomers (idCustomer) SELECT TOP ' +
CONVERT(nvarchar, (@PageSize * @CurrentPage)) + ' idCustomer FROM tabCustomers'
-- Se ho il filtro sul CustomerName
IF (@CustomerName <> '')
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerName LIKE ''%' +
CONVERT(nvarchar(30), REPLACE(@CustomerName, '''', '''''')) + '%'''
SET @Prefix = ' AND '
END
-- Se ho il filtro sul CustomerEMail
IF (@CustomerEMail <> '')
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerEMail LIKE ''%' +
CONVERT(nvarchar(30), REPLACE(@CustomerEMail, '''', '''''')) + '%'''
SET @Prefix = ' AND '
END
-- Se ho il filtro sul CustomerBirthday
IF ((NOT @CustomerBirthDayStart IS NULL) AND (NOT @CustomerBirthDayEnd IS NULL))
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerBirthDay BETWEEN ''' +
CONVERT(nvarchar(30), @CustomerBirthDayStart) + ''' AND ''' +
CONVERT(nvarchar(30), @CustomerBirthDayEnd) + ''''
SET @Prefix = ' AND '
END
-- Se ho il filtro sul CustomerSex
IF (NOT @CustomerSex IS NULL)
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerSex = ' +
CONVERT(nvarchar(30), @CustomerSex)
END
SET @SqlStatement = @SqlStatement + @SqlCondition
-- Se devo applicare l'ordinamento
IF (NOT @SortExpression IS NULL)
SET @SqlStatement = @SqlStatement + ' ORDER BY ' + @SortExpression
-- Riempio la tabella temporanea di appoggio
EXEC sp_executesql @sqlStatement
-- Preparo lo statement di SELECT dei dati per fornire il vero risultato all'utente
SET @SqlStatement = 'SELECT idCustomer, CustomerName, CustomerEMail,
CustomerBirthday, CustomerSex FROM tabCustomers WHERE idCustomer IN
(SELECT TOP ' + CONVERT(nvarchar, @PageSize) + ' idCustomer FROM #tmpCustomers '
IF (@CurrentPage > 1)
SET @SqlStatement = @SqlStatement +
' WHERE idRecord > (SELECT Max(idRecord) FROM #tmpCustomers WHERE idRecord
IN (SELECT TOP ' + CONVERT(nvarchar, (@PageSize * (@CurrentPage - 1))) +
' idRecord FROM #tmpCustomers))'
SET @SqlStatement = @SqlStatement + ')'
-- Eseguo lo statement di SELECT
EXEC sp_executesql @sqlStatement
-- Elimino la tabella temporanea
DROP TABLE #tmpCustomers
-- Conto quanti sono i record che corrispondo al criterio di filtro impostato dall'utente (per il VirtualItemCount)
SET @SqlStatement = 'SELECT @RC = Count(*) FROM tabCustomers' + @SqlCondition
EXEC sp_executesql @SqlStatement, N'@RC int OUTPUT', @RC = @RecordCount OUTPUT
GO
Come si vede non cambia molto rispetto a prima. Solo che stiamo chiedendo di eseguire:
SELECT idCustomer, CustomerPosition, CustomerName, CustomerEMail, CustomerBirthday, CustomerSex FROM tabCustomers WHERE idCustomer IN (SELECT TOP 20 idCustomer FROM #tmpCustomers WHERE idRecord > (SELECT Max(idRecord) FROM #tmpCustomers WHERE idRecord IN (SELECT TOP 862900 idRecord FROM #tmpCustomers)))
Questa soluzione paga solo se definiamo degli opportuni indici sulla tabella, altrimenti rischiamo di dover attendere diversi secondi il risultato. Inoltre ha senso applicarla solo quando le pagine sulle quali ci si sta muovendo sono di numero basso. Infatti più si sale, più cresce la quantità di record da copiare nella tabella temporanea (ricordatevi di non fare una INSERT INTO sempre di tutti i record, ma solo di quelli minori o uguali a quelli della pagina richiesta dall’utente), penalizzando quindi in modo lineare le prestazioni della query e il carico di lavoro per il server SQL. Il client e la rete non sono assolutamente penalizzati da queste soluzioni.
Paginazione custom con colonna dinamica
L’uso delle tabelle temporanee è mal sopportato da molti, seppur a volte utile. E se provassimo a fondere l’idea del filtro basato sulla chiave primaria e dell’ordinamento personalizzabile? Possiamo inserire nella SELECT una colonna generata dinamicamente in funzione del criterio di ordinamento scelto dall’utente e costruita concatenando la condizione di sorting con la colonna PrimaryKey. In questo modo otteniamo l’univocità della colonna dinamica generata e l’ordinamento secondo le richieste dell’utente. Se indicizziamo correttamente la tabella possiamo avere dei buoni risultati in termini di prestazioni.
CREATE PROCEDURE spListCustomersCustomColumn
(
@CustomerName varchar(30) = '',
@CustomerEMail varchar(50) = '',
@CustomerBirthDayStart datetime = NULL,
@CustomerBirthDayEnd datetime = NULL,
@CustomerSex bit = NULL,
@SortExpression nvarchar(100) = NULL,
@PageSize int = 20,
@CurrentPage int = 1,
@RecordCount int OUTPUT
)
AS
-- Variabili di appoggio
DECLARE @SqlStatement nvarchar(1000)
DECLARE @SqlCondition nvarchar(1000)
DECLARE @CustomColumn nvarchar(100)
DECLARE @Prefix nvarchar(7)
SET @Prefix = ' WHERE '
SET @SqlCondition = ''
SET @CustomColumn = ''
-- Se ho il filtro sul CustomerName
IF (@CustomerName <> '')
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerName LIKE ''%' +
CONVERT(nvarchar(30), REPLACE(@CustomerName, '''', '''''')) + '%'''
SET @Prefix = ' AND '
END
-- Se ho il filtro sul CustomerEMail
IF (@CustomerEMail <> '')
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerEMail LIKE ''%' +
CONVERT(nvarchar(30), REPLACE(@CustomerEMail, '''', '''''')) + '%'''
SET @Prefix = ' AND '
END
-- Se ho il filtro sul CustomerBirthday
IF ((NOT @CustomerBirthDayStart IS NULL) AND (NOT @CustomerBirthDayEnd IS NULL))
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerBirthDay BETWEEN ''' +
CONVERT(nvarchar(30), @CustomerBirthDayStart) + ''' AND ''' +
CONVERT(nvarchar(30), @CustomerBirthDayEnd) + ''''
SET @Prefix = ' AND '
END
-- Se ho il filtro sul CustomerSex
IF (NOT @CustomerSex IS NULL)
BEGIN
SET @SqlCondition = @SqlCondition + @Prefix + ' CustomerSex = ' +
CONVERT(nvarchar(30), @CustomerSex)
END
-- Verifico se devo applicare un ordinamento diverso da quello predefinito
IF (@SortExpression IS NULL)
SET @CustomColumn = 'idCustomer'
ELSE
SET @CustomColumn = '(CONVERT(nvarchar(100), ' + @SortExpression + ') +
CONVERT(nvarchar(10), idCustomer))'
SET @SqlStatement = 'SELECT TOP ' + CONVERT(nvarchar, @PageSize) +
' idCustomer, CustomerName, CustomerEMail, CustomerBirthday, CustomerSex, ' + @CustomColumn + '
AS CustomKey FROM tabCustomers ' + @SqlCondition
IF (@CurrentPage > 1)
SET @SqlStatement = @SqlStatement +
@Prefix + @CustomColumn + ' > (SELECT TOP 1 Max(' + @CustomColumn + ') AS CustomKey
FROM tabCustomers ' + @SqlCondition + @Prefix + @CustomColumn + ' IN ' +
'(SELECT TOP ' + CONVERT(nvarchar, (@PageSize * (@CurrentPage - 1))) + ' ' + @CustomColumn + '
AS CustomKey FROM tabCustomers ' + @SqlCondition + ' ORDER BY CustomKey)' +
' ORDER BY CustomKey) ORDER BY CustomKey'
PRINT @SqlStatement
EXEC sp_executesql @SqlStatement
-- Conto quanti sono i record che corrispondo al criterio di filtro impostato dall'utente (per il VirtualItemCount)
SET @SqlStatement = 'SELECT @RC = Count(*) FROM tabCustomers' + @SqlCondition
EXEC sp_executesql @SqlStatement, N'@RC int OUTPUT', @RC = @RecordCount OUTPUT
GO
Si noti il punto del codice SQL, evidenziato in grassetto, dove si costruisce la colonna dinamica in funzione dell’ordinamento richiesto dall’utente. Si tratta di una semplice concatenazione. Fate attenzione alle colonne di tipo datetime, che per default nel cast su nvarchar forniscono un valore testuale che rende difficile ordinare correttamente i dati. In questo caso il valore del parametro @SortExpression dovrà essere opportunamente modificato per contenere la lista di colonne di ordinamento concatenate e non separate da una virgola.
Per il resto eseguiamo una query abbastanza simile a quella del caso precedente, solo che la chiave con la quale eseguiamo il confronto non è più la Primary Key della tabella temporanea, ma la colonna dinamica generata nella query.
È assolutamente opportuno definire degli indici sulla tabella che comprendano le possibili combinazioni di ordinamento (@SortExpression) sempre seguite da idCustomer, altrimenti rischiamo di avere comunque delle soluzioni poco efficienti.
Questa soluzione si contraddistingue in particolare per il fatto che le sue prestazioni sono abbastanza costanti, a prescindere dal numero di pagina richiesta. Su pagine di numero basso è più costosa della soluzione basata su tabella temporanea, ma salendo il numero di pagina le prestazioni rimangono abbastanza uniformi. Come nel caso della tabella temporanea sia la rete che il client non sono per nulla sovraccaricati di lavoro.
Facciamo del nostro meglio
Una soluzione a volte accettabile è quella che prevede un mix di tutte le soluzioni appena viste. Possiamo infatti costruire una stored procedure che, a seconda della presenza o meno di ordinamenti custom e del numero di pagina richiesto, in funzione del numero complessivo di pagine disponibili, scelga quale tecnica applicare. Eccone un’ipotesi accettabile:
CREATE PROCEDURE spListCustomersForBestPaging
(
@CustomerName varchar(30) = '',
@CustomerEMail varchar(50) = '',
@CustomerBirthDayStart datetime = NULL,
@CustomerBirthDayEnd datetime = NULL,
@CustomerSex bit = NULL,
@SortExpression nvarchar(100) = NULL,
@PageSize int = 20,
@CurrentPage int = 1,
@RecordCount int OUTPUT
)
AS
DECLARE @TotalRecords int
SELECT @TotalRecords = Count(*) FROM tab_Customers
DECLARE @Counter int
-- Se non ho ordinamenti custom
IF (@SortExpression IS NULL)
-- Eseguo la paginazione basata sulla primary key con indice clusterd
EXEC spListCustomersSortedByClusteredIndexedPrimaryKey
@CustomerName, @CustomerEMail, @CustomerBirthDayStart,
@CustomerBirthDayEnd, @CustomerSex, DEFAULT,
@PageSize, @CurrentPage, @Counter OUTPUT
ELSE
BEGIN
-- Se sono sotto 1/3 della dimensione complessiva
-- usa la tabella temporanea
IF (@PageSize * @CurrentPage < @TotalRecords / 3)
EXEC spListCustomersTempTable @CustomerName, @CustomerEMail,
@CustomerBirthDayStart, @CustomerBirthDayEnd,
@CustomerSex, @SortExpression, @PageSize,
@CurrentPage, @Counter OUTPUT
-- Se sono sotto 2/3 della dimensione complessiva
-- usa la colonna custom
ELSE IF (@PageSize * @CurrentPage < @TotalRecords * 2 / 3)
EXEC spListCustomersCustomColumn @CustomerName,
@CustomerEMail, @CustomerBirthDayStart,
@CustomerBirthDayEnd, @CustomerSex,
@SortExpression, @PageSize, @CurrentPage, @Counter OUTPUT
-- Se sono oltre 2/3 della dimensione complessiva
-- uso il cursore server side
ELSE
EXEC spListCustomersServerSideCursor @CustomerName,
@CustomerEMail, @CustomerBirthDayStart, @CustomerBirthDayEnd,
@CustomerSex, @SortExpression, @PageSize,
@CurrentPage, @Counter OUTPUT
END
SET @RecordCount = @Counter
GO
Inoltre per migliorare ulteriormente le prestazioni possiamo preparare, con SQL Server 2000, delle viste indicizzate su misura per le varie modalità di ricerca/ordinamento che vogliamo rendere disponibili. Per esempio, se vogliamo ordinare e filtrare in base alla colonna CustomerEMail possiamo dire a SQL:
CREATE VIEW dbo.vwListCustomersByEmail WITH SCHEMABINDING AS SELECT idCustomer, CustomerName, CustomerEMail, CustomerBirthday, CustomerSex, SysTimestamp FROM dbo.tabCustomers CREATE UNIQUE CLUSTERED INDEX CUSTOMERS_BY_EMAIL_UNIQUE ON vwListCustomersByEmail(CustomerEMail, IdCustomer)
A questo punto per una richiesta di pagina con @SortExpression sulla colonna CustomerEMail avrà senso usare questa vista e non la tabella originale.
Aggirare l’ostacolo: binding + indici di pagina in cache
Un’altra possibile strada che possiamo percorrere è quella di utilizzare un controllo per il DataBinding, che non sia a questo punto necessariamente una DataGrid ma che sia configurato per permetterci di spostarci tra le pagine, avendo già a disposizione, magari perché l’abbiamo conservato in cache, il valore dell’indice del primo elemento di ciascuna pagina. In questo modo potremo chiedere di avere i primi N (dove al solito N è la dimensione di pagina) record con primary Key maggiore o uguale a quella che abbiamo letto dalla cache. Per lavorare in questo modo, però, dovremo costruirci una cache di indici di pagina che dovremo mantenere in memoria, associandola ai diversi criteri di filtro e ordinamento scelti dall’utente. Come sempre poi queste tecniche e applicazioni dipendono fortemente dal contesto di esecuzione del codice. Se siamo in un'applicazione web non è detto per esempio che abbia senso fare caching, per ogni singolo utente, di una lista di chiavi di lookup su pagine. Se si tratta invece di un'applicazione Windows dove ogni utente può avere sul proprio PC le chiavi in cache il discorso cambia. Tutte queste valutazioni spettano comunque a noi nei momenti in cui implementiamo le applicazioni e non è possibile identificare delle soluzioni valide sempre e a priori.
Cosa ci riserva il futuro? La paginazione con Whidbey
Avrete già sicuramente sentito che con la prossima versione del Framework .NET, quella attualmente distribuita insieme a Visual Studio .NET Whidbey, ci sarà la possibilità di eseguire delle query, tramite gli oggetti Command, cha potranno dare come risultato una particolare pagina. Si parla del metodo ExecutePageReader, che restituisce al chiamante un DataReader, pronto per eseguire del DataBinding efficiente senza dover utilizzare un DataSet, che sicuramente sarebbe più pesante del DataReader, in questo contesto.
SqlConnection cn =
new SqlConnection("server=paolo2k3;database=DotNetDemos;uid=sa;password=bip;");
SqlCommand cmd = new SqlCommand("spListAllCustomers", cn);
cmd.CommandType = CommandType.StoredProcedure;
using (cn)
{
cn.Open();
SqlDataReader dr = cmd.ExecutePageReader(CommandBehavior.CloseConnection,
100, 20);
while (dr.Read())
{
Console.WriteLine("{0} - {1} - {2}", dr[0], dr[1], dr[2]);
}
}
Come funziona questo comando? Se proviamo a tracciare con il SQL Profiler i comandi inviati vedremo che sono:
exec sp_cursoropen @cursorhandle = @P1 output, @stmt = N'spListAllCustomers', @scrollopt = @P2 output, @ccopt = @P3 output, @rowcount = @P4 output exec sp_cursorfetch @cursorhandle = @P1, @fetchtype = 32, @rownum = 101, @nrows = 20
In pratica si ricade nello scenario del cursore lato server già visto. Non c’è quindi la necessità di aspettare Whidbey per paginare i record e forse la soluzione adottata in Whidbey non è la soluzione vincente.
In conclusione, penso che l’approccio migliore al problema sia tenere presenti tutte le possibili soluzioni e, a seconda dei casi, applicare quelle più adatte, in funzione del numero di record, del tipo di filtro, della locazione del client del database, della pagina richiesta e della sua dimensione, ecc. Come ho detto in principio, non credo che per questo problema esista LA soluzione, piuttosto esistono DELLE soluzioni, da applicare di volta in volta, a seconda dei casi.
Ok. Se hai letto fino a qui ... complimenti per la pazienza e facci sapere che sei arrivato fino a qui (Sul serio! Non barare :-)!). Ci serve come test per capire quanta pazienza hanno i nostri lettori.