Alegerea între variabilele de tabel și tabelele temporare (ST011, ST012)

Oamenii pot argumenta foarte mult despre meritele relative ale variabilelor de tabel și ale tabelelor temporare. Uneori, ca și atunci când scrii funcții, nu ai de ales; dar când o veți face, veți descoperi că ambele își folosesc și este ușor să găsiți exemple în care oricare dintre ele este mai rapidă. În acest articol, voi explica principalii factori implicați în alegerea uneia sau alteia și voi demonstra câteva „reguli” simple pentru a obține cea mai bună performanță.

Presupunând că respectați regulile de bază de angajare , atunci ar trebui să luați în considerare variabilele de tabel ca o primă alegere atunci când lucrați cu seturi de date relativ mici. Sunt mai ușor de lucrat și declanșează mai puține recompilări în rutinele în care sunt utilizate, comparativ cu utilizarea tabelelor temporare. Variabilele de tabel necesită, de asemenea, mai puține resurse de blocare, deoarece sunt „private” pentru procesul și lotul care le-a creat. SQL Prompt implementează această recomandare ca regulă de analiză a codului, ST011 – Luați în considerare utilizarea variabilei de tabel în loc de tabel temporar.

Dacă efectuați prelucrări mai complexe pe date temporare sau trebuie să utilizați cantități mai mult decât rezonabile de mici datele din ele, atunci tabelele temporare locale sunt probabil o alegere mai bună. SQL Code Guard include o regulă de analiză a codului, bazată pe recomandarea sa, ST012 – Luați în considerare utilizarea tabelului temporar în locul variabilei de tabel, dar în prezent nu este implementată în SQL Prompt.

Pro și contra ale variabilelor de tabel și tabelelor temporare

Variabilele de tabel tind să aibă o „presă nepotrivită”, deoarece interogările care le folosesc ocazional duc la planuri de execuție foarte ineficiente. Cu toate acestea, dacă urmați câteva reguli simple, acestea sunt o alegere bună pentru tabelele intermediare „de lucru” și pentru transmiterea rezultatelor între rutine, în care seturile de date sunt mici și procesarea necesară este relativ simplă.

Variabilele de tabel sunt foarte ușor de utilizat, în principal pentru că sunt „întreținere zero”. Acestea sunt cuprinse în lotul sau rutina în care sunt create și sunt eliminate automat odată ce finalizează execuția și, astfel, folosindu-le într-o conexiune de lungă durată. nu riscă probleme de „hogging de resurse” în tempdb. Dacă o variabilă de tabel este declarată într-o procedură stocată, aceasta este locală la acea procedură stocată și nu poate fi referită într-o procedură imbricată. De asemenea, nu există recompilări bazate pe statistici pentru variabilele de tabel și nu puteți ALTER, așa că rutinele care le folosesc tind să aibă mai puține recompilări decât cele care folosesc tabele temporare. De asemenea, acestea nu sunt complet înregistrate, astfel încât crearea și completarea lor este mai rapidă necesită mai puțin spațiu în t jurnal de răscumpărare. Atunci când sunt utilizate în proceduri stocate, există mai puține dispute pe tabelele de sistem, în condiții de concurență ridicată. Pe scurt, este mai ușor să păstrezi lucrurile îngrijite și ordonate.

Când lucrezi cu seturi de date relativ mici, acestea sunt mai rapide decât tabelul temporar comparabil. Cu toate acestea, pe măsură ce numărul de rânduri crește, dincolo de aproximativ 15.000 de rânduri, dar variind în funcție de context, atunci puteți întâmpina dificultăți, în principal din cauza lipsei lor de suport pentru statistici. Chiar și indexurile care aplică PRIMARY KEY și UNIQUE constrângerile pentru variabilele de tabel nu au statistici . Prin urmare, optimizatorul va utiliza o estimare codificată de 1 rând returnat dintr-o variabilă de tabel și, astfel, va tinde să aleagă operatorii optimi pentru lucrul cu seturi de date mici (cum ar fi operatorul Bucle imbricate pentru îmbinări). Cu cât sunt mai multe rânduri în variabila de tabel, cu atât sunt mai mari discrepanțele dintre estimare și realitate și cu atât mai ineficiente devin opțiunile de plan ale optimizatorului. Planul rezultat este uneori înspăimântător.

Dezvoltatorul experimentat sau DBA va fi în căutarea acestui tip de problemă și va fi gata să adauge OPTION (RECOMPILE) sugestie de interogare pentru instrucțiunea care utilizează variabila de tabel. Când trimitem un lot care conține o variabilă de tabel, optimizatorul compilează mai întâi lotul, moment în care variabila de tabel este goală. Când lotul începe să se execute, sugestia va face recompilarea numai a acelei instrucțiuni, moment în care variabila de masă va fi completată, iar optimizatorul poate folosi numărul de rânduri reale pentru a compila un nou plan pentru acea instrucțiune. Uneori, dar rareori, nici acest lucru nu va ajuta. De asemenea, încrederea excesivă în acest indiciu va anula într-o oarecare măsură avantajul pe care îl au variabilele de tabel de a provoca mai puține recompilări decât tabelele temporare.

