Valg mellem tabelvariabler og midlertidige tabeller (ST011, ST012)

Folk kan og kan argumentere meget for de relative fordele ved tabelvariabler og midlertidige tabeller. Nogle gange, som når du skriver funktioner, har du intet valg; men når du gør det, finder du ud af, at begge har deres anvendelser, og det er nemt at finde eksempler, hvor en af dem er hurtigere. I denne artikel vil jeg forklare de vigtigste faktorer, der er involveret i at vælge den ene eller den anden, og demonstrere et par enkle regler for at få den bedste præstation.

Forudsat at du følger de grundlæggende regler for engagement , så skal du overveje tabelvariabler som et førstevalg, når du arbejder med relativt små datasæt. De er lettere at arbejde med, og de udløser færre rekompiler i de rutiner, de bruges i, sammenlignet med brug af midlertidige tabeller. Tabelvariabler kræver også færre låseressourcer, da de er private for den proces og batch, der oprettede dem. SQL Prompt implementerer denne anbefaling som en kodeanalyseregel, ST011 – Overvej at bruge tabelvariabel i stedet for midlertidig tabel.

Hvis du laver mere kompleks behandling af midlertidige data eller har brug for mere end rimelig små mængder data i dem, så vil lokale midlertidige tabeller sandsynligvis være et bedre valg. SQL Code Guard indeholder en kodeanalyseregel, der er baseret på hans anbefaling, ST012 – Overvej at bruge midlertidig tabel i stedet for tabelvariabel, men den er i øjeblikket ikke implementeret i SQL Prompt.

Fordele og ulemper ved tabelvariabler og midlertidige tabeller

Tabelvariabler har tendens til at blive dårlig presse, fordi forespørgsler, der bruger dem lejlighedsvis resulterer i meget ineffektive udførelsesplaner. Men hvis du følger et par enkle regler, er de et godt valg til mellemliggende arbejdstabeller og til at videregive resultater mellem rutiner, hvor datasættene er små, og den nødvendige behandling er relativt ligetil.

Tabelvariabler er meget enkle at bruge, hovedsageligt fordi de er “nul vedligeholdelse”. De afgrænses til den batch eller rutine, hvori de oprettes, og fjernes automatisk, når den er færdig med udførelsen, og bruger dem således inden for en langvarig forbindelse risikerer ikke ressource hogging problemer i tempdb. Hvis en tabelvariabel erklæres i en lagret procedure, er den lokal for den lagrede procedure og kan ikke refereres til i en indlejret procedure. Der er heller ingen statistikbaserede rekompiler for tabelvariabler og du kan ikke ALTER en, så rutiner, der bruger dem, har en tendens til at medføre færre rekompiler end dem, der bruger midlertidige tabeller. De er heller ikke fuldt logget, så oprettelse og udfyldning af dem er hurtigere og kræver mindre plads i t løsladelseslog. Når de bruges i lagrede procedurer, er der mindre strid på systemtabeller under forhold med høj samtidighed. Kort sagt er det lettere at holde tingene pæne og ryddelige.

Når du arbejder med relativt små datasæt, er de hurtigere end den sammenlignelige midlertidige tabel. Efterhånden som antallet af rækker stiger ud over ca. 15K rækker, men varierer alt efter kontekst, kan du imidlertid komme i vanskeligheder, hovedsageligt på grund af deres manglende støtte til statistik. Selv de indekser, der håndhæver PRIMARY KEY og UNIQUE begrænsninger for tabelvariabler, har ikke statistik . Optimizer bruger derfor et hårdkodet estimat på 1 række, der returneres fra en tabelvariabel, og vil derfor have tendens til at vælge operatører, der er optimale til at arbejde med små datasæt (f.eks. Nested Loops-operator til sammenføjning). Jo flere rækker i tabelvariablen, jo større uoverensstemmelser mellem estimering og virkelighed, og jo mere ineffektive bliver optimeringsplanens valg. Den resulterende plan er undertiden skræmmende.

Den erfarne udvikler eller DBA vil være på udkig efter denne slags problemer og være klar til at tilføje OPTION (RECOMPILE) forespørgselstip til udsagnet, der bruger tabelvariablen. Når vi sender en batch, der indeholder en tabelvariabel, kompilerer optimizer først batchen, på hvilket tidspunkt tabelvariablen er tom. Når batchen begynder at køre, får tipet kun den enkelte sætning til at kompilere igen, på hvilket tidspunkt tabelvariablen udfyldes, og optimeringsprogrammet kan bruge det rigtige rækkeantal til at kompilere en ny plan for denne erklæring. Nogle gange, men sjældent, hjælper ikke selv dette. Også overdreven afhængighed af dette tip vil til en vis grad forkaste den fordel, som tabelvariabler har ved at forårsage færre rekompiler end midlertidige tabeller.

