Velge mellom tabellvariabler og midlertidige tabeller (ST011, ST012)

Folk kan og kan argumentere mye for de relative fordelene ved tabellvariabler og midlertidige tabeller. Noen ganger, som når du skriver funksjoner, har du ikke noe valg; men når du gjør det, vil du oppdage at begge har sitt bruk, og det er enkelt å finne eksempler der begge er raskere. I denne artikkelen vil jeg forklare de viktigste faktorene som er involvert i å velge den ene eller den andre, og demonstrere noen få enkle regler for å oppnå best ytelse.

Forutsatt at du følger de grunnleggende regler for engasjement , så bør du vurdere tabellvariabler som et førstevalg når du arbeider med relativt små datasett. De er lettere å jobbe med, og de utløser færre rekompiler i rutinene de brukes i, sammenlignet med å bruke midlertidige tabeller. Tabellvariabler krever også færre låsingsressurser ettersom de er ‘private’ for prosessen og batchen som opprettet dem. SQL Prompt implementerer denne anbefalingen som en kodeanalyseregel, ST011 – Vurder å bruke tabellvariabel i stedet for midlertidig tabell.

Hvis du gjør mer kompleks behandling av midlertidige data, eller trenger å bruke mer enn rimelig små mengder data i dem, så vil lokale midlertidige tabeller sannsynligvis være et bedre valg. SQL Code Guard inkluderer en kodeanalyseregel, basert på hans anbefaling, ST012 – Vurder å bruke midlertidig tabell i stedet for tabellvariabel, men den er foreløpig ikke implementert i SQL Prompt.

Fordeler og ulemper med tabellvariabler og midlertidige tabeller

Tabellvariabler har en tendens til å bli «dårlig trykk», fordi spørsmål som bruker dem av og til resulterer i svært ineffektive gjennomføringsplaner. Imidlertid, hvis du følger noen få enkle regler, er de et godt valg for mellomliggende arbeidstabeller og for å overføre resultater mellom rutiner, der datasettene er små og den nødvendige behandlingen er relativt grei.

Tabellvariabler er veldig enkle å bruke, hovedsakelig fordi de er «null vedlikehold». De er avgrenset til batchen eller rutinen de ble opprettet i, og fjernes automatisk når den er fullført, og bruker dem i en langvarig forbindelse risikerer ikke «ressurs-hogging» -problemer i tempdb. Hvis en tabellvariabel blir deklarert i en lagret prosedyre, er den lokal for den lagrede prosedyren og kan ikke refereres til i en nestet prosedyre. Det er heller ingen statistikkbaserte rekompiler for tabellvariabler og du kan ikke ALTER en, så rutiner som bruker dem, har en tendens til å påføre færre rekompiler enn de som bruker midlertidige tabeller. De er heller ikke fullstendig logget, så det er raskere å opprette og fylle dem krever mindre plass i t løsepengelogg. Når de brukes i lagrede prosedyrer, er det mindre konflikt med systemtabeller under forhold med høy samtidighet. Kort fortalt er det lettere å holde ting pent og ryddig.

Når du jobber med relativt små datasett, er de raskere enn den sammenlignbare midlertidige tabellen. Imidlertid, når antall rader øker utover omtrent 15K rader, men varierer i henhold til kontekst, kan du komme i vanskeligheter, hovedsakelig på grunn av deres manglende støtte for statistikk. Selv indeksene som håndhever PRIMARY KEY og UNIQUE begrensninger på tabellvariabler, har ikke statistikk . Derfor vil optimalisereren bruke en hardkodet estimering av 1 rad returnert fra en tabellvariabel, og vil derfor ha en tendens til å velge operatører som er optimale for å jobbe med små datasett (for eksempel Nested Loops operator for join). Jo flere rader i tabellvariabelen, jo større avvik er det mellom estimering og virkelighet, og jo mer ineffektive blir optimeringsplanens valg. Den resulterende planen er noen ganger skremmende.

Den erfarne utvikleren eller DBA vil være på utkikk etter denne typen problemer, og være klar til å legge til OPTION (RECOMPILE) spørretips til setningen som bruker tabellvariabelen. Når vi sender inn en batch som inneholder en tabellvariabel, kompilerer optimalisereren først batchen på hvilket tidspunkt tabellvariabelen er tom. Når batchen begynner å kjøres, vil hintet føre til at bare den eneste setningen kompileres på nytt, på hvilket tidspunkt tabellvariabelen blir fylt ut og optimalisereren kan bruke den virkelige radtellingen til å kompilere en ny plan for denne setningen. Noen ganger, men sjelden, hjelper ikke dette. Også overdreven avhengighet av dette hintet vil til en viss grad forkaste fordelen som tabellvariabler har av å forårsake færre rekompiler enn midlertidige tabeller.

