Választás a táblázatváltozók és az ideiglenes táblák között (ST011, ST012)

Az emberek sokat vitathatnak, és igen, sokat vitatkoznak a táblázatváltozók és az ideiglenes táblázatok relatív előnyeiről. Néha, mint a funkciók írásakor, nincs más választása; de amikor megteszi, rájön, hogy mindkettőnek van felhasználása, és könnyű olyan példákat találni, ahol bármelyik gyorsabb. Ebben a cikkben elmagyarázom az egyik vagy másik kiválasztásának fő tényezőit, és bemutatok néhány egyszerű „szabályt” a legjobb teljesítmény elérése érdekében.

Feltéve, hogy betartja az elkötelezettség alapvető szabályait. , akkor a táblázatváltozókat kell első választásnak tekinteni, amikor viszonylag kicsi adathalmazokkal dolgozunk. Könnyebb velük dolgozni, és kevesebb átfordítást váltanak ki az általuk használt rutinokban, mint az ideiglenes táblázatok. A táblázatváltozóknak kevesebb zárolási erőforrásra is szükségük van, mivel „privátak” azokkal a folyamatokkal és kötegekkel, amelyek létrehozták őket. Az SQL Prompt ezt az ajánlást kódelemzési szabályként hajtja végre, az ST011 – Fontolja meg a table változó használatát az ideiglenes tábla helyett.

Ha összetettebb ideiglenes adatok feldolgozását végzi, vagy ésszerűnél kisebb mennyiségű adatot kell használnia. adatok, akkor a helyi ideiglenes táblák valószínűleg jobb választást jelentenek. Az SQL Code Guard tartalmaz egy kódelemzési szabályt, az ajánlása alapján, az ST012 – Fontolja meg az ideiglenes tábla használatát a táblázatváltozó helyett, de ez jelenleg nincs implementálva az SQL Prompt-ban.

A táblaváltozók és az ideiglenes táblák előnyei és hátrányai

A táblázatváltozók általában „rossz sajtót” kapnak, mert az őket használó lekérdezések alkalmanként nagyon nem hatékony végrehajtási terveket eredményeznek. Ha azonban betart néhány egyszerű szabályt, azok jó választás a köztes „munka” táblákhoz, valamint az eredmények átadásához a rutinok között, ahol az adathalmazok kicsiek és a szükséges feldolgozás viszonylag egyszerű.

A táblázatváltozók használata nagyon egyszerű, főleg azért, mert “nulla karbantartás”. Átfogják azokat a kötegeket vagy rutinokat, amelyekben létrehozták őket, és automatikusan eltávolításra kerülnek, amint a végrehajtás befejeződik, és így hosszú távú kapcsolaton belül használják őket nem kockáztatja az “erőforrás-hogging” problémákat a tempdb-ben. Ha egy tábla változót egy tárolt eljárásban deklarálunk, akkor az a tárolt eljárás lokális és nem hivatkozható beágyazott eljárásban. Nincsenek statisztikákon alapuló újrafordítások a táblaváltozókra és nem lehet ALTER egy, így az őket használó rutinok általában kevesebb újrafordítást hajtanak végre, mint azok, amelyek ideiglenes táblákat használnak. Ezeket sem teljesen naplózzák, így létrehozása és kitöltése gyorsabb és kevesebb helyet igényel a t váltságnapló. Ha tárolt eljárásokban használják őket, akkor kevésbé vetélkednek a rendszer táblák, nagy egyidejűség mellett. Röviden: könnyebb a dolgokat rendben és rendben tartani.

Viszonylag kis adathalmazokkal végzett munka esetén azok gyorsabbak, mint az összehasonlítható ideiglenes táblázatok. Azonban a sorok számának növekedésével, megközelítőleg 15 000 soron túl, de a kontextustól függően változóan, nehézségekbe ütközhet, elsősorban a statisztikák támogatásának hiánya miatt. Még azok az indexek sem rendelkeznek statisztikákkal, amelyek PRIMARY KEY és UNIQUE korlátozásokat kényszerítenek a táblázatváltozókra. . Ezért az optimalizáló keményen kódolt becslést fog használni egy tábla változóból visszaküldött 1 sorra, és így inkább olyan operátorokat választ, amelyek optimálisak a kis adathalmazokkal való munkavégzéshez (például Nested Loops operátor a csatlakozásokhoz). Minél több sor található a táblázat változójában, annál nagyobbak az eltérések a becslés és a valóság között, és annál kevésbé hatékonyak az optimalizáló tervválasztásai. Az így kapott terv néha ijesztő.

