Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.

April 8, 2008 / by Zsolt Soczó

SQL Server 2008 újdonságok 33. – Sparse columns (foghíjas oszlopok?)

Ha egy oszlopot megjelölönk a SPARSE jelzővel, akkor a NULL értéket tartalmazó mezők egyáltalán nem fognak helyet elfoglalni a táblákban. Ez akkor hasznos, ha olyan az adatmodellünk, hogy nagyon sok oszlop van egy táblában, de ezek küzöl sok NULL értékű.
A mintapélda erre az esetre a termékeket és azok jellemzőit tároló adatbázis. Minden terméknek van pár jellemzője, ami vagy azonos, vagy nem a többiekével. Példál minden terméknek van ára, némelyiknek van színe, másnak mérete, stb. Ha a termékeket egy táblában akarjuk tárolni, akkor a közös adatok közönséges oszlopok lennének, a csak bizonyos termékekre vonatkozó adatok pedig sparse oszlopokban. Egy ilyen terv miatt akár több ezer oszlopa is lehet a táblának, ami miatt a sparse oszlopok száma felmegy 30000-re (csak az RTM-ben, most még nem).
Számomra furcsa ez a példa, mert egy új termék miatt sokszor sémát is kell módosítani, új oszlopot felvenni, ami nekem rossz tervet jelent, de sokan ezt favorizáják, ha nem sűrűn változnak az oszlopok, mint például Sharepointban, ahol a dokumentumok jellemzőit fogják majd várhatóan így tárolni (a következő verzióban).
A teljesség kedvéért elmondom, hogy nekem szimpatikusabb a másik két módszer erre a problémára. Az egyikben egy szótártábla van, név, jellemző oszlopokkal. A termékek tábla és a szótártábla között egy kapcsolótábla teremt több-több kapcsolatot. Így bármely termékhez felvehetünk tetszőleges adatokat, illetve egy már létező jellemzőt bármely termékhez hozzárendelhetjük. Entity-attribute-value (EAV) vagy open schema néven fut ez a világban.
A módszer hátránya, hogy a “jellemző” oszlop valamilyen polimorf izé kell legyen, valamilyen string vagy variant, hisz a jellemzők más és más típusúak. Azaz ez a megoldás nem strongly typed, nem erősen típusos. A sparse column-ös az igen.
A lekérdezés is macerás kicsit, hisz 3 táblából kell kijoinolni az adatokat, ami lassabb is, míg a sparse esetén egy sort kell egy táblából lekérdezni, valamint az eredmények sorokban jönnek, nem a természetesebb oszlopos megjelenítésben mint a sparse-nál. Mondjuk egy pivot operátor ezen segíthet.
Harmadik lehetséges design xml oszlop használata, abba aztán minden termékhez annyi adatot rakunk be, olyan típussal, ahogy csak akarjuk. Ekkor az adatok be-ki tolása kicsit körülményesebb, illetve az xml sémamódosítás nem leányálom a szerverben.
Mindenki másra esküszik. Celko pl. a sparse columns híve.
No, de beszéljünk most már konkrétan a sparse oszlopokról.
Előnyök:

  • A NULL egyáltalán nem foglal el helyet
  • Kívülről nem látszik az oszlopról, hogy belül spórolósan tárol
  • A Filtered Indexekkel szuperül együtt tud működni, hisz egy sparse oszlop IS NOT NULL-os filtered index nagyon hatékonyan csak a tényleges adatokat fogja indexelni.
  • A tábla sparse oszlopaira lehet készíteni egy ún. column set-et, amin keresztül xmlben ki lehet nyerni a nem null oszlopok adatait egyben, sőt, ezen keresztül módosítani is őket. Mindjárt lesz rá példa.