For det andre blir visse indeksbegrensninger med tabellvariabler mer av en faktor når du arbeider med store datasett. Mens du nå kan bruke den innebygde syntaksen for å lage indekser for å lage ikke-grupperte indekser på en tabellvariabel, er det noen begrensninger, og det er fortsatt ingen tilknyttet statistikk.

Selv med relativt beskjedne radtall, kan du støte på problemer med spørringsytelse hvis du prøver å utføre et spørsmål som er en sammenkobling, og du glemmer å definere en PRIMARY KEY eller UNIQUE begrensning på kolonnen du bruker for sammenføyningen. Uten metadataene de gir, har ikke optimalisereren kunnskap om den logiske rekkefølgen på dataene, eller om dataene i sammenføyningskolonnen inneholder dupliserte verdier, og vil sannsynligvis velge ineffektive sammenføyningsoperasjoner, noe som resulterer i langsomme spørsmål. Hvis du jobber med en tabellvariabelbunke, kan du bare bruke den til en enkel liste som sannsynligvis vil bli behandlet i en enkelt slurk (tabellskanning). Hvis du kombinerer både bruk av OPTION (RECOMPILE) hint, for nøyaktige kardinalitetsestimater, og en nøkkel i sammenføyningskolonnen for å gi optimalisereren nyttig metadata, for mindre datasett kan du ofte oppnå spørrehastigheter som ligner på eller bedre enn å bruke en lokal midlertidig tabell.

Når radtallene øker utover komfortsonen til en tabellvariabel, eller du må gjøre mer komplekse data behandling, så bytter du best til å bruke midlertidige tabeller. Her har du de fulle tilgjengelige alternativene for indeksering, og optimalisereren vil ha den luksusen å bruke statistikk for hver av disse indeksene. Selvfølgelig er ulempen at midlertidige bord har høyere vedlikeholdskostnader. Du må sørge for å rydde opp etter deg selv, for å unngå tempdb-overbelastning. Hvis du endrer en midlertidig tabell eller endrer dataene i dem, kan det hende at du får på nytt rekompiler av den overordnede rutinen.

Midlertidige tabeller er bedre når det er behov for et stort antall slettinger og innsettinger (deling av radsett) ). Dette gjelder spesielt hvis dataene må fjernes helt fra tabellen, da bare midlertidige tabeller støtter avkorting. Kompromissene i utformingen av tabellvariabler, som mangel på statistikk og rekompiler, virker mot dem hvis dataene er ustabile.

Når det lønner seg å bruke tabellvariabler

Vi Begynn med et eksempel der en tabellvariabel er ideell, og gir bedre ytelse. Vi vil lage en liste over ansatte for Adventureworks, hvilken avdeling de jobber i, og skiftene de jobber. Vi har å gjøre med et lite datasett (291 rader).

Vi legger resultatene i en annen midlertidig tabell, som om vi overfører resultatet til neste batch. Oppføring 1 viser koden.

Og her er et typisk resultat på min sakte testmaskin:

Å bruke en midlertidig tabell er jevnt langsommere, selv om individuelle kjøringer kan variere ganske mye.

Problemer med å skalere og glemme å gi en nøkkel eller et hint

Hvordan er ytelsen hvis vi blir sammen med to tabellvariabler? La oss prøve det. For dette eksemplet trenger vi to enkle tabeller, en med alle vanlige ord på engelsk (CommonWords), og den andre med en liste over alle ordene i Bram Stokers Dracula (WordsInDracula). TestTVsAndTTs-nedlastingen inkluderer skriptet for å lage disse to tabellene og fylle ut hver fra den tilknyttede tekstfilen. Det er 60 000 vanlige ord, men Bram Stoker brukte bare 10 000 av dem. Førstnevnte er godt utenfor break-even-punktet, hvor man begynner å foretrekke midlertidige tabeller.

Vi bruker fire enkle, ytre sammenføyningsspørsmål, og tester resultatet for NULL verdier, for å finne ut de vanlige ordene som ikke er i Dracula, vanlige ord som er i Dracula, ord i Dracula som er uvanlige, og til slutt et annet spørsmål om å finne vanlige ord i Dracula, men slutter seg i motsatt retning. Du ser spørsmålene snart, når jeg viser koden for testriggen.

Følgende er resultatene av de første testkjøringene. I første omgang har begge tabellvariablene primære nøkler, og i det andre er de begge bunker, bare for å se om jeg overdriver problemene med å ikke erklære en indeks i en tabellvariabel. Til slutt kjører vi de samme spørsmålene med midlertidige tabeller. Alle testene ble kjørt bevisst på en server med langsom utvikling, for illustrasjonsformål; du vil få veldig forskjellige resultater med en produksjonsserver.

