Taulukon muuttujien ja väliaikaisten taulukoiden (ST011, ST012) valitseminen

Ihmiset voivat ja kiistävät paljon pöytämuuttujien ja väliaikaisten taulukoiden suhteellisista eduista. Joskus, kuten kirjoittaessasi toimintoja, sinulla ei ole muuta vaihtoehtoa; mutta kun huomaat, huomaat, että molemmilla on käyttötarkoituksensa, ja on helppo löytää esimerkkejä siitä, kumpi on nopeampaa. Tässä artikkelissa selitän tärkeimmät tekijät, jotka liittyvät yhden tai toisen valitsemiseen, ja esitän muutaman yksinkertaisen säännön parhaan suorituskyvyn saavuttamiseksi.

Olettaen, että noudatat sitoutumisen perussääntöjä , sinun tulisi harkita taulukon muuttujia ensimmäisenä vaihtoehtona työskennellessäsi suhteellisen pienten tietojoukkojen kanssa. Niiden kanssa on helpompi työskennellä ja ne aiheuttavat vähemmän uudelleenkääntämistä rutiineissa, joissa niitä käytetään, verrattuna väliaikaisten taulukoiden käyttöön. Taulukon muuttujat vaativat myös vähemmän lukitusresursseja, koska ne ovat ”yksityisiä” niitä luoneelle prosessille ja erälle. SQL Prompt toteuttaa tämän suosituksen koodianalyysisääntönä, ST011 – Harkitse taulukon muuttujan käyttöä väliaikaisen taulukon sijaan.

Jos käsittelet monimutkaisempia väliaikaisia tietoja tai sinun on käytettävä enemmän kuin kohtuullisen pieniä määriä tietoja, niin paikalliset väliaikaiset taulukot ovat todennäköisesti parempi valinta. SQL Code Guard sisältää koodianalyysisäännön, joka perustuu hänen suositukseensa ST012 – Harkitse väliaikaisen taulukon käyttöä taulukon muuttujan sijaan, mutta sitä ei tällä hetkellä ole otettu käyttöön SQL-kehotteessa.

Taulukon muuttujien ja väliaikaisten taulukoiden edut ja haitat

Taulukon muuttujilla on taipumus saada huonoa lehdistöä, koska niitä käyttävät kyselyt johtavat ajoittain erittäin tehottomiin toteutussuunnitelmiin. Jos kuitenkin noudatat muutamia yksinkertaisia sääntöjä, ne ovat hyvä valinta välitason ”työtaulukoihin” ja tulosten siirtämiseen rutiinien välillä, joissa tietojoukot ovat pieniä ja vaadittu käsittely on suhteellisen yksinkertaista.

Taulukon muuttujia on erittäin helppo käyttää, pääasiassa siksi, että ne ovat ”nolla ylläpitoa”. Ne määritetään erään tai rutiiniin, jossa ne on luotu, ja ne poistetaan automaattisesti, kun se on suoritettu loppuun, ja siten niiden käyttäminen pitkäikäisessä yhteydessä ei vaaranna resurssien hogging-ongelmia tempdb: ssä. Jos taulukon muuttuja ilmoitetaan tallennetussa menettelyssä, se on paikallinen kyseiselle tallennetulle menettelylle, eikä siihen voi viitata sisäkkäisessä menettelyssä. Taulukon muuttujiin ei myöskään ole tilastopohjaisia uudelleenkäännöksiä ja et voi ALTER yhtä, joten niitä käyttävissä rutiineissa on yleensä vähemmän käännöksiä kuin niissä, jotka käyttävät väliaikaisia taulukoita. Niitä ei myöskään ole täysin kirjattu, joten niiden luominen ja täyttäminen on nopeampaa ja vaatii vähemmän tilaa t: ssä ransaction log. Kun niitä käytetään tallennetuissa menettelyissä, järjestelmätaulukoissa on vähemmän kiistoja korkean samanaikaisuuden olosuhteissa. Lyhyesti sanottuna on helpompaa pitää asiat siistinä.