În al doilea rând, anumite limitări ale indexului cu variabilele de tabel devin mai mult un factor atunci când se tratează seturi mari de date. Deși acum puteți utiliza sintaxa de creare a indexului în linie pentru a crea indici ne-grupați pe o variabilă de tabel, există unele restricții și încă nu există statistici asociate.

Chiar și în cazul numărărilor de rânduri relativ modeste, puteți întâmpina probleme de performanță la interogare dacă încercați să executați o interogare care este o îmbinare și uitați să definiți un PRIMARY KEY sau UNIQUE constrângere pe coloana pe care o utilizați pentru unire. Fără metadatele pe care le furnizează, optimizatorul nu are cunoștințe despre ordinea logică a datelor sau dacă datele din coloana de asociere conțin valori duplicate și va alege probabil operațiuni de asociere ineficiente, rezultând interogări lente. Dacă lucrați cu o grămadă variabilă de masă, atunci o puteți folosi doar o listă simplă care este probabil să fie procesată într-o singură înghițitură (scanare de masă). Dacă combinați atât utilizarea diviziei OPTION (RECOMPILE), pentru estimări precise de cardinalitate, cât și o cheie din coloana de unire pentru a oferi optimizatorului util metadate, atunci pentru seturi de date mai mici puteți obține adesea viteze de interogare similare sau mai bune decât utilizarea unui tabel local temporar.

Odată ce numărul de rânduri crește dincolo de zona de confort a variabilei de tabel sau trebuie să faceți date mai complexe procesare, atunci cel mai bine treceți la utilizarea tabelelor temporare. Aici aveți la dispoziție opțiunile complete pentru indexare, iar optimizatorul va avea luxul de a utiliza statistici pentru fiecare dintre acești indici. Desigur, dezavantajul este că mesele temporare au un cost de întreținere mai mare. Trebuie să vă asigurați că vă lămuriți după dvs., pentru a evita aglomerația. Dacă modificați un tabel temporar sau modificați datele din acestea, este posibil să suportați recompilări ale rutinei părinte.

Tabelele temporare sunt mai bune atunci când există o cerință pentru un număr mare de ștergeri și inserții (partajarea setului de rânduri) ). Acest lucru este valabil mai ales dacă datele trebuie eliminate complet din tabel, deoarece doar tabelele temporare acceptă tăierea. Compromisurile în proiectarea variabilelor de tabel, cum ar fi lipsa de statistici și recompilări, acționează împotriva lor dacă datele sunt volatile.

Când este util să folosiți variabilele de tabel

Noi Voi începe cu un exemplu în care o variabilă de tabel este ideală și are ca rezultat o performanță mai bună. Vom produce o listă de angajați pentru Adventureworks, în ce departament lucrează și în schimburile în care lucrează. Avem de-a face cu un set mic de date (291 de rânduri).

Vom pune rezultatele într-un al doilea tabel temporar, ca și cum am fi transferat rezultatul la următorul lot. Listarea 1 arată codul.

Și iată un rezultat tipic pe mașina mea de testare lentă:

Utilizarea unui tabel temporar este constant mai lentă, deși rulările individuale pot varia destul de mult.

Problemele de scară și de uitare pentru a furniza o cheie sau un indiciu

Cum este performanța dacă unim două variabile de tabel? Să încercăm. Pentru acest exemplu, avem nevoie de două tabele simple, unul cu toate cuvintele obișnuite în limba engleză (CommonWords), iar celălalt cu o listă a tuturor cuvintelor din Dracula lui Bram Stoker. (WordsInDracula). Descărcarea TestTVsAndTTs include scriptul pentru crearea acestor două tabele și completarea fiecăruia din fișierul său text asociat. Există 60.000 de cuvinte comune, dar Bram Stoker a folosit doar 10.000 dintre ele. Primul este mult în afara punctului de echilibru, unde se începe să preferăm tabelele temporare.

Vom folosi patru interogări simple de îmbinare externe, testând rezultatul pentru NULL valori, pentru a afla cuvintele obișnuite care nu sunt în Dracula, cuvintele obișnuite care sunt în Dracula, cuvinte în Dracula care sunt mai puțin frecvente și, în cele din urmă, o altă interogare pentru a găsi cuvinte obișnuite în Dracula, dar care se alătură în direcția opusă. Veți vedea întrebările în curând, când voi afișa codul pentru dispozitivul de testare.

Următoarele sunt rezultatele testelor inițiale. În prima rundă, ambele variabile de tabel au chei primare, iar în a doua sunt ambele grămezi, doar pentru a vedea dacă exagerez problemele de a nu declara un index într-o variabilă de tabel. În cele din urmă, executăm aceleași interogări cu tabele temporare. Toate testele au fost efectuate, în mod deliberat, pe un server de dezvoltare lentă, în scop ilustrativ; veți obține rezultate foarte diferite cu un server de producție.