A tapasztalt fejlesztő vagy a DBA erre a problémára fog figyelni, és készen áll a OPTION (RECOMPILE) lekérdezési tipp arra az utasításra, amely a tábla változót használja. Amikor egy táblázatváltozót tartalmazó köteget küldünk be, az optimalizáló először összeállítja azt a köteget, amelyben a táblázatváltozó üres. Amikor a köteg elkezdi végrehajtani, a tipp csak az egyetlen utasítás újrafordítását eredményezi, ekkor a tábla változó feltöltésre kerül, és az optimalizáló a valós sorszámot felhasználva összeállíthatja az utasítás új tervét. Néha, de ritkán még ez sem segít. Emellett az erre a célzásra való túlzott támaszkodás bizonyos mértékig érvényteleníti azt az előnyt, amelyet a táblázati változók kevesebb újrafordításhoz vezetnek, mint az ideiglenes táblákat.

Másodszor, a táblázati változókkal kapcsolatos bizonyos indexkorlátozások inkább tényezőkké válnak. nagy adathalmazok. Noha az inline index létrehozási szintaxist használhatja nem fürtözött indexek létrehozására egy táblázati változón, vannak bizonyos korlátozások, és még mindig nincsenek társított statisztikák.

Még viszonylag szerény sorszám esetén is találkozhat a lekérdezés teljesítményével kapcsolatos problémákkal, ha olyan lekérdezést próbál végrehajtani, amely egy csatlakozás, és elfelejtett megadni egy PRIMARY KEY vagy UNIQUE korlátozás az összekapcsoláshoz használt oszlopban. Az általuk megadott metaadatok nélkül az optimalizálónak nincs tudomása az adatok logikai sorrendjéről, illetve arról, hogy az összekapcsolási oszlop adatai tartalmaznak-e ismétlődő értékeket, és valószínűleg nem hatékony csatlakozási műveleteket választ, ami lassú lekérdezéseket eredményez. Ha asztali változó halommal dolgozik, akkor csak egy egyszerű listát használhat, amelyet valószínűleg egyetlen adagban fognak feldolgozni (táblázat beolvasása). Ha kombinálja a OPTION (RECOMPILE) tipp használatát a pontos számosság becsléséhez, valamint a csatlakozási oszlopban található kulcsot, hogy az optimalizáló hasznos legyen metaadatok, akkor kisebb adathalmazok esetén gyakran elérheti a helyi ideiglenes táblázathoz hasonló vagy annál jobb lekérdezési sebességet.

Miután a sorok száma meghaladja a táblázat változójának komfortzónáját, vagy bonyolultabb adatokat kell megtennie feldolgozását, akkor a legjobb, ha ideiglenes táblákat használ. Itt az indexeléshez rendelkezésre állnak a teljes lehetőségek, és az optimalizáló luxusban áll majd, hogy statisztikákat használjon ezekhez az indexekhez. Természetesen hátránya, hogy az ideiglenes asztalok magasabb karbantartási költségekkel járnak. A tempdb torlódások elkerülése érdekében feltétlenül tisztázza magát. Ha módosít egy ideiglenes táblázatot, vagy módosítja a benne lévő adatokat, előfordulhat, hogy újrafordítja a szülői rutint.

Az ideiglenes táblák jobbak, ha nagyszámú törlésre és beillesztésre van szükség (sormegosztás ). Ez különösen igaz, ha az adatokat teljes mértékben el kell távolítani a táblából, mivel csak az ideiglenes táblák támogatják a csonkolást. A táblázati változók tervezésének kompromisszumai, például a statisztikák és az újrafordítások hiánya ellenük hat, ha az adatok volatilisak.