De persze mindennek meg van az ára:

  • A NULL ugyan nem foglal semmi helyet, de ha van ott adat, akkor plusz 4 byte kell hozzá. Egy bit esetén azért ez jelentős veszteség. :)
  • A text, ntext, image, timestamp, bármely saját típus, geometry, geography, varbinray (max) FILESTREAM-mel vagy a számított oszlopok nem lehet sparse-ként definiálva
  • Nem lehet default értékük (minek is persze)
  • A laptömörítés és a merge replikáció nem kompatibilis vele (a tranzakciós igen).

A plusz 4 byte miatt nagyon meg kell gondolni, érdemes-e használni ezt a szolgáltatást egy oszlopra. Van itt egy remek kis táblázat, ami azt taglalja, legalább hány százaléka legyen egy oszlop értékeinek null, hogy megérje bevetni a sparse-t. Nyilván ez típusonként változó, bitnél ha 2%-nál több sorban van nem null, már nem éri meg. Látható a táblázatból, hogy tipikus típusoknál kb. az adatok fele null kell legyen, különben több a veszteség, mint a nyereség.

Lássunk egy termékeket tároló táblás példát:

create table Termek
(
  id int not null primary key identity,
  nev nvarchar(30) not null,
  ar decimal null,
  szin nvarchar(30) sparse,
  szag nvarchar(30) sparse,
  suly decimal sparse,
  maxhofok int sparse,
  eltarthatosag date sparse,
  egyebAdatok xml column_set for all_sparse_columns
)

Id, név, ár minden terméknek van, de a többi saláta nem értelmezhető minden termékre, azok sparse oszlopokba kerültek. Az összes sparse oszlopot összefog egy xml column set. Ezen keresztül látható és módosítható is az összes sparse adat egyszerre.

Szúrjunk be pár sort, majd kérdezzük le őket explicit oszlopnevekkel!

insert Termek (nev, ar, szin, szag, suly, maxhofok, eltarthatosag)
values
(N'Amar margarin', 250, NULL, N'büdös', 250, NULL, '20080104'),
(N'Lítiumbázisú csapágyzsír', 140, 'sárga', N'fincsi', 100, 240, NULL),
(N'Kalciumbázisú csapágyzsír', 140, 'piros', N'kellemes', 90, 110, NULL)

select id, nev, ar, szin, szag, suly, maxhofok, eltarthatosag from Termek
id          nev                            ar                                      szin                           szag                           suly                                    maxhofok    eltarthatosag
----------- ------------------------------ --------------------------------------- ------------------------------ ------------------------------ --------------------------------------- ----------- -----------------------
1           Amar margarin                  250                                     NULL                           büdös                          250                                     NULL        2008-01-04 00:00:00.000
2           Lítiumbázisú csapágyzsír       140                                     sárga                          fincsi                         100                                     240         NULL
3           Kalciumbázisú csapágyzsír      140                                     piros                          kellemes                       90                                      110         NULL

Kívülről semmi különbség a sima és a sparse oszlopok között. Van azonban egy column setünk, ami picit felborítja a szokásos viselkedést. Nézzünk csak egy select *-ot!

select * from Termek
id          nev                            ar                                      egyebAdatok
----------- ------------------------------ --------------------------------------- ----------------------------------------------------------------------------------------------------
1           Amar margarin                  250                                     <szag>büdös</szag><suly>250</suly><eltarthatosag>2008-01-04</eltarthatosag>
2           Lítiumbázisú csapágyzsír       140                                     <szin>sárga</szin><szag>fincsi</szag><suly>100</suly><maxhofok>240</maxhofok>
3           Kalciumbázisú csapágyzsír      140                                     <szin>vörös</szin><szag>penetráns</szag><suly>90</suly><maxhofok>110</maxhofok>

Hoppá. A sparse oszlopok nem látszanak a select *-ban, csak az összesítő xml column set oszlopunk, amin keresztül xmlként jönnek át az adatok. Amelyik oszlop NULL, annak megfelelő elem hiányzik az xml tartalomból. Ez felfogható egyfajta számított oszlopként, hisz nincs letárolva pluszként, a sparse oszlopokból rakják össze dinamikusan.
Számított létére azonban okos, mert rajta keresztül lehet update-elni és insert-álni:

update Termek
set egyebAdatok = N'<szin>vörös</szin><szag>penetráns</szag><suly>90</suly>'
where id = 3

select id, nev, ar, szin, szag, suly, maxhofok, eltarthatosag, egyebAdatok from Termek
id          nev                            ar                                      szin                           szag                           suly                                    maxhofok    eltarthatosag           egyebAdatok
----------- ------------------------------ --------------------------------------- ------------------------------ ------------------------------ --------------------------------------- ----------- ----------------------- ----------------------------------------------------------------------------------------------------
1           Amar margarin                  250                                     NULL                           büdös                          250                                     NULL        2008-01-04 00:00:00.000 <szag>büdös</szag><suly>250</suly><eltarthatosag>2008-01-04</eltarthatosag>
2           Lítiumbázisú csapágyzsír       140                                     sárga                          fincsi                         100                                     240         NULL                    <szin>sárga</szin><szag>fincsi</szag><suly>100</suly><maxhofok>240</maxhofok>
3           Kalciumbázisú csapágyzsír      140                                     vörös                          penetráns                      90                                      NULL        NULL                    <szin>vörös</szin><szag>penetráns</szag><suly>90</suly>

Látható, hogy az adatok sorrendje tetszőleges az xml bemenetben, és a hiányzó adatok NULL-ra lesznek kitöltve.

Egyébként a select * csak akkor nem tartalmazza a sparse oszlopokat, ha van xml column set is definiálva a táblán. Ha nincs, a sparse oszlopok úgy látszanak, mint a sima oszlopok.

alter table Termek
drop column egyebAdatok
select * from Termek
id          nev                            ar                                      szin                           szag                           suly                                    maxhofok    eltarthatosag
----------- ------------------------------ --------------------------------------- ------------------------------ ------------------------------ --------------------------------------- ----------- -----------------------
1           Amar margarin                  250                                     NULL                           büdös                          250                                     NULL        2008-01-04 00:00:00.000
2           Lítiumbázisú csapágyzsír       140                                     sárga                          fincsi                         100                                     240         NULL
3           Kalciumbázisú csapágyzsír      140                                     vörös                          penetráns                      90                                      NULL        NULL

A 30000-es oszlopszámot teszteltem:

declare @i int = 0
while (@i < 10000) begin declare @sql nvarchar(max) = N'alter table Termek add ujoszlop' + CAST(@i as nvarchar(20)) + ' nvarchar(10) sparse' print @sql exec(@sql) set @i += 1 end [/source] De egyelőre még 1024 a limit, mint régen, majd az RTM-ig még megváltoztatják.

Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.

LEAVE A COMMENT

3 COMMENTS

  • gerely April 9, 2008

    Egy lehetséges use case: írsz egy olyan ORM toolt, ami az egyes objektum-típusokat nekik megfelelő táblákra képezi le.
    Ebben eddig nincs semmi csodálatos, de szeretnéd megoldani azt is, hogy az üzleti objektumaid öröklési hierarchiába szervezhetők legyenek. Ez kicsit megbonyolítja a tárolást. Egy lehetséges megoldás, hogy a közös őstől származó osztályokat ugyanarra a táblára képzed le – értelemszerűen ennek a táblának olyan szélesnek kell lenni, hogy elférjen benne az összes leszármazott minden tulajdonsága. Egy-egy adott típusnál nem használt, de a táblában jelen lévő mező pedig NULL.
    Ezentúl a nem a közös ősben definiált tulajdonságok sparse-ként lehetnek megjelölve. Szerintem.

  • Soczó Zsolt April 9, 2008

    Pontosan, az öröklés modellezésének egyik módja az egy tábla, minden leszármazott leképezés, ekkor is jó a sparse. Köszi, hogy emlékeztettél rá.