Rezultatele arată că atunci când variabilele tabelului sunt grămezi, riscați ca interogarea să ruleze timp de zece minute mai degrabă decât 100 de milisecunde. Acestea oferă un exemplu excelent al performanței teribile pe care o puteți experimenta dacă nu cunoașteți regulile. Chiar și atunci când folosim chei primare, totuși, numărul de rânduri cu care avem de-a face înseamnă că utilizarea tabelelor temporare este acum de două ori mai rapidă.

Nu voi intra în detaliile planurilor de execuție din spatele acestor măsurători de performanță, altele decât pentru a oferi câteva explicații largi ale principalelor diferențe. Pentru interogările din tabelul de temperatură, optimizatorul, înarmat cu o cunoaștere completă a cardinalității și a metadatelor din constrângerile cheii primare, alege un operator Merge Join eficient pentru a efectua operația de join.Pentru variabila de tabele cu chei primare, optimizatorul cunoaște ordinea rândurilor din coloana de asociere și că acestea nu conțin duplicate, dar presupune că are de-a face doar cu un rând și, prin urmare, alege o asociere cu bucle imbricate. Aici, scanează un tabel și apoi pentru fiecare rând returnat efectuează căutări individuale ale celuilalt tabel. Acest lucru devine mai puțin eficient cu cât sunt mai mari seturile de date și este deosebit de rău în cazurile în care scanează variabila de tabel CommonWords, deoarece are ca rezultat peste 60K căutări ale Dracula variabilă de tabel. Îmbinarea buclelor imbricate atinge „ineficiența maximă” pentru două interogări de zece minute folosind grămezi variabile de tabel, deoarece implică mii de scanări de tabel de CommonWords. Interesant este că cele două interogări „cuvinte obișnuite în Dracula” au performanțe mult mai bune și acest lucru se datorează faptului că, pentru acele două, optimizatorul a ales în schimb o alăturare Hash Match.

În general, tabelele temporare par a fi cea mai bună alegere , dar încă nu am terminat! Să adăugăm OPTION (RECOMPILE) indiciu la interogările care utilizează variabilele de tabel cu chei principale și reluați din nou testele pentru aceste interogări și interogările originale folosind tabelele temporare. Lăsăm deocamdată grămezi sărace.

După cum puteți vedea, avantajul de performanță al tabelului temporar dispare. Armat cu număr de rânduri corecte și intrări ordonate, optimizatorul alege îmbinarea îmbinării mult mai eficientă.

Ce, te întrebi, s-ar întâmpla dacă le-ai da acelor grămezi sărace OPTION (RECOMPILE) sugerează și el? Iată, povestea se schimbă pentru ei, astfel încât toate cele trei temporizări să fie mult mai apropiate.

Interesant, cele două „cuvinte comune din Dracula” interogă erau rapid chiar și pe grămezi sunt acum mult mai lente. Înarmat cu numărul corect de rânduri, optimizatorul își schimbă strategia, dar pentru că încă nu are disponibile metadatele utile atunci când definim constrângeri și chei, face o alegere proastă. Scanează heap-ul CommonWords apoi încearcă o „agregare parțială”, estimând că va agrega de la 60K rânduri la câteva sute. Nu știe că nu există duplicate, deci de fapt nu se agregează deloc, iar agregarea și îmbinarea ulterioară se revarsă la tempdb.

Dispozitivul de testare

Vă rugăm să rețineți că acesta este aparatul de testare în forma sa finală afișând performanțe aproximativ egale pentru cele trei tipuri diferite de tabel. Va trebui să eliminați OPTION (RECOMPILE) indicii pentru a reveni la original.

Listarea 2

Concluzii

Nu este nimic imprudent în utilizarea variabilelor de tabel. Acestea oferă o performanță mai bună atunci când sunt utilizate în scopurile pentru care au fost destinate și își fac propriile curățări. La un moment dat, compromisurile care le conferă o performanță mai bună (nu declanșează recompilări, nu furnizează statistici, nici o revenire, nici un paralelism) devin prăbușirea lor. dimensiunea rezultatului care va cauza probleme unei variabile de tabel. Rezultatele pe care vi le-am arătat în acest articol vă vor sugera că acest lucru simplifică excesiv problemele. Există doi factori importanți: dacă aveți un rezultat de peste, să spunem, 1000 de rânduri (și această cifră depinde de context), atunci trebuie să aveți un PRIMARY KEY sau UNIQUE cheie pentru orice interogare care se alătură unei variabile de tabel. La un moment dat, va trebui, de asemenea, să declanșați o recompilare pentru a obține un plan de execuție decent, care are propria cheltuieli generale.

Chiar și atunci, performanța poate suferi grav, mai ales dacă efectuați procesări mai complexe , deoarece optimizatorul încă nu are acces la statistici și, prin urmare, nu cunoaște selectivitatea vreunui predicat de interogare. În astfel de cazuri, va trebui să treceți la utilizarea tabelelor temporare.

Lecturi suplimentare