For det andet bliver visse indeksbegrænsninger med tabelvariabler mere af en faktor, når man beskæftiger sig med store datasæt. Mens du nu kan bruge den indbyggede syntaks til oprettelse af indeks til at oprette ikke-klyngede indekser på en tabelvariabel, er der nogle begrænsninger, og der er stadig ingen tilknyttede statistikker.

Selv med relativt beskedne rækkeoptællinger kan du støde på problemer med forespørgselsydeevne, hvis du prøver at udføre en forespørgsel, der er en sammenføjning, og du glemmer at definere en PRIMARY KEY eller UNIQUE begrænsning for den kolonne, du bruger til sammenføjningen. Uden de metadata, de leverer, har optimizer intet kendskab til den logiske rækkefølge af dataene, eller om dataene i sammenføjningskolonnen indeholder duplikatværdier og vil sandsynligvis vælge ineffektive tilslutningsoperationer, hvilket resulterer i langsomme forespørgsler. Hvis du arbejder med en tabelvariabel bunke, kan du kun bruge den til en simpel liste, der sandsynligvis vil blive behandlet i en enkelt gulp (tabel scanning). Hvis du kombinerer begge brug af OPTION (RECOMPILE) tip, for nøjagtige kardinalitetsestimater og en nøgle i sammenføjningskolonnen for at give optimeringsværktøjet nyttigt metadata, så for mindre datasæt kan du ofte opnå forespørgselshastigheder, der svarer til eller bedre end at bruge en lokal midlertidig tabel.

Når rækkeoptællinger stiger ud over en tabelvariabels komfortzone, eller du skal udføre mere komplekse data behandling, så skifter du bedst til at bruge midlertidige tabeller. Her har du de fulde muligheder, der er tilgængelige for dig til indeksering, og optimizer vil have den luksus at bruge statistik til hvert af disse indekser. Selvfølgelig er ulempen, at midlertidige borde har højere vedligeholdelsesomkostninger. Du skal sørge for at rydde op efter dig selv for at undgå tempdb-overbelastning. Hvis du ændrer en midlertidig tabel eller ændrer dataene i dem, kan du påtage dig genkompileringer af den overordnede rutine.

Midlertidige tabeller er bedre, når der er behov for et stort antal sletninger og indsættelser (rækkesætdeling ). Dette gælder især, hvis dataene skal fjernes fuldstændigt fra tabellen, da kun midlertidige tabeller understøtter trunkering. Kompromiserne i designet af tabelvariabler, såsom mangel på statistik og rekompiler, virker imod dem, hvis dataene er ustabile.

Når det lønner sig at bruge tabelvariabler

Vi Start med et eksempel, hvor en tabelvariabel er ideel og resulterer i bedre ydeevne. Vi vil udarbejde en liste over medarbejdere til Adventureworks, hvilken afdeling de arbejder i og de skift, de arbejder. Vi har at gøre med et lille datasæt (291 rækker).

Vi placerer resultaterne i en anden midlertidig tabel, som om vi videregiver resultatet til næste batch. Liste 1 viser koden.

Og her er et typisk resultat på min langsomt testmaskine:

Brug af en midlertidig tabel er konsekvent langsommere, selvom individuelle kørsler kan variere ret meget.

Problemer med at skalere og glemme at give en nøgle eller et tip

Hvordan er præstationen, hvis vi forbinder to tabelvariabler? Lad os prøve det. Til dette eksempel har vi brug for to enkle tabeller, en med alle de almindelige ord på det engelske sprog (CommonWords), og den anden med en liste over alle ordene i Bram Stokers Dracula (WordsInDracula). TestTVsAndTTs-download inkluderer scriptet til at oprette disse to tabeller og udfylde hver enkelt fra den tilknyttede tekstfil. Der er 60.000 almindelige ord, men Bram Stoker brugte kun 10.000 af dem. Førstnævnte ligger langt uden for break-even-punktet, hvor man begynder at foretrække midlertidige tabeller.

Vi bruger fire enkle, ydre sammenføjningsforespørgsler og tester resultatet for NULL værdier, for at finde ud af de almindelige ord, der ikke er i Dracula, almindelige ord, der er i Dracula, ord i Dracula, der er usædvanlige, og endelig en anden forespørgsel for at finde almindelige ord i Dracula, men slutter sig i den modsatte retning. Du vil se forespørgslerne snart, når jeg viser koden til testriggen.

Følgende er resultaterne af de første testkørsler. I det første løb har begge tabelvariabler primære nøgler, og i det andet er de begge masser, bare for at se om jeg overdriver problemerne med ikke at erklære et indeks i en tabelvariabel. Endelig kører vi de samme forespørgsler med midlertidige tabeller. Alle tests blev bevidst kørt på en server med langsom udvikling med henblik på illustration; du får meget forskellige resultater med en produktionsserver.

