Gestire la paginazione dei dati con SQL Server
Ho trovato interessante l'articolo di Lorenzo Braidi sulla gestione della paginazione dei dati presente nel numero di novembre 2004 di Computer Programming. In particolare si spiega come riuscire ad estrarre da una tabella contenente una grande mole di dati un numero di record esattamente pari ad una prefissata dimensione di pagina.
Nel caso di SQL Server il trucco sta nell'annidare tre query una dentro all'altra:
- la prima query estrae tutti i record fino al limite superiore filtrandoli e ordinandoli secondo quanto richiesto dall'applicazione;
- la seconda query prende il resultset della prima, ordinato in maniera decrescente, limitatamente ai record da inserire nella pagina;
- la terza query ripristina l'ordinamento della prima query sui record della pagina.
La query complessiva dovrebbe pertanto avere una forma di questo tipo:
SELECT TOP [dimPage] [field1], [field2], ... FROM (SELECT TOP [dimPage] [field1], [field2], ... FROM (SELECT TOP ([dimPage] * [numPage]) [field1], [field2], ... FROM [table] WHERE [conditions] ORDER BY [order] ASC) AS TMP1 ORDER BY [order] DESC) AS TMP2 ORDER BY [order] ASC
TrackBack scrive: 
TrackBack scrive:
lorenzobraidi scrive:
Gestire la paginazione dei dati con SQL Server -
Innanzitutto, sono lusingato per l'attenzione che avete prestato all'articolo pubblicato su CP 140 :-)
Cerco di rispondere ad alcuni dei commenti che ho letto...
Per ottenere il numero totale dei record che verrebbero estratti dalla query, non c'è altra strada se non quella di eseguire una select count preventivamente.
Tra l'altro - se leggete l'intero articolo è scritto anche li - la selezione preventiva per ottenere il numero totale dei record è da fare anche per un altro motivo.
Se non sappiamo quanti siano i record, infatti, non ci accorgeremmo mai di essere arrivati alla fine del nostro resultset! Per come è strutturata la query, infatti, all'aumentare del numero della pagina che si vuole estrarre, si otterrebbe sempre l'ultima.
Se per esempio la nostra tabella avesse 100 record e noi li estraessimo in pagine da 20 ciascuna, le prime 5 pagine sarebbero corrette. Nel momento in cui estraessimo la sesta, però, otterremmo nuovamente i dati presenti nella quinta...
Un altro commento, poi, proponeva una soluzione alternativa che condivido.
La soluzione, in pratica, prevede di estrarre i record appartenenti alle pagine che si vogliono saltare per poi eseguire una query NOT IN che va a prendere i primi record successivi a quelli identificati in precedenza.
La soluzione, però, è efficace solo se si utilizza come criterio di ordinamento la chiave primaria della tabella o uno o più campi che ne costituiscano un indice univoco.
Grazie comunque per l'attenzione e l'interesse
Lorenzo Braidi
lorenzo__@__braidi.com (eliminare i __)
http:\\www.braidi.com
Innanzitutto, sono lusingato per l'attenzione che avete prestato all'articolo pubblicato su CP 140 :-)
Cerco di rispondere ad alcuni dei commenti che ho letto...
Per ottenere il numero totale dei record che verrebbero estratti dalla query, non c'è altra strada se non quella di eseguire una select count preventivamente.
Tra l'altro - se leggete l'intero articolo è scritto anche li - la selezione preventiva per ottenere il numero totale dei record è da fare anche per un altro motivo.
Se non sappiamo quanti siano i record, infatti, non ci accorgeremmo mai di essere arrivati alla fine del nostro resultset! Per come è strutturata la query, infatti, all'aumentare del numero della pagina che si vuole estrarre, si otterrebbe sempre l'ultima.
Se per esempio la nostra tabella avesse 100 record e noi li estraessimo in pagine da 20 ciascuna, le prime 5 pagine sarebbero corrette. Nel momento in cui estraessimo la sesta, però, otterremmo nuovamente i dati presenti nella quinta...
Un altro commento, poi, proponeva una soluzione alternativa che condivido.
La soluzione, in pratica, prevede di estrarre i record appartenenti alle pagine che si vogliono saltare per poi eseguire una query NOT IN che va a prendere i primi record successivi a quelli identificati in precedenza.
La soluzione, però, è efficace solo se si utilizza come criterio di ordinamento la chiave primaria della tabella o uno o più campi che ne costituiscano un indice univoco.
Grazie comunque per l'attenzione e l'interesse
Lorenzo Braidi
lorenzo__@__braidi.com (eliminare i __)
http:\\www.braidi.com
»»»» rickyvr scrive:
Re: Gestire la paginazione dei dati con SQL Server -
Lorenzo, grazie a te per le puntualizzazioni...
Chi meglio dell'autore può spiegare e approfondire al meglio i concetti esposti nell'articolo?!?!
Ciao, Ricky.
Lorenzo, grazie a te per le puntualizzazioni...
Chi meglio dell'autore può spiegare e approfondire al meglio i concetti esposti nell'articolo?!?!
Ciao, Ricky.
andrewz scrive:
Gestire la paginazione dei dati con SQL Server -
E se volessi sapere contemporaneamente quanti record totali sono restituiti dalla query?
Ciao!
E se volessi sapere contemporaneamente quanti record totali sono restituiti dalla query?
Ciao!
Daniele Bochicchio scrive:
Re: Gestire la paginazione dei dati con SQL Server -
usi una SP con output parameter come faccio io nel 99% dei casi, usando una tab temporanea
imho il codice della SP è sicuramente più leggibile e diventa difficile fare errori, oltre che più semplice manutenerla.
usi una SP con output parameter come faccio io nel 99% dei casi, usando una tab temporanea
imho il codice della SP è sicuramente più leggibile e diventa difficile fare errori, oltre che più semplice manutenerla.
»»»» rickyvr scrive:
Re: Gestire la paginazione dei dati con SQL Server -
La query restituisce al massimo [dimPage] records.
Cosa intendi per record totali?
Ciao, Ricky.
La query restituisce al massimo [dimPage] records.
Cosa intendi per record totali?
Ciao, Ricky.
Daniele Bochicchio scrive:
Re: Gestire la paginazione dei dati con SQL Server -
quelli che la query darebbe come totale.
se fai la paginazione, devi pur sapere fino a quale pagina arriva...
quelli che la query darebbe come totale.
se fai la paginazione, devi pur sapere fino a quale pagina arriva...
andrewz scrive:
Re: Gestire la paginazione dei dati con SQL Server -
Ora mi avete fatto venire un dubbio: è meglio l'utilizzo della tabella temporanea oppure l'esecuzione di due query, una per il conteggio e la seguente con l'uso del TOP per avere solo i record necessari? (Io ho sempre preferito la tabella temporanea).
Forse che l'uso della tabella temporanea pregiudica le prestazioni con query che restituiscono centinaia di migliaia (se non milioni) di record?
Ciao
Ora mi avete fatto venire un dubbio: è meglio l'utilizzo della tabella temporanea oppure l'esecuzione di due query, una per il conteggio e la seguente con l'uso del TOP per avere solo i record necessari? (Io ho sempre preferito la tabella temporanea).
Forse che l'uso della tabella temporanea pregiudica le prestazioni con query che restituiscono centinaia di migliaia (se non milioni) di record?
Ciao
»»»» rickyvr scrive:
Re: Gestire la paginazione dei dati con SQL Server -
Sull'articolo di Lorenzo Braidi su CP sono riportati i grafici con le prestazioni relative ad una simulazione con 1000 pagine da 20 righe ciascuna. Si parla di un tempo medio di risposta compreso tra i 300 e i 400 ms. Per le prime 20/30 pagine si parla addirittura di tempi inferiori al decimo di secondo. Si può inoltre rilevare un progressivo deterioramento delle prestazioni di tipo esponenziale a partire dalla novantesima pagina circa. Questi tempi sono addirittura migliorabili introducendo un indice sulla colonna di ordinamento dei dati. Si parla di tempi inferiori al decimo o poco più (sotto i 50 ms per le prime 350/400 estrazioni) usando l'indice.
Ciao, Ricky.
Sull'articolo di Lorenzo Braidi su CP sono riportati i grafici con le prestazioni relative ad una simulazione con 1000 pagine da 20 righe ciascuna. Si parla di un tempo medio di risposta compreso tra i 300 e i 400 ms. Per le prime 20/30 pagine si parla addirittura di tempi inferiori al decimo di secondo. Si può inoltre rilevare un progressivo deterioramento delle prestazioni di tipo esponenziale a partire dalla novantesima pagina circa. Questi tempi sono addirittura migliorabili introducendo un indice sulla colonna di ordinamento dei dati. Si parla di tempi inferiori al decimo o poco più (sotto i 50 ms per le prime 350/400 estrazioni) usando l'indice.
Ciao, Ricky.
Daniele Bochicchio scrive:
Re: Gestire la paginazione dei dati con SQL Server -
guarda, la paginazione su questo sito è fatta con una tab temporanea, che è popolata (a seconda della ricerca) da altre 3-4 SP che eseguono query full text.
la tabella temporanea ha ovviamente un paio di colonne indicizzate e viene ordinata in base al rank, quindi vengono estratti i dati contenuti all'interno.
ci sono un po' di ottimizzazioni qua e la', ma direi che come puoi vedere tu stesso facendo una ricerca anche banale (tipo ASP) le prestazioni sono ottime. per il resto, ricky ha aggiunto un po' di dettagli
guarda, la paginazione su questo sito è fatta con una tab temporanea, che è popolata (a seconda della ricerca) da altre 3-4 SP che eseguono query full text.
la tabella temporanea ha ovviamente un paio di colonne indicizzate e viene ordinata in base al rank, quindi vengono estratti i dati contenuti all'interno.
ci sono un po' di ottimizzazioni qua e la', ma direi che come puoi vedere tu stesso facendo una ricerca anche banale (tipo ASP) le prestazioni sono ottime. per il resto, ricky ha aggiunto un po' di dettagli
ITHost scrive:
Gestire la paginazione dei dati con SQL Server -
prova a dare un occhio alla simulazione della clausula LIMIT in sql server e avrai lo stesso risultato
http://blogs.aspitalia.com/cp/post520.aspx
prova a dare un occhio alla simulazione della clausula LIMIT in sql server e avrai lo stesso risultato
http://blogs.aspitalia.com/cp/post520.aspx
Aggiungi un nuovo commento »»»
Per inserire un commento, devi registrarti alla nostra community.
Nella stessa categoria
I più letti del mese
Stampa 
Download 
10annidi.ASPItalia.com: iscriviti alla competizione e vinci fantastici premi ogni mese!
mi sorprendevo come in tutti gli articoli che ho letto non si è mai occupato di sapere quante righe effettivamente tornasse la query senza filtro :D
questo è un modo per avere il numero totale di righe senza riscrivere effettivamente la query due volte:
SELECT *,
ROW_NUMBER() OVER (ORDER BY field) as num
into #tmp
FROM tbl
SELECT @count = count(0) FROM #tmp
SELECT * from #tmp
WHERE num BETWEEN @min AND @max
Continua »»» | Rispondi »»»