február 2014 havi bejegyzések

Lapozzunk okosan

A lapozást szeretjük, mert kevésbé erőforrás igényes, mintha egyben kérnénk el a teljes eredményhalmazt. Az SQL Server 2012-től megjelent az offset, ami az order by része és segítségével egyszerűen lapozhatóvá tehetjük a meglévő lekérdezéseinket. Sokan használjuk, nagyszerű cause, viszont időnként jobban járunk, ha más utat választunk a lapozás megoldására. Tipikus példa amikor egy táblát kell egyoszlopos elsődleges kulcs szerint lapozva végigjárni.

Vegyünk egy egyszerű példát.

Van egy táblánk 3 oszloppal (id, név, email), ahol az “id” az elsődleges kulcs és egy növekvő számsorozat. Legyen a táblában 5.000 sor, a feladat pedig, hogy email-eket küldünk az email oszlopban lévő címekre 1.000-es kötegekben. Vizuálisan valahogy így szemléltetném:

045_tabla_rajz

Jelen esetben a offset-es lapozás kényelmes és kézre eső, de kevésbé hatékony. Miért? Mert ahogy haladunk előre a lapozással, úgy fokozatosan nő a lekérdezés költsége. Ha egyszerűen akarnám modellezni akkor azt mondanám, hogy az első szelet költsége x, a második szelet költsége 2x, a harmadiké 3x és így tovább, egészen az utolsó szeletig, aminek a költsége már a teljes táblaolvasás költsége. Ez nem pont így igaz, de jól szemlélteti a problémát.

A lényeg pedig: ahhoz, hogy a szerver tudja a következő szelet(ek) elejét, az előző szelete(ke)n végig kell futnia. 5.000 sornál még nem észrevehető, de nagyobb adatmennyiségnél már igencsak.

A probléma megoldása, hogy mindig visszaadjuk a lekérdezésnek az utoljára olvasott azonosítót és csak az annál nagyobbakat kérjük le, növekvő sorrendben.

És akkor már csak a demó maradt.

1.) Készítünk egy táblát 3 millió sorral:


use tempdb
go

if object_id('accounts', 'U') is not null
drop table accounts
go

select top 3000000
identity(int, 1, 1) id,
concat(newid(), null) as name,
concat(newid(), '@domainname') as email
into accounts
from sys.columns c1
cross join sys.columns c2
cross join sys.columns c3
go

alter table accounts add constraint PK_accounts primary key clustered (id) with (fillfactor = 100)

 

Kapcsoljuk be az “Include Actual Execution Plan”-t.

A beépített lapozás, kérjük le a legelső szeletet:


set statistics io on;
set statistics time on;

select
id,
name,
email
from accounts
order by id
offset 0 rows fetch next 1000 rows only

Table ‘accounts’. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

És mindemellett Clustered index scan.

Menjünk egy kicsit előrébb a resultsetben:


set statistics io on;
set statistics time on;

select
id,
name,
email
from accounts
order by id
offset 2000000 rows fetch next 1000 rows only

 

Table ‘accounts’. Scan count 1, logical reads 25374, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ez szintén Clustered index scan. A logical reads-ből is jól látszik, hogy 25374 darab 8 KB-os lapot érintett a lekérdezés.

Nézzük a másik módszert:

set statistics io on;
set statistics time on;

select top 1000
id,
name,
email
from accounts
where id > 0
order by id

Table ‘accounts’. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Page olvasásban ugyanaz a költség, mint a beépített lapozás első szeleté, de ez már Clustered index seek!

És most menjünk előrébb a resultsetben:

set statistics io on;
set statistics time on;
select top 1000
id,
name,
email
from accounts
where id > 2000000
order by id

Table ‘accounts’. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

A beépített módszernél ez 25374 logical reads volt. Na ilyenkor lehet számolgatni, hogy hányszoros teljesítménynövekedést érünk is el? 🙂 Jah, és továbbra is Clustered index seek!