Paging is een mechanisme dat de performance van applicaties kan verbeteren door grote hoeveelheden data in kleine stukjes (pagina’s) op te delen en deze één voor één te tonen. Op deze manier hoeven er veel minder gegevens tussen cliënt en server uitgewisseld te worden. Tegenwoordig is dit vrij eenvoudig te realiseren met bijvoorbeeld ASP.NET.
Echter, alle data wordt wel uit SQL Server gelezen. Als een enkele query vele duizenden rijen kan opleveren, dan wordt het ook belangrijk om een efficiënt paging-algoritme te implementeren in de database server. SQL Server 2000 biedt hiervoor echter weinig ondersteuning, in tegenstelling tot zijn opvolger SQL Server Yukon die hiervoor wel standaard functionaliteit bevat. Voor iedereen die niet kan wachten op deze volgende versie, die waarschijnlijk pas in 2005 uit komt, beschrijf ik in dit artikel een efficiënt paging-algoritme.
Dit algoritme biedt ook bij grote databases met miljoenen rijen een goede performance. Met behulp van dit algoritme kan de gebruiker zelf de paginagrootte en de sorteervolgorde bepalen, en ook filterexpressies toepassen.
Paging in de database
Een effectief paging-mechanisme leest alleen de rijen uit het zoekresultaat die nodig zijn. Als de gebruiker nu pagina 100 wil zien, met een paginagrootte van 10 rijen, hoe zorg je er dan voor dat de SQL Server alleen die 10 rijen opvraagt? Met andere woorden: hoe kun je beginnen met lezen bij rij 1000 en stoppen bij rij 1010?
Ik zal eerst beschrijven hoe het paging-algoritme rij 1000 identificeert. Daarna beschrijf ik hoe het algoritme begint met inlezen vanaf rij 1000. Tenslotte komt aan bod hoe het algoritme stopt met inlezen bij rij 1010.
Het identificeren van rij 1000
Om rij 1000 te kunnen vinden moet je hem uniek kunnen identificeren binnen de sorteervolgorde. Als het algoritme niet kan bepalen dat rij 999 voor rij 1000 komt, dan kan het ook niet beginnen met inlezen vanaf rij 1000.
Figuur 1 toont een voorbeeld hiervan in een fictieve tabel ‘Persoon’; alle voorbeelden in dit artikel maken daar gebruik van. Uit figuur 1 blijkt, dat als er alleen wordt gesorteerd op de niet unieke kolom ‘Naam’, het niet mogelijk is om rij 1000 te onderscheiden van rij 999.
|
Rijnummer |
Naam |
|
997 |
Jan |
|
998 |
Jans |
|
999 |
Janssen |
|
1000 |
Janssen |
|
1001 |
Janssen |
|
1002 |
Janz |
|
1003 |
Janzen |
Fig. 1: Rij 1000 niet uniek te identificeren in sorteervolgorde
De oplossing hiervoor is het uniek maken van elke rij binnen de sorteervolgorde, door als laatste kolom in de sorteervolgorde de primary key (PersoonID) op te nemen. Het voorbeeld in figuur 2 laat deze zelfde tabel zien, maar dan gesorteerd op naam en PersoonID.
|
Rijnummer |
Naam |
PersoonID |
|
997 |
Jan |
90 |
|
998 |
Jans |
714 |
|
999 |
Janssen |
35 |
|
1000 |
Janssen |
36 |
|
1001 |
Janssen |
37 |
|
1002 |
Janz |
1 |
|
1003 |
Janzen |
23 |
Fig. 2: Rij 1000 te identificeren aan Naam, PrimaryKey combinatie
Het vinden van rij 1000
Nu we rij 1000 kunnen identificeren, moeten we deze rij op een efficiënte manier kunnen vinden. Figuur 3 toont een query die de naam en het persoonid van de 1000e persoon in 2 variabelen leest.
DECLARE @Naam VARCHAR(50)
DECLARE @PersoonID INT
-- Selecteer de naam en het persoonid van de 1000e persoon uit de
-- lijst van personen, gesorteerd op naam en persoonid.
SELECT TOP 1000 @Naam = Naam, @PersoonID = PersoonID
FROM Persoon
ORDER BY Naam, PersoonID
Fig. 3: Query voor het vinden van de eerste rij
Omdat de gegevens in de tabel ‘Persoon’ niet fysiek gesorteerd zijn op naam, moet de query uit figuur 3 eerst de complete tabel doorzoeken, voordat hij kan bepalen wat de 1000e persoon is. Dit kunnen we voorkomen door op een slimme manier een index te plaatsen. Vergelijk het maar met de inhoudsopgave van een boek. Doordat je niet het hele boek hoeft door te bladeren, kun je veel sneller vinden wat je zoekt.
De query uit figuur 3 maakt alleen gebruik van de kolommen ‘Naam’ en ‘PersoonID’. Als we dus een index plaatsen op deze kolommen, hoeft SQL Server alleen maar in de index te zoeken, zonder de complete tabel te hoeven raadplegen. Dit wordt een ‘covering index’ genoemd, aangezien alle kolommen uit het select statement voorkomen in de index.
Helaas kun je geen variabele gebruiken bij het TOP keyword, om aan te geven hoeveel rijen je wilt ophalen. Daarom zul je de bovenstaande query in een string moeten stoppen en uitvoeren mbv ‘sp_executesql’ (Zie figuur 6 voor een uitgewerkt voorbeeld).
Het inlezen van rij 1000 tot en met rij 1010
Nu we weten dat rij 1000 begint met de naam ‘Janssen’ en PersoonID ‘36’ (zie figuur 2), kunnen we beginnen met het ophalen van het zoekresultaat. We willen dus alle personen inlezen waarvan de naam gelijk is aan ‘Janssen’ en het persoonID groter dan ‘36’ of waarvan de naam groter is dan ‘Janssen’. Figuur 4 toont hiervan een voorbeeld.
-- Selecteer alle gegevens van alle personen
SELECT *
FROM PERSOON
-- waarvoor geldt dat de naam gelijk is aan ‘Janssen’
-- en het persoonid >= 36
WHERE (Naam = @Naam AND PersoonID >= @PersoonID)
-- of waar de naam groter is dan ‘Janssen’
OR (Naam > @Naam)
ORDER BY Naam, PersoonID
Fig. 4: Query voor het ophalen van alle rijen
Stoppen met lezen bij rij 1010
De query uit figuur 4 haalt nu alles vanaf rij 1000 op, maar stopt nog niet met lezen bij rij 1010. Je zou dit kunnen doen door het statement ’SET ROWCOUNT 10’ voor de query te plaatsen. Het nadeel hiervan is alleen, dat SQL Server intern toch alle rijen ophaalt, om er uiteindelijk maar 10 terug te geven.
Je kunt SQL Server na 10 rijen ook laten stoppen met lezen door het statement ‘Top 10’ toe te voegen aan de query. Dit werkt alleen als je een vaste paginagrootte wilt gebruiken, want je kunt geen variabele gebruiken om aan te geven na hoeveel rijen SQL Server moet stoppen met lezen.
Je kunt een variabele paginagrootte maken door het gebruik van ‘SET ROWCOUNT’ en ‘TOP’ te combineren. Met behulp van ‘SET ROWCOUNT @Pagesize’ definieer je de paginagrootte. Om te voorkomen dat SQL Server intern veel te veel rijen gaat inlezen, zet je een maximale paginagrootte met behulp van het ‘TOP’ statement. Zie figuur 6 voor een uitgewerkt voorbeeld hiervan.
Filteren
Het paging-algoritme is ook uit te breiden met filtermogelijkheden. Je wilt bijvoorbeeld alle personen ophalen waarvan het geboortejaar 1980 is. Om dit te realiseren moet je de volgende stappen ondernemen:
1. Breid de covering index uit met de kolommen waarop gefilterd moet kunnen worden. De volgorde van de index is dan: (Filterkolommen, Sorteerkolommen, PrimaryKey kolom)
2. De queries zullen moeten worden uitgebreid met een filterexpressie. Zie figuur 5 voor een voorbeeld.
-- Als er een filter is opgegeven, filter dan op geboortejaar.
WHERE @Geboortejaar IS NULL OR Geboortejaar = @Geboortejaar
Fig. 5: Optioneel filter
Andere sorteervolgordes
Natuurlijk wil je het resultaat niet alleen oplopend gesorteerd hebben op naam, maar wil je ook andere sorteervolgorden kunnen bieden. Helaas zul je elk sorteerscenario moeten uitprogrammeren in aparte queries.
Als je alleen de sorteervolgorde wilt omdraaien hoef je echter geen nieuwe index aan te maken. SQL Server kan een oplopend gesorteerde index ook gebruiken voor aflopende sorteervolgorde. Hij begint gewoon vanaf de andere kant van de index te lezen.
Als je op andere kolommen wilt sorteren, moet je voor elk scenario een covering index aanmaken.
Het uitgewerkte voorbeeld
/*
* Pagingmechanisme voor ophalen van persoonsgegevens,
* oplopend gesorteerd op naam, PersoonID
*/
CREATE PROCEDURE spSelectPersoon
(
@PaginaNummer INT, -- Nummer van de op te halen pagina
@PaginaGrootte INT, -- Grootte van de pagina
@Geboortejaar INT DEFAULT NULL, -- Optioneel filter op geboortejaar
@AantalPaginas INT OUTPUT -- Het totaal aantal pagina's
)
AS
BEGIN
DECLARE @Naam VARCHAR(50)
DECLARE @PersoonID INT
DECLARE @RijNummer INT
DECLARE @StartRijQuery NVARCHAR(1024)
-- Bepaal het nummer van de eerste rij die opgevraagd moet worden
SET @RijNummer = (@PaginaNummer - 1 * @PaginaGrootte) + 1
-- Bouw in een string de query die de gegevens
-- van de eerste rij ophaalt
SET @StartRijQuery = CONVERT(NVARCHAR(1024),
'SELECT TOP ' + CAST(@RijNummer as VARCHAR(10)) + '
@Naam = Naam,
@PersoonID = PersoonID
FROM Persoon
WHERE GeboorteJaar = @GeboorteJaar OR @Geboortejaar IS NULL
ORDER BY Naam, PersoonID')
-- Haal de gegevens van de eerste rij op
EXEC SP_EXECUTESQL @StartRijQuery,
N'@Geboortejaar INT
, @Naam VARCHAR(50) OUTPUT
, @PersoonID INT OUTPUT',
@Naam,
@PersoonID
-- Bepaal het aantal pagina's
SELECT @AantalPaginas = (COUNT(1) / @PaginaGrootte)
FROM Persoon
WHERE Geboortejaar = @Geboortejaar OR @Geboortejaar IS NULL
-- Zet de paginagrootte, zodat de volgende
-- query dit aantal rijen ophaalt
SET ROWCOUNT @PaginaGrootte
-- Selecteer alle gegevens van alle personen
-- Waarvoor geld dat de naam gelijk is aan ‘Janssen’
-- En het persoonid >= 36
-- Of waar de naam groter is dan ‘Janssen’
SELECT TOP 100 *
FROM PERSOON
WHERE ((Geboortejaar = @Geboortejaar OR @Geboortejaar IS NULL)
AND (Naam = @Naam AND PersoonID >= @PersoonID) OR (Naam >