Resultaterne viser, at når tabelvariablerne er dynger, løber du risikoen for, at forespørgslen kører i ti minutter i stedet for 100 millisekunder. Disse giver et godt eksempel på den uhyggelige præstation, du kan opleve, hvis du ikke kender reglerne. Selv når vi bruger primære nøgler, betyder antallet af rækker, vi har at gøre med, at brugen af midlertidige tabeller nu er dobbelt så hurtig.

Jeg vil ikke dykke ned i detaljerne i udførelsesplanerne bag disse præstationsmålinger, bortset fra at give et par brede forklaringer på de vigtigste forskelle. Til temp-tabel-forespørgsler vælger optimizer, bevæbnet med en fuld viden om kardinalitet og metadata fra de primære nøglebegrænsninger, en effektiv Merge Join-operatør til at udføre sammenkædningsoperationen.For tabellerne, der er variabelt med primære nøgler, kender optimizer rækkefølgen af rækkerne i tilslutningskolonnen, og at de ikke indeholder duplikater, men antager, at det kun handler om en række, og vælger derfor i stedet en indlejret loops. Her scanner den en tabel og udfører derefter individuelle søger efter den anden tabel for hver række, der returneres. Dette bliver mindre effektivt, jo større datasættene er og er især dårligt i de tilfælde, hvor det scanner CommonWords tabelvariablen, fordi det resulterer i over 60K søgninger af Dracula tabelvariabel. De indlejrede loops sammenføjninger når peak ineffektivitet for to, ti minutters forespørgsler ved hjælp af tabelvariabelle dynger, fordi det medfører tusindvis af tabel scanninger af CommonWords. Interessant nok fungerer de to “almindelige ord i Dracula” -forespørgsler meget bedre, og det er fordi optimizer for disse to i stedet valgte en Hash Match-sammenkædning.

Samlet set ser temp-tabellerne ud til at være det bedste valg , men vi er ikke færdige endnu! Lad os tilføje OPTION (RECOMPILE) tip til de forespørgsler, der bruger tabelvariablerne med de primære nøgler, og kør testene for disse forespørgsler og de originale forespørgsler ved hjælp af de midlertidige tabeller. Vi udelader de dårlige dynger for tiden.

Som du kan se, forsvinder fordelene ved den midlertidige tabel. Bevæbnet med korrekte rækkeoptællinger og ordnede indgange, optimeringsprogrammet vælger den langt mere effektive Flet sammenføjning.

Hvad, undrer du dig over, ville ske, hvis du gav de dårlige dynger OPTION (RECOMPILE) antydning også? Se, historien ændres for dem, så alle tre tidspunkter er meget tættere.

Interessant nok spørger de to “almindelige ord i Dracula”, var hurtigt selv på dynger er nu meget langsommere. Bevæbnet med de korrekte rækkeoptællinger ændrer optimizer sin strategi, men fordi den stadig ikke har nogen af de nyttige metadata til rådighed, når vi definerer begrænsninger og nøgler, gør det et dårligt valg. Den scanner CommonWords bunken og forsøger derefter en “delvis sammenlægning” og estimerer, at den vil samle sig ned fra 60K rækker til et par hundrede. Det ved ikke, at der ikke er nogen dubletter, så faktisk aggregerer det slet ikke ned, og aggregering og efterfølgende deltager spild til tempdb.

Testrigget

Bemærk, at dette er testriggen i sin endelige form viser nogenlunde lige ydeevne for de tre forskellige typer borde. Du skal fjerne OPTION (RECOMPILE) tip for at komme tilbage til originalen.

Liste 2

Konklusioner

Der er intet hensynsløst ved at bruge tabelvariabler. De giver en bedre ydeevne, når de bruges til de formål, de var beregnet til, og de laver deres egen opsamling. På et bestemt tidspunkt bliver kompromiserne, der giver dem en bedre ydeevne (ikke udløser rekompiler, ikke leverer statistik, ingen tilbagevenden, ingen parallelisme) deres undergang.

Ofte vil SQL Server-pundit give vismandsråd om størrelsen på resultatet, der vil skabe problemer for en tabelvariabel. De resultater, jeg har vist dig i denne artikel, vil foreslå dig, at dette forenkler problemerne. Der er to vigtige faktorer: Hvis du har et resultat af over, lad os sige, 1000 rækker (og dette tal afhænger af kontekst), skal du have en PRIMARY KEY eller UNIQUE nøgle til forespørgsler, der slutter sig til en tabelvariabel. På et bestemt tidspunkt bliver du også nødt til at udløse en genkompilering for at få en anstændig udførelsesplan, der har sin egen overhead.

Selv da kan ydeevne lide dårligt, især hvis du udfører mere kompleks behandling , fordi optimeringsprogrammet stadig ikke har adgang til statistik, og derfor ikke har kendskab til selektiviteten af et forespørgselspredikat. I sådanne tilfælde skal du skifte til at bruge midlertidige tabeller.

Yderligere læsning