Kun työskentelet suhteellisen pienten tietojoukkojen kanssa, ne ovat nopeampi kuin vastaava väliaikainen taulukko. Rivien lukumäärän kasvaessa, kuitenkin noin 15 000 rivin yli, mutta vaihtelevat kontekstin mukaan, voit joutua vaikeuksiin pääasiassa siksi, että ne eivät tue tilastoja. Jopa hakemistoissa, jotka pakottavat PRIMARY KEY ja UNIQUE rajoitukset taulukon muuttujiin, ei ole tilastoja . Siksi optimoija käyttää kovakoodattua estimaattia yhdestä taulukon muuttujasta palautetusta rivistä ja pyrkii siten valitsemaan operaattorit, jotka ovat optimaalisia työskentelemään pienten tietojoukkojen kanssa (kuten Nested Loops -operaattori liittymiä varten). Mitä enemmän rivejä taulukon muuttujassa on, sitä suuremmat erot estimoinnin ja todellisuuden välillä ovat, ja tehottomammiksi tulevat optimoijan suunnitelmavalinnat. Tuloksena oleva suunnitelma on joskus pelottava.

Kokenut kehittäjä tai DBA etsii tällaista ongelmaa ja on valmis lisäämään OPTION (RECOMPILE) kyselyvihje lauseelle, joka käyttää taulukon muuttujaa. Kun lähetämme erän, joka sisältää taulukon muuttujan, optimoija kokoaa ensin erän, jossa taulukon muuttuja on tyhjä. Kun erä alkaa suorittaa, vihje aiheuttaa vain kyseisen yksittäisen lauseen kääntämisen uudelleen, jolloin taulukon muuttuja täytetään ja optimoija voi käyttää todellista rivien lukumäärää laatiakseen uuden suunnitelman tälle käskylle. Joskus, mutta harvoin, edes tämä ei auta. Myös liiallinen luottaminen tähän vihjeeseen mitätöi jossain määrin edun, joka taulukon muuttujilla on, mikä aiheuttaa vähemmän uudelleenkääntämistä kuin väliaikaiset taulukot.

Toiseksi tietyistä taulukon muuttujien indeksirajoituksista tulee enemmän tekijöitä käsiteltäessä suuret tietojoukot. Vaikka voit nyt käyttää sisäisen hakemistojen luomisen syntaksia luomaan ryhmittymättömiä hakemistoja taulukon muuttujalle, on joitain rajoituksia, eikä niihin liittyviä tilastoja ole vielä.

Jopa suhteellisen vaatimattomien rivien määrällä voit kohdata kyselyn suorituskykyongelmia, jos yrität suorittaa kyselyn, joka on liitos, ja unohdat määrittää PRIMARY KEY tai UNIQUE rajoitus liittymiseen käytetyssä sarakkeessa. Ilman tarjoamiaan metatietoja optimoijalla ei ole tietoa tietojen loogisesta järjestyksestä tai siitä, sisältävätkö liittymissarakkeen tiedot päällekkäisiä arvoja, ja todennäköisesti valita tehottomia liittymistoimintoja, mikä johtaa hitaisiin kyselyihin. Jos työskentelet taulukon muuttuvan kasan kanssa, voit käyttää sitä vain yksinkertaista luetteloa, joka todennäköisesti käsitellään yhdellä kerralla (taulukon skannaus). Jos yhdistät sekä OPTION (RECOMPILE) -vihjeen käytön tarkkojen kardinaaliarvioiden arvioimiseksi, että liitosarakkeen avaimen, jotta optimoija olisi hyödyllinen metatiedot, pienempien tietojoukkojen kohdalla voit usein saavuttaa kyselynopeuden, joka on samanlainen tai parempi kuin paikallisen väliaikaisen taulukon käyttö.

Kun rivien määrä kasvaa taulukon muuttujan mukavuusalueen ulkopuolella tai sinun on tehtävä monimutkaisempia tietoja käsittelyssä, sinun on parasta siirtyä käyttämään väliaikaisia taulukoita. Täällä sinulla on kaikki indeksointiin käytettävissä olevat vaihtoehdot, ja optimoijalla on ylellisyyttä käyttää tilastoja kullekin näistä hakemistoista. Tietenkin haittapuoli on, että väliaikaisilla pöydillä on korkeammat ylläpitokustannukset. Sinun on ehdottomasti puhdistettava itsesi jälkeen, jotta vältetään tempdb-ruuhkat. Jos muutat väliaikaista taulukkoa tai muokkaat niiden tietoja, saatat joutua kääntämään uudelleen ylätason rutiinin.

Väliaikaiset taulukot ovat parempia, kun vaaditaan suurta määrää poistoja ja lisäyksiä (rivisarjan jakaminen) ). Tämä pätee erityisesti, jos tiedot on poistettava kokonaan taulukosta, koska vain väliaikaiset taulukot tukevat katkaisua. Taulukon muuttujien suunnittelussa tehdyt kompromissit, kuten tilastojen ja uudelleen kääntämisen puute, toimivat niitä vastaan, jos tiedot ovat epävakaita.

