Fejlett lekérdezés tuningolás
Az SQL szerver 2000 a keresési, metszetkészítési, unió- és különbségképző műveleteit a memóriában osztályozási és darabolási technikával végzi. E lekérdezési terv használatával az SQL szerver vertikális tábla felosztást hajt végre, amelyet oszlopszintű tárolásnak nevezünk.
Az SQL szerver háromféle kapcsolatképző művelettel dolgozik:
- Nested lookup join. Ha a join egyik fele eléggé kis méretű (pl. 10 sornál kevesebb), a másik fele jóval nagyobb, és a kapcsoló mezőre indexelt, akkor az index nested loop (indexelt egymásba ágyazott ciklus) a leggyorsabb művelet, mert a legkevesebb I/O műveletet és összehasonlítást igényli.
- Merge join. Ha a két tábla nagyobb méretű, de rendezve van az összekapcsoló oszlopra, akkor a merge (összefésülés) a leggyorsabb kapcsoló művelet. Ha mindkét tábla nagy és hasonló méretű, akkor a merge join-nak van elsőbbsége, habár a hash join hasonló teljesítménnyel dolgozik. A hash join sokkal gyorsabb, ha a két méret jelentősen eltér egymástól.
- Hash join. Ez a típus – a daraboló kapcsolat – a leggyorsabb a nagyméretű, rendezetlen, index nélküli bemenet esetében. Használható közbülső eredményhez bonyolult lekérdezésekben:
- A közbülső eredmények nem indexeltek (hacsak nem mentjük le direkt a lemezre és indexeljük azokat), és gyakran nem eredményeznek megfelelő sorrendet a következő művelethez a lekérdezési tervben.
- A lekérdezés optimalizáló csak a közbülső méretet becsüli meg. Mivel a becslés komplex lekérdezés esetén nagyságrendi hiba lehet, kódolva feldolgoz egy közbülső eredményt, melynek nem csak eredményesnek kell lennie, hanem könnyedén csökkenthetőnek is, ha egy közbülső eredmény sokkal nagyobbá válik, mind amire számítani lehetett.
A hash join redukálást végez, denormalizálás esetén. A denormalizálást tipikusan teljesítménynövekedés elérésére használjuk, join műveletek csökkentésével, a redundancia veszélyének ellenére is, mint pl. az inkonzisztens frissítés. A hash join csökkenti a denormalizálás szükségét. A hash join vertikális felosztást tesz lehetővé (reprezentálva oszlopok csoportját egy táblával, különálló fájlok és indexek között), így megvalósítható opcióvá válik a fizikai adatbázis-tervben.
Egymásba ágyazott ciklusú kapcsolat
Az egymásba ágyazott ciklusú kapcsolat egy külső és egy belső input táblát használ. A külső ciklus felhasználja a külső bemeneti táblát sorról sorra, keresi a megfeleltethető sort a belső input táblában. A legegyszerűbb esetben a keresés megvizsgálja az adott táblát, vagy indexet. Ezt hívjuk nativ egymásba ágyazott ciklusos kapcsolatnak. Ha a keresés egy indexre bukkan, akkor ezt indexelt egymásba ágyazott ciklusú kapcsolatnak hívjuk. Ha az index a lekérdezési terv részeként készült és megszűnik a lekérdezés futtatása után, ezt úgy hívjuk, hogy átmeneti indexelt egymásba ágyazott ciklusos kapcsolat. Mindezen variánsokat a lekérdezés-optimalizáló veszi tekintetbe. Egy ilyen join főképpen akkor hatékony, ha a külső input elég kicsi és a belső indexelt input eléggé nagy. Több kisméretű tranzakció esetében (ha pl. kis számú sorok az érintettek), ez a típus messze vezet a két másik fajta kapcsolat között. Viszont nagy lekérdezések esetében gyakran ez a típus nem jó megoldás.
Összefésülő kapcsolat
Az összefésülő kapcsolat ugyanaz a két rendezett bemeneti táblát igényli, melyek egyenlő módon deklaráltak a join WHERE feltételében. Az optimalizáló tipikusan egy indexet keres, ha található egy a saját oszlopain, vagy elhelyez egy rendező operátort az összefésülő kapcsolat alatt. Ritka esetben több egyenlő feltétel lehet, de az összefésülő oszlopok csak néhány elérhető egyenlő feltételből származnak.
Mivel minden bemenet rendezett, a Merge Join operátor fog egy sort mindkét bemenetről, és összehasonlítja őket. Például inner join műveleteknél a sorok akkor adódnak vissza, ha megegyeznek. Ha nem egyenlők, akkor elveti azt a sort, amelyik a kisebb értékkel rendelkezik, és másik sort kért a táblából. Ez a folyamat ismétlődik addig, amíg minden sort fel nem dolgoz.
Ez a join művelet lehet szokásos, vagy N-to-N beavatkozás. Egy N-to-N összefésülés átmeneti táblát használ a sorok tárolására. Ha duplikációt észlel akármelyik bemeneten, akkor valamelyik input visszatér a duplikáció elejére, amíg minden hasonló értéket a külső bemenetről fel nem dolgoz.
Ha visszamarad egy állítmány, minden sor, amely kiegyenlíti az összefésülő állítást, kiértékeli a megmaradó állítmányt és csak egy teljesített sort ad vissza.
Ez a típusú join nagyon gyors, de költséges választás lehet, ha rendezés szükséges hozzá. Viszont ha az adatmennyiség nagyméretű, és a kívánt adat elérhető egy meglévő előre rendezett bináris fa-indexszel, akkor az összefésülés a leggyorsabb algoritmus.
Daraboló kapcsolat
A hash join két bemenettel rendelkezik: az "épített" bemenettel és a próba bemenettel. Az optimalizáló úgy határozza meg ezt a szabályt, hogy a kisebb méretű bemeneti tábla lesz az "épített" input.
A daraboló kapcsolat használható a legtöbb fajta join típus esetében: inner join; left, right, és full outer join; left és right semi-join; metszet; unió; és különbségképzés esetében. Ezen felül, egy változója a daraboló join-nak képes duplikáció megszüntetést és csoportosítást végezni (mint pl.: SUM(fizetes) GROUP BY ceg). Ezek a módosítások csak az egyik bemenetre használhatók.
Hasonlóan a merge join-hoz, egy hash join csak akkor használható, ha legalább egy azonos (WHERE) feltétel van a join állítmányban. Viszont mivel join-ok tipikusan újra összegyűjtik a kapcsolatokat, kifejeződnek egy egyenlőség állítmánnyal az elsődleges kulcs és a távoli kulcs között, a legtöbb join-nak legalább egy egyenlőségi feltétele van. Az oszlopcsoportokat egy egyenlőségi állítmányban hash kulcsnak nevezzük, mivel ezek azok az oszlopok, amelyek elősegítik a daraboló funkciót. Lehetséges hozzáadni állítmányokat és kiértékelni, mint visszamaradó állítmányokat, elválasztva az összehasonlító hash értékektől. A hash kulcs lehet egy kifejezés, akármilyen hosszan kiértékelődő, oszlopok nélkül egy egyszerű sorban. Csoportosító műveletekben, a group by lista oszlopai a daraboló kulcsok. A set műveleteknél, mint amilyen a metszet, valamint a duplikáció megszüntetése, a daraboló kulcs minden oszlopot magába foglal.
Memórián belüli daraboló join
A hash join elsőként megvizsgálja és kiszámítja az adott "épített" bemenetet és készít egy daraboló táblát a memóriában. Minden sor beszúrásra kerül a daraboló kosárba, a hash értéktől függően, amely a hash kulcshoz számítódik. Ha az adott épített bemenet kisebb, mint az elérhető memória, akkor minden sor beszúrásra kerül a hash táblába. Ez az építő fázis követi a próba fázist. Az adott próba input-vizsgálatra és -számításra kerül soronként, és minden próba sor hash kulcsa kiszámítódik, a megfeleltetett hash kosár vizsgálatra kerül és az eredmény, az egyezés itt kalkulálódik.
Jóindulatú daraboló join
Ha az épített bemenet nem fér el a memóriában, a hash join több lépésben halad. Minden lépésnek van építő és próba fázisa. Kezdetben az adott épített és próba bemenet felhasználásra és felosztásra kerül (hash függvény használatával a daraboló kulcsokon) több fájlba. A fájlok számát hívjuk a felosztás kiterjedésének. A daraboló függvény használata a hash kulcsokon biztosítja, hogy csak két kapcsolódó rekord lehet ugyanabban a fájlpárosban. Emiatt két nagy bemenet összekapcsolási lépése leredukálódik több, kisebb példányra, ugyanazon lépésben. A hash join ezután végrehajtódik minden felosztott fájlpárosra.
Rekurzív daraboló join
Ha az épített bemenet annyira nagy, hogy az egyszerű külső összefésülő rendezés szükségessé tesz többszintű összefésülést, több felosztó lépés és több felosztási szint szükséges. Ha csak néhány partíció nagy, a hozzáadott felosztó lépések csak az adott felosztáshoz használhatók. Annak kedvéért, hogy minden felosztó lépés a lehető leggyorsabb legyen, nagyméretű, aszinkron I/O beavatkozást használ, így egy egyedi szál több lemezmeghajtót lefoglalva tarthat.
Ha az épített bemenet nagyobb, de nem annyira nagy, mint az elérhető memóriaméret, akkor a memóriában lévő hash join elemek, és a jóindulatú hash join kombinálódik egy lépésben, egy hibrid hash join-t előállítva.
Nem mindig lehetséges az optimalizálás közepette eldönteni, hogy melyik hash joint kell használni. Ekképpen az SQL szerver elkezd használni egy memórián belüli hash join-t, és fokozatosan átlép jóindulatú hash join-ra és rekurzív hash join-ra, az épített bemenet méretétől függően.
Ha az optimalizáló előre hibásan érzi meg, hogy melyik input a kisebb, és így melyik lesz az épített és próba input, a szabályok dinamikusan fennmaradnak. A hash join meggyőződik, hogy a kisebb többlettel rendelkező fájl az épített bemenet. Ezt a technikát hívjuk szabálymegőrzésnek.