MS SQL užklausų rezultatų puslapiavimas

Daugeliu atvejų vienu metu mums tereikia tik dalies duomenų. Vienas toks atvejų – internetiniai sprendimai.

Šis klausimas turi dominti programuotojus, norinčius derinti paprastumą su greitaveika. Paprasčiausi sprendimai nėra efektyviausi, nes ištraukiami visi įrašai prieš atmetant nereikalingus, o patys efektyviausi visus veiksmus atlieka serveryje naudojant sudėtingą kodą.

MS SQL 2005 įtraukta funkcija ROW_NUMBER suteikia galimybę efektyviai apriboti rezultato imtį.

Daugelis populiarių duomenų bazių valdymo sistemų pateikia funkcijas, leidžiančias riboti užklausoje gražinamų eilučių kiekį. Pvz., MySQL turi LIMIT patikslą. Jos pirmasis parametras nurodo, nuo kurios eilutės (pradedant 0) pateikti rezultatą, o antrasis – kiek eilučių pateikti. Pvz.,

SELECT * FROM LENTA LIMIT 20, 13

Ji gražins 20-32 eilutes – arba tiek, kiek yra virš 20-os, jei jų mažiau nei 32. Jei lentelėje mažiau nei 20 įrašų, atsakymas bus tuščias.

Tokios galimybės nenumato MS SQL, tačiau ji turi savų triukų. Pvz., CLR procedūrų galimybė leidžia puslapiavimui panaudoti VB.Net arba C# kodą, vykdomą serverio aplinkoje. Tačiau CLR procedūros nėra tokios efektyvios kaip Transact SQL, tad TSQL panaudojimas labiau praktiškas.

ROW_NUMBER

ROW_NUMBER, grubiai tariant, kiekvienam gražinamam įrašui suteikia eilės numerį (1, 2, …). Sudėtinėse užklausose tai galima naudingai panaudoti.

Tarkim turime lentelę ALGOS su laukais ASMUO, ALGA

Tačiau serveris turi žinoti, kaip reikia rūšiuoti duomenis, todėl po ROW_NUMBER turi sekti OVER funkcija, pvz.,

SELECT ROWNUM() OVER (ORDER BY ASMUO) AS nr, ASMUO, ALGA FROM ALGOS

Ši užklausa išduos pagal lauką ASMUO surūšiuotą lentelę kartu su eilės numeriais (pradedant 1). Tad jei norime apriboti pateikiamų duomenų kiekį, galime naudoti sudėtinę užklausą, ribojančią įrašus pagal eilės numerius, pvz., pirmųjų 10 įrašų pateikimui:

SELECT ASMUO, ALGA FROM (
    SELECT ROWNUM() OVER (ORDER BY ASMUO) AS nr, ASMUO, ALGA FROM ALGOS
) WHERE nr <= 10

Tad dabar galime parašyti MS SQL procedūrą, atitinkančią MySQL LIMIT: <

CREATE PROCEDURE [dbo].[pageAlgos]
   @start int = 1,
   @maxct int =1,
   @sort nvarchar(200)
AS
   SET NOCOUNT ON
   DECLARE
       @STMT nvarchar(max),
       @ubound int

    IF @start < 1 SET @start = 1
    IF @maxct < 1 SET @maxct = 1
    SET @ubound = @start + maxct

    SET @STMT = ‘SELECT asmuo, alga FROM (‘ +
        ‘SELECT ROWNUM() OVER (ORDER BY ‘ + @sort + ‘) AS nr, ASMUO, ALGA FROM ALGOS‘ +
        ‘) WHERE nr >= ‘ + CONVERT(varchar(9), @start) +
        ‘ AND nr <’ + CONVERT(varchar(9), @ubound)

   EXEC (@STMT)

Procedūra prasideda SET NOCOUNT ON, išjungiančią įrašų skaičiavimo pranešimą (bendra užklauso optimizavimo technika). Tada kintamajame @STMT suformuojama užklausa, kuri galiausiai įvykdoma.

Šios procedūros iškvietimo pavyzdys (pateikiant 11-20 įrašus rūšiavus pagal algų didumą):

pageAlgos 11, 20, ‘ALGA’

Dabar jau galime sukurti apibendrintą procedūrą, kuriai pateikiama ir duomenų šaltinis (lentelė), ir laukai, ir filtravimo sąlyga, ir puslapiavimo duomenys:

CREATE PROCEDURE [dbo].[PagingSource] 
@datasrc nvarchar(200) 
,@orderBy nvarchar(200) 
,@fieldlist nvarchar(200) = '*' 
,@filter nvarchar(200) = '' 
,@pageNum int = 1 
,@pageSize int = NULL 
AS 
SET NOCOUNT ON 
DECLARE 
@STMT nvarchar(max),@recct int –- bendras įrašų skaičius 
 
IF LTRIM(RTRIM(@filter)) = '' SET @filter = '1 = 1' 
IF @pageSize IS NULL BEGIN 
SET @STMT = 'SELECT ' + @fieldlist +  
'FROM ' + @datasrc + 
'WHERE ' + @filter +  
'ORDER BY ' + @orderBy 
EXEC (@STMT) 
END ELSE BEGIN 
SET @STMT = 'SELECT @recct = COUNT(*) 
FROM ' + @datasrc + ' 
WHERE ' + @filter 
EXEC sp_executeSQL @STMT, @params = N'@recct INT OUTPUT', @recct = @recct 
OUTPUT 
SELECT @recct AS recct – bendras įrašų skaičius 
 
DECLARE 
@lbound int, 
@ubound int 
 
SET @pageNum = ABS(@pageNum) 
SET @pageSize = ABS(@pageSize) 
IF @pageNum < 1 SET @pageNum = 1 
IF @pageSize < 1 SET @pageSize = 1 
SET @lbound = ((@pageNum - 1) * @pageSize) 
SET @ubound = @lbound + @pageSize + 1 
IF @lbound >= @recct BEGIN 
SET @ubound = @recct + 1 
SET @lbound = @ubound - (@pageSize + 1) –- pask.psl., jei nėra įrašų 
nurodytame puslapyje
END 
SET @STMT = 'SELECT ' + @fieldlist + ' 
FROM ( 
SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, * 
FROM ' + @datasrc + ' 
WHERE ' + @filter + ' 
) AS tbl 
WHERE 
row > ' + CONVERT(varchar(9), @lbound) + ' AND 
row < ' + CONVERT(varchar(9), @ubound) 
EXEC (@STMT)  
END

Jei šiai procedūrai nenurodytas @pageSize, puslapiavimas neatliekamas ir gražinama, tačiau negražinamas ir ištrauktų įrašų kiekis.

Procedūros panaudojimas toks (pateikiant trečią puslapį, kai puslapio dydis – 10 įrašų):

PagingSource ‘ALGOS‘, ‘ASMUO‘, ‘*‘, ‘ALGA > 2500‘, 3, 10

Ši užklausa gražins du rezultatus – vieną, kuriame recct bus bendras visų įrašų kiekis, o kitame – trečio puslapio duomenys.

Ankstesnės "Advanced HTML" skyrelio temos:
Tcl kalba
Minčių schema
Programavimas Unix aplinkoje
Unix komandinės eilutė
Pitonas, kandantis sau uodegą
AWK kalba - sena ir nuolat aktuali
WebService naudojimas Asp.Net aplinkoje
Daugiau apie WebService
CGI.pm biblioteka: sausainiai
Kaip valdyti piešinių pakrovimo tvarką
Dygios JavaScript eilutės
Debesies architektūra
Tiesa apie REST
Ateities kalbos?
Ruby on Rails