2013. február 17., vasárnap

Amikor nem jön a csoda

Üdvözletem mindenkinek!


Nemrég egy tanfolyamon kérték, hogy nézzünk már rá egy példát arra, amikor az index használata nem feltétlenül fogja csoda szerűen meggyorsítani a folyamatunkat úgy, ahogy azt elvárnánk tőle. Bár akkor is játszottunk a teszt környezetben, de most is bemásolok egy friss adatot egy mai szituból.


Adott egy tábla, benne kb 12-13 millió rekord.

Létre lett hozva egy non-clustered index egy string típusú mezőre. Legyen mondjuk a neve "M"
(Van clustered index is a táblán, de az most nem használható fel). Erre az indexre azért van szükség, mert sokszor lesz ún. "nagy szelektivitású" lekérdezés ezen mező segítségével.

Az index létrehozása kb 13 percig tartott.

Ezután nem sokkal jött az isteni sugallat, hogy van jónéhány olyan rekord, aminek az értéke ezen mezőn nem az igazi, ezért azt mad jól megápdételem.

update táblám SET M=NULL where datalength(M) > 20

Aztán csak vártam és vártam és vártam... :) Majd megkaptam azt hogy 3.558.279 row(s) affected.

Ja és ezt természetesen 15 és fél perc után.

Tehát elmondhatjuk, hogy az indextől nem várhatunk mindig csodát...

Miért is volt ez ennyire lassú?

Olyan "szerencsés" helyzetben vagyok, hogy több probléma is volt ezzel a lekérdezéssel:

1.: Az indexbe mi került bele? Hát maga a string tartalom... Én meg mit használok a feltételben? Egy int típusú számot, ami mellesleg a kulcs hossza, de hát kit érdekel, mivel azt szépen ki kell számolnia minden egyes esetben a datalength függvénnyel.

2.: Akárhogy is csűrjük-csavarjuk, ha 12 millió rekordból 3.5 milliót kezelned kell, az már nem annyi időbe kerül, mintha csak pár 10 ezret kezelnél...

Mi lehetett volna erre egy megoldás, így utólag belegondolva? :)

Talán lehetne az, hogy csinálni kell egy számított oszlopot, ami tartalmazza a hosszakat, na azzal lehetne szép gyorsulást elérni! Főleg ha dobunk rá egy indexet ;)


Kreatívabb ötlet a számított oszlopnál? Valaki?

(Természetesen ha az adattartalmat kellett volna vizsgálni, akkor jobban muzsikált volna az index.)


Bye.


4 megjegyzés:

  1. Szia!

    Ejha! Azt hiszem most leesett valami nagy számomra az indexekről! Pedig azt hittem már nagyjából rendben vagyok velük! Ilyesmibe én még bele se gondoltam. Köszi! Z.

    VálaszTörlés
  2. Szia!

    Szerintem :)
    A 12-13M rekordra az indexépítési és update idő az elég sok.
    Az update-t érdemesebb kis kötegekben (5.000 - 10.000) és tranzakcióban futtatni, és nagyságrendekkel kisebb lesz a futási idő.

    Persze a tábla és egyéb indexek szerkezetek nélkül nehéz bármit is mondani :)

    VálaszTörlés
  3. Szia!

    Az index építési idő még csak hagyján!
    Az update 10.000-es bontásai egy klasz ötlet egyébként! :) Bár lehet hogy én mernék egy picivel nagyobb egységekben is gondolkodni, de ahogy mondtad, ez már több mindentől is függ.

    VálaszTörlés
  4. Szia,

    Ez természetes, hogy így sokáig tart. Ha az update "update táblám SET M=NULL where datalength(M) > 20", akkor nonclustred index scan lesz annak alapján amit az elején leírtál, mivel az összes index page-t végig kell nézni,így a scan hatékonyabb mint a seek + számolni is kell, az index nem tartalmazza a keresett értéket - datalenght. Ha az update "update táblám SET M=NULL whereM = 'aa'" lenne, akkor index seek lehet kardinalitástól függően. Ha megint nagy rekordszámot lát a query optimizer akkor ismét scan lesz. Ajánlott a statisztikák folyamatos karbantartása, elkerüendő az un. kardinalitási hibákat. Zsolt által javasolt batch update működhet, de így is figyelni kell a transaction logra, full recovery model esetén biztosan. Esetleg egy computed column - PERSISTED - segíthet , persze indexelve ;)

    VálaszTörlés