március 2014 havi bejegyzések

Lapozzunk okosan – Nonunique oszlopok problémája

Az előző postomban írtam a hagyományos lapozás erőforrásigény problémájáról. Ott a megoldás az volt, hogy kellett keresni egy unique oszlopot, ami segítségével lehet készíteni egy saját, hatékonyabb lapozó eljárást. Viszont vannak olyan esetek, amikor az oszlop, ami szerint lapozni akarunk, az nem egyedi. Itt nincs meg az a trükk, hogy visszaadjuk az utoljára olvasott “id”-t. Vagy mégis? 🙂

Vegyünk a korábbi példát. Van egy táblánk: id, name, email és legyen benne néhány adatunk, amit lapozni szeretnénk név szerint és mivel kicsiben játszunk, így csak 10-es kötegekben:

01_resultset

Pirossal vonallal bejelöltem, hogy milyen szeletekben szeretnénk visszakapni az eredményt. A hagyományos lapozás jelen esetben megint kézenfekvő megoldás, hiszen ORDER BY name, id offset x rows fetch next 10 rows only és vígan lapozunk, csak éppen egyre teljesítményigényesebben, ahogy haladunk előre a listában.

A fenti esetre két alternatívám van:

1.) Lapozzunk okosan: Itt megintcsak visszaadjuk az utoljára olvasott értékeket. Csak azokat a sorokat adjuk vissza, ahol a name nagyobb a visszakapott name értékénél vagy a name egyenlő a visszakapott name értékével, de az id az nagyobb a visszakapot értéktől.

2.) Lapozzunk kompromisszumokkal: Felülvizsgálhatjuk, hogy biztos 10 értékre van e szükségünk. Pl: Ha lapozással jelenítünk meg adatokat a felhasználóknak, akkor biztosan annyit kell megjeleníteni, mert azt mondja a specifikáció, így marad az előbbi módszer, de ha pl.: egy feldolgozót akarunk készíteni, ami 10-esével dolgoz fel adatokat, ott már lehet kicsit csalni és a TOP 10 with ties parancsot használni, ami kb. 10-et fog adni, de mivel “magunknak” dolgozunk, erre fel vagyunk készülve.

Nézzünk két demó scriptet, a fenti két módszerre.

1.) Készítsünk adatokat:

use tempdb
go

if object_id('tempdb..#tmp') is not null
drop table #tmp
go

create table #tmp (
    id int identity(1,1) primary key,
    name nvarchar(50) not null,
    email nvarchar(100) not null
)
go

create index IX_name on #tmp (name) include (email)
go

declare @i tinyint = 0

while (@i < 3)
begin
    insert into #tmp (name, email)
    select top 100
        concat('Name', row_number() over(order by (select null))) as name,
        concat('Name', row_number() over(order by (select null)), '@domain.valami') as email
    from sys.columns c

    set @i += 1;
end

2.) Nézzük a query-ket:

Módszer 1, első lap:


declare
    @last_name nvarchar(50) = '',
    @last_id int = -1

select top 10
    id, name, email
from #tmp
where name > @last_name
    or (name = @last_name and id > @last_id)
order by name

Módszer 1, második lap:

declare
    @last_name nvarchar(50) = 'Name11',
    @last_id int = 11

select top 10
    id, name, email
from #tmp
where name > @last_name
    or (name = @last_name and id > @last_id)
order by name

Módszer 2, első lap:


declare @last_name nvarchar(50) = ''

select top 10 with ties
    id, name, email
from #tmp
where name > @last_name
order by name

Módszer 2, második lap:


declare @last_name nvarchar(50) = 'Name11'

select top 10 with ties
    id, name, email
from #tmp
where name > @last_name
order by name

Ezek egytáblás, egyszerű lekérdezések voltak, de jól szemléltetik a lehetőségeket.