Kun kannattaa käyttää taulukon muuttujia

Me Aloitan esimerkillä, jossa taulukon muuttuja on ihanteellinen ja johtaa parempaan suorituskykyyn. Laadimme luettelon Adventureworksin työntekijöistä, missä osastossa he työskentelevät ja missä vuoroissa he työskentelevät. Käsittelemme pientä tietojoukkoa (291 riviä).

Laitamme tulokset toiseen väliaikaiseen taulukkoon, ikään kuin välitämme tuloksen seuraavalle erälle. Listaus 1 näyttää koodin.

Ja tässä on tyypillinen tulos hitaalla testikoneellani:

Väliaikaisen taulukon käyttö on jatkuvasti hitaampaa, vaikka yksittäiset ajot voivat vaihdella melko paljon.

Mittakaavan ongelmat ja avaimen tai vihjeen antamisen unohtaminen

Millainen suorituskyky on, jos yhdistämme kaksi taulukon muuttujaa? Kokeillaan. Tässä esimerkissä tarvitaan kaksi yksinkertaista taulukkoa, joista toisessa on kaikki englannin kielen yleisimmät sanat (CommonWords), ja toisessa luettelo kaikista Bram Stokerin Draculan sanoista. (WordsInDracula). TestTVsAndTTs-lataus sisältää komentosarjan näiden kahden taulukon luomiseksi ja kunkin täyttämiseksi siihen liittyvästä tekstitiedostosta. Yleisiä sanoja on 60 000, mutta Bram Stoker käytti niitä vain 10000. Ensin mainittu on kaukana kannattavuusrajasta, jossa aletaan suosia väliaikaisia taulukoita.

Käytämme neljää yksinkertaista, ulompaa liittymiskyselyä, testaamalla tuloksen NULL arvot, selvittääksesi yleiset sanat, jotka eivät ole Draculassa, yleiset sanat, jotka ovat Draculassa, Draculan sanat, jotka ovat harvinaisia, ja lopuksi toinen kysely, jolla löydetään yleisiä sanoja Draculasta, mutta yhdistetään vastakkaiseen suuntaan. Näet kyselyt pian, kun näytän testilaitteen koodin.

Seuraavassa on ensimmäisten testiajojen tulokset. Ensimmäisessä ajassa molemmissa taulukon muuttujissa on ensisijaiset avaimet, ja toisessa ne ovat molemmat kasoja, vain sen selvittämiseksi, liioittelenko ongelmia, jos hakemistoa ei ilmoiteta taulukon muuttujassa. Lopuksi suoritamme samat kyselyt väliaikaisilla taulukoilla. Kaikki testit suoritettiin tarkoituksella hitaalla kehityspalvelimella havainnollistamista varten; saat hyvin erilaisia tuloksia tuotantopalvelimen kanssa.

Tulokset osoittavat, että kun taulukon muuttujat ovat kasoja, sinulla on riski, että kysely toimii kymmenen minuuttia eikä 100 millisekuntia. Nämä ovat loistava esimerkki kamala suorituskyvystä, jonka voit kokea, jos et tunne sääntöjä. Vaikka käytämme ensisijaisia avaimia, käsittelemiemme rivien lukumäärä tarkoittaa sitä, että väliaikaisten taulukkojen käyttö on nyt kaksinkertaista.

En aio syventää näiden takana olevia toteutussuunnitelmia. suorituskykymittareita, paitsi antaa muutama laaja selitys tärkeimmistä eroista. Lämpötilataulukoiden kyselyjä varten optimoija, joka on täysin tietoinen kardinaalisuudesta ja ensisijaisten avainten rajoituksista saatavista metatiedoista, valitsee tehokkaan Yhdistä liittyminen -operaattorin liittämistoiminnon suorittamiseksi.Ensisijaisilla avaimilla varustetun taulukon muuttujan optimoija tietää liittymissarakkeen rivien järjestyksen ja että ne eivät sisällä kaksoiskappaleita, mutta olettaa, että se käsittelee vain yhtä riviä ja valitsee sen sijaan sisäkkäisten silmukoiden liittymisen. Tässä se skannaa yhden taulukon ja suorittaa sitten jokaiselle palautetulle riville toisen taulukon yksittäiset hakutoiminnot. Tämä on vähemmän tehokasta, mitä isommat tietojoukot ovat, ja se on erityisen huono tapauksissa, joissa se skannaa taulukon muuttujaa CommonWords, koska se johtaa yli 60 000 hakemukseen Dracula taulukon muuttuja. Sisäkkäisten silmukoiden liittyminen saavuttaa ”huipputehokkuuden” kahdelle kymmenen minuutin kyselylle taulukon muuttujien kasoilla, koska se edellyttää tuhansia taulukon skannauksia CommonWords. Mielenkiintoista on, että ”Draculan yleiset sanat” -kyselyt toimivat paljon paremmin, ja tämä johtuu siitä, että optimoija valitsi näille kahdelle Hash Match -liittymän.