Amikor táblaváltozók használatát érdemes használni

Mi Kezdem egy példával, ahol a táblázat változója ideális, és jobb teljesítményt eredményez. Készítünk egy listát az Adventureworks alkalmazottairól, melyik részlegen dolgoznak, és milyen műszakokban dolgoznak. Kis adatkészlettel van dolgunk (291 sor).

Az eredményeket egy második ideiglenes táblázatba helyezzük, mintha az eredményt továbbadnánk a következő kötegnek. Az 1. lista mutatja a kódot.

És itt van egy tipikus eredmény a lassú tesztgépemen:

Az ideiglenes tábla használata folyamatosan lassabb, bár az egyes futtatások meglehetősen változhatnak.

A méretarány problémái és a kulcs vagy tipp megadásának elfelejtése

Milyen a teljesítmény, ha két táblaváltozót csatlakoztatunk? Próbáljuk ki. Ehhez a példához két egyszerű táblázatra van szükségünk, az egyik az összes elterjedt szóval az angol nyelven (CommonWords), a másik pedig az összes szó felsorolásával Bram Stoker Drakulájában. (WordsInDracula). A TestTVsAndTTs letöltés tartalmazza a szkriptet, amely létrehozza ezt a két táblázatot, és mindegyiket feltölti a hozzá tartozó szövegfájlból. 60 000 gyakori szó létezik, de Bram Stoker csak 10 000-et használt közülük. Az előbbi jóval a megtérülési ponton kívül esik, ahol az ember kezdi előnyben részesíteni az ideiglenes táblákat.

Négy egyszerű, külső csatlakozási lekérdezést fogunk használni, tesztelve az eredményt a NULL értékek, a Drakulában nem szereplő, a Drakulában található, a Drakulában nem gyakori szavak és végül egy másik lekérdezés a Drakulában található, de az ellenkező irányba csatlakozó szavak megkeresésére. Rövidesen látni fogja a lekérdezéseket, amikor megmutatom a tesztkészlet kódját.

Az alábbiakban bemutatjuk az első tesztfuttatások eredményeit. Első futtatáskor mindkét táblaváltozó rendelkezik elsődleges kulcsokkal, a másodikban pedig mindkettő kupac, csak azért, hogy lássam, túlzom-e azokat a problémákat, amelyek nem jelentenek indexet egy tábla változóban. Végül ugyanazokat a lekérdezéseket futtatjuk ideiglenes táblákkal. Az összes tesztet szándékosan, lassú fejlesztésű szerveren futtattuk szemléltetés céljából; nagyon különböző eredményeket fog elérni egy éles kiszolgálóval.

Az eredmények azt mutatják, hogy ha a táblázati változók halmozott értékek, fennáll annak a veszélye, hogy a lekérdezés 100 perc helyett tíz percig fut. Ezek nagyszerű példát mutatnak arra a szörnyűséges teljesítményre, amelyet megtapasztalhat, ha nem ismeri a szabályokat. Még akkor is, ha elsődleges kulcsokat használunk, a sorok száma, amelyekkel foglalkozunk, azt jelenti, hogy az ideiglenes táblák használata most kétszer olyan gyors.

Nem fogok elmélyülni az ezek mögött lévő végrehajtási tervek részleteiben. teljesítménymutatók, kivéve néhány átfogó magyarázatot a fő különbségekre. A temp tábla lekérdezéseihez az optimalizáló a kardinalitás és az elsődleges kulcs korlátaiból származó metaadatok teljes ismeretével felfegyverkezve egy hatékony Merge Join operátort választ a csatlakozási művelet végrehajtására.Az elsődleges kulcsokkal rendelkező táblák változója esetében az optimalizáló ismeri a sorok sorrendjét az összekapcsolási oszlopban, és azt, hogy nem tartalmaznak duplikátumokat, de feltételezi, hogy csak egy sorral foglalkozik, és ezért egy beágyazott hurok csatlakozást választ. Itt az egyik táblázatot beolvassa, majd minden egyes visszaküldött sor után egyéni kereséseket hajt végre a másik táblán. Ez kevésbé hatékony, minél nagyobb az adathalmaz, és különösen rossz azokban az esetekben, amikor a CommonWords tábla változót vizsgálja, mert több mint 60 000 keresést eredményez a Dracula táblázatváltozó. A beágyazott hurokcsatlakozás eléri a „csúcsteljesítménytelenséget” két, tízperces lekérdezésnél, a táblázat változó halmainak felhasználásával, mert ez több ezer tábla vizsgálatát vonja maga után CommonWords. Érdekes módon a két “Dracula-ban elterjedt szó” lekérdezés sokkal jobban teljesít, és ez azért van, mert e kettőnél az optimalizáló inkább Hash Match csatlakozást választott.