Resultatene viser at når tabellvariablene er store, risikerer du at spørringen kjører i ti minutter i stedet for 100 millisekunder. Disse gir et godt eksempel på den uhyggelige ytelsen du kan oppleve hvis du ikke kjenner reglene. Selv når vi bruker primærnøkler, betyr antall rader vi har med å gjøre at bruk av midlertidige tabeller nå er dobbelt så raskt.

Jeg vil ikke fordype detaljene i utførelsesplanene bak disse ytelsesberegninger, annet enn å gi noen få brede forklaringer på hovedforskjellene. For temp-tabellspørsmålene velger optimizer, bevæpnet med full kunnskap om kardinalitet og metadata fra de primære nøkkelbegrensningene, en effektiv Merge Join-operatør for å utføre sammenkoblingsoperasjonen.For tabellene som er variabelt med primærnøkler, kjenner optimalisereren rekkefølgen på radene i sammenføyningskolonnen, og at de ikke inneholder duplikater, men antar at den bare har å gjøre med en rad, og velger i stedet en nestet løkker. Her skanner den en tabell og utfører deretter individuelle søk etter den andre tabellen for hver rad som returneres. Dette blir mindre effektivt jo større datasettene er, og er spesielt ille i tilfeller der det skanner CommonWords tabellvariabelen, fordi det resulterer i over 60 000 søk etter Dracula tabellvariabel. The Nested Loops join når peak inefficiency for to, ti minutters spørsmål ved å bruke tabellvariabler, fordi det innebærer tusenvis av tabellskanninger av CommonWords. Interessant, de to «vanlige ordene i Dracula» -spørsmålene gir mye bedre resultater, og dette er fordi for disse to valgte optimalisereren i stedet en Hash Match-sammenkobling. , men vi er ikke ferdige ennå! La oss legge til OPTION (RECOMPILE) hint til spørsmålene som bruker tabellvariablene med primærnøkler, og kjør testene for disse spørsmålene og de originale spørsmålene ved hjelp av de midlertidige tabellene. Vi utelater foreløpig de dårlige dyngene.

Som du kan se, forsvinner ytelsesfordelen med den midlertidige tabellen. Bevæpnet med riktig radtall og bestilte innganger, velger optimalisereren den langt mer effektive Merge Join.

Hva, lurer du på, ville skje hvis du ga de dårlige dyngene OPTION (RECOMPILE) hint også? Se, historien endres for dem slik at alle tre tidsinnstillinger er mye nærmere.

Interessant, de to «vanlige ordene i Dracula» spør om var raskt selv på dynger er nå mye tregere. Bevæpnet med riktig antall teller endrer optimalisereren sin strategi, men fordi den fremdeles ikke har noen av de nyttige metadataene tilgjengelig når vi definerer begrensninger og nøkler, gjør det et dårlig valg. Den skanner CommonWords bunken og prøver deretter en «delvis aggregering», og estimerer at den vil samle seg ned fra 60 000 rader til noen få hundre. Den vet ikke at det ikke er noen duplikater, så faktisk samler det seg ikke i det hele tatt, og aggregeringen og påfølgende blir med spill til tempdb.

Testriggen

Vær oppmerksom på at dette er testriggen i sin endelige form som viser omtrent like ytelse for de tre forskjellige typene tabeller. Du må fjerne OPTION (RECOMPILE) tips for å komme tilbake til originalen.

Oppføring 2

Konklusjoner

Det er ingenting hensynsløst å bruke tabellvariabler. De gir bedre ytelse når de brukes til de formålene de var ment for, og de gjør sin egen mopping-up. På et visst tidspunkt blir kompromissene som gir dem bedre ytelse (ikke utløser rekompiler, ikke gir statistikk, ingen tilbakestilling, ingen parallellitet) deres undergang.

Ofte vil eksperten på SQL Server gi vismannsråd om størrelsen på resultatet som vil forårsake problemer for en tabellvariabel. Resultatene jeg har vist deg i denne artikkelen, vil foreslå deg at dette forenkler problemene. Det er to viktige faktorer: Hvis du har et resultat på over, la oss si, 1000 rader (og denne figuren avhenger av sammenheng), må du ha en PRIMARY KEY eller UNIQUE nøkkel for alle spørsmål som kobles til en tabellvariabel. På et bestemt tidspunkt må du også utløse en kompilering for å få en anstendig utførelsesplan, som har sin egen overhead.

Selv da kan ytelsen lide dårlig, spesielt hvis du utfører mer kompleks behandling , fordi optimalisereren fremdeles ikke har tilgang til statistikk, og så ingen kunnskap om selektiviteten til et spørringspredikat. I slike tilfeller må du bytte til bruk av midlertidige tabeller.

Videre lesing