Kaiken kaikkiaan temp-taulukot näyttävät olevan paras valinta , mutta emme ole vielä valmiit! Lisätään OPTION (RECOMPILE) -vihje kyselyihin, jotka käyttävät taulukon muuttujia ensisijaisilla avaimilla, ja Suorita näiden kyselyjen testit ja alkuperäiset kyselyt väliaikaisilla taulukoilla. Jätämme toistaiseksi huonot kasat pois.

Kuten näette, väliaikaisen taulukon suorituskykyetu katoaa. Oikeat rivilaskelmat ja järjestetyt syötteet optimoija valitsee paljon tehokkaamman Yhdistä liittyminen.

Mitä tapahtuisi, jos antaisit niille köyhille kasoille OPTION (RECOMPILE) vihje myös? Lo, tarina muuttuu heille niin, että kaikki kolme ajoitusta ovat paljon lähempänä.

Kiinnostavaa on, että kaksi ”Draculan yleistä sanaa” kysyvät olivat nopeasti jopa kasoilla ovat nyt paljon hitaampia. Oikeilla riviluvuilla varustettuna optimoija muuttaa strategiaansa, mutta koska sillä ei ole vielä mitään hyödyllisiä metatietoja, kun määritämme rajoituksia ja avaimia, se tekee huonon valinnan. Se skannaa kasan CommonWords ja yrittää sitten ”osittaista aggregaatiota” arvioiden, että se kootaan 60 000 rivistä muutamaan sataan. Se ei tiedä, että kaksoiskappaleita ei ole, joten itse asiassa se ei koota lainkaan alaspäin, ja yhdistäminen ja sitä seuraava liittyminen vuotavat tempdb: hen.

Testilaite

Huomaa, että tämä on testilaite lopullisessa muodossaan näyttää suunnilleen samanlaisen suorituskyvyn kolmelle erityyppiselle taulukolle. Sinun on poistettava OPTION (RECOMPILE) -vihjeet palataksesi alkuperäiseen.

Listaus 2

Johtopäätökset

Taulukon muuttujien käytössä ei ole mitään holtittomia. Ne antavat paremman suorituskyvyn, kun niitä käytetään tarkoituksiin, joihin ne on tarkoitettu, ja he tekevät oman moppauksensa. Tietyssä vaiheessa kompromisseista, jotka antavat heille paremman suorituskyvyn (ei käynnistää uudelleenkääntämistä, ei toimiteta tilastoja, ei palautusta, ei rinnakkaisuutta), tulee heidän kaatumisensa.

Usein SQL Server -asiantuntija antaa neuvoja tuloksen koko, joka aiheuttaa ongelmia taulukon muuttujalle. Tulokset, jotka olen osoittanut sinulle tässä artikkelissa, viittaavat sinulle, että tämä yksinkertaistaa asioita liikaa. On kaksi tärkeää tekijää: jos tulos on yli, sanokaamme, 1000 riviä (ja tämä luku riippuu kontekstista), sinulla on oltava PRIMARY KEY tai UNIQUE -näppäin kyselyille, jotka liittyvät taulukon muuttujaan. Tietyssä vaiheessa joudut myös käynnistämään uudelleenkääntämisen saadaksesi kunnollisen suoritussuunnitelman, jolla on oma yleiskustannuksensa.

Silloinkin suorituskyky voi kärsiä pahasti, varsinkin jos suoritat monimutkaisempaa käsittelyä. , koska optimoijalla ei ole vieläkään pääsyä tilastoihin, joten ei tietoa minkään kysely predikaatin valikoivuudesta. Tällaisissa tapauksissa sinun on vaihdettava väliaikaisten taulukkojen käyttöön.

Lisätietoja