Összességében úgy tűnik, hogy a temp táblák a legjobb választás , de még nem fejeztük be! Adjuk hozzá a OPTION (RECOMPILE) tippet azokhoz a lekérdezésekhez, amelyek elsődleges kulcsokkal használják a táblázatváltozókat, és futtassa újra ezeket a lekérdezéseket, és az eredeti lekérdezéseket az ideiglenes táblák segítségével. Egyelőre kihagyjuk a gyenge kupacokat.

Amint láthatja, az ideiglenes tábla teljesítményelőnye eltűnik. Helyes sorszám és rendezett bemenet esetén az optimalizáló a sokkal hatékonyabb Merge Join lehetőséget választja.

Mi történne, ha ezeknek a szegény halmoknak megadná a OPTION (RECOMPILE) utalás is? Íme, számukra úgy változik a történet, hogy mindhárom időzítés sokkal közelebb álljon egymáshoz.

Érdekes módon a két “Drakulában gyakori szó” lekérdezés voltak gyors még halmokon is sokkal lassabb. A megfelelő sorszámmal felfegyverkezve az optimalizáló megváltoztatja a stratégiáját, de mivel korlátozások és kulcsok definiálásakor még mindig nem áll rendelkezésre a hasznos metaadatok egyike, rosszul dönt. Megvizsgálja a CommonWords kupacot, majd megkísérli a “részleges összesítést”, becsülve, hogy 60K-s sorokból néhány százra összesít. Nem tudja, hogy nincsenek duplikátumok, ezért valójában egyáltalán nem aggregálódik, és az összesítés és az azt követő összekapcsolódás a tempdb-re ömlik.

A tesztkészlet

Felhívjuk figyelmét, hogy ez a tesztkészlet végső formájában nagyjából azonos teljesítményt mutat a három különböző típusú táblához. El kell távolítania a OPTION (RECOMPILE) tippeket, hogy visszatérjen az eredetihez.

2. lista

Következtetések

Nincs semmi vakmerőség a táblázatváltozók használatában. Jobb teljesítményt nyújtanak, ha azokat a célokat használják, amelyekre szánták őket, és saját maguk készítik el a felpörgetést. Egy bizonyos ponton azok a kompromisszumok, amelyek jobb teljesítményt nyújtanak nekik (nem váltják ki az újrafordításokat, nem nyújtanak statisztikákat, nincs visszagörgetés, nincs párhuzamosság), bukássá válik. az eredmény nagysága, amely problémákat okoz egy tábla változónál. A cikkben bemutatott eredmények arra utalnak, hogy ez túlságosan leegyszerűsíti a problémákat. Két fontos tényező van: ha 1000, mondjuk mondjuk 1000 sor eredményei vannak (és ez a szám a kontextustól függ), akkor PRIMARY KEY vagy UNIQUE kulcs minden olyan lekérdezéshez, amely egy táblaváltozóhoz csatlakozik. Egy bizonyos ponton az újrafordítást is be kell váltania, hogy megkapja a tisztességes végrehajtási tervet, amelynek megvan a maga rezsije.

A teljesítmény akkor is súlyosan károsodhat, különösen, ha bonyolultabb feldolgozást végez , mert az optimalizáló még mindig nem fér hozzá a statisztikákhoz, így nincsenek ismeretei egyetlen lekérdezési predikátum szelektivitásának sem. Ilyen esetekben át kell váltani az ideiglenes táblázatok használatára.

További információk