Att välja mellan tabellvariabler och tillfälliga tabeller (ST011, ST012)

Människor kan, och gör, argumentera mycket om de relativa fördelarna med tabellvariabler och tillfälliga tabeller. Ibland, som när du skriver funktioner, har du inget val; men när du gör det kommer du att upptäcka att båda har sina användningsområden, och det är lätt att hitta exempel där någon av dem är snabbare. I den här artikeln kommer jag att förklara de viktigaste faktorerna som är inblandade i att välja den ena eller den andra och demonstrera några enkla regler för att få bästa resultat.

Om du följer de grundläggande reglerna för engagemang , bör du överväga tabellvariabler som ett första val när du arbetar med relativt små datamängder. De är lättare att arbeta med och utlöser färre rekompiler i de rutiner de används i jämfört med att använda tillfälliga tabeller. Tabellvariabler kräver också färre låseresurser eftersom de är ”privata” för processen och batchen som skapade dem. SQL Prompt implementerar denna rekommendation som en kodanalysregel, ST011 – Överväg att använda tabellvariabel istället för tillfällig tabell.

Om du gör mer komplex bearbetning av tillfälliga data eller behöver använda mer än rimligt små mängder data i dem, då troligen lokala tillfälliga tabeller är ett bättre val. SQL Code Guard innehåller en kodanalysregel, baserad på hans rekommendation, ST012 – Överväg att använda tillfällig tabell istället för tabellvariabel, men den är för närvarande inte implementerad i SQL Prompt.

Fördelar och nackdelar med tabellvariabler och tillfälliga tabeller

Tabellvariabler tenderar att bli ”dålig press”, eftersom frågor som använder dem ibland resulterar i mycket ineffektiva exekveringsplaner. Om du följer några enkla regler är de dock ett bra val för mellanliggande arbetstabeller och för att skicka resultat mellan rutiner, där datamängderna är små och den bearbetning som krävs är relativt enkel.

Tabellvariabler är väldigt enkla att använda, främst för att de är ”utan underhåll”. De omfattas av den batch eller rutin som de skapas i och tas bort automatiskt när den har slutfört körningen och använder dem i en långvarig anslutning riskerar inte ”resurshugg” -problem i tempdb. Om en tabellvariabel deklareras i en lagrad procedur är den lokal för den lagrade proceduren och kan inte refereras till i en kapslad procedur. Det finns inte heller några statistikbaserade rekompiler för tabellvariabler och du kan inte ALTER en, så rutiner som använder dem tenderar att medföra färre återkompiler än de som använder tillfälliga tabeller. De är inte helt loggade, så att skapa och fylla dem är snabbare och kräver mindre utrymme i t ransaction logg. När de används i lagrade procedurer är det mindre strid på systemtabellerna under förhållanden med hög samtidighet. Kort sagt, det är lättare att hålla sakerna snygga och snygga.

När du arbetar med relativt små datamängder är de snabbare än jämförbar tillfällig tabell. Men när antalet rader ökar utöver cirka 15 000 rader, men varierar beroende på kontext, kan du stöta på svårigheter, främst på grund av deras brist på stöd för statistik. Även index som tvingar fram PRIMARY KEY och UNIQUE begränsningar för tabellvariabler har ingen statistik . Därför kommer optimeraren att använda en hårdkodad uppskattning av 1 rad som returneras från en tabellvariabel och tenderar därför att välja operatörer som är optimala för att arbeta med små datamängder (till exempel Nested Loops-operatören för sammanfogningar). Ju fler rader i tabellvariabeln, desto större är avvikelserna mellan uppskattning och verklighet och desto mer ineffektiva blir optimeringsplanens val. Den resulterande planen är ibland skrämmande.

Den erfarna utvecklaren eller DBA kommer att leta efter denna typ av problem och vara redo att lägga till OPTION (RECOMPILE) frågetips till uttalandet som använder tabellvariabeln. När vi skickar ett parti som innehåller en tabellvariabel kompilerar optimeraren först batchen vid vilken tidpunkt tabellvariabeln är tom. När batchen körs kommer ledtråden att endast det enskilda uttalandet kommer att kompileras igen, vid vilken tidpunkt tabellvariabeln fylls i och optimeraren kan använda det verkliga radantalet för att sammanställa en ny plan för det uttalandet. Ibland men sällan hjälper inte ens detta. Dessutom kommer överberoende av denna ledtråd att i viss mån förneka den fördel som tabellvariabler har av att orsaka färre återkompileringar än tillfälliga tabeller.

För det andra blir vissa indexbegränsningar med tabellvariabler mer av en faktor när man hanterar stora datamängder. Medan du nu kan använda syntaxen för inbyggt index för att skapa icke-klustrade index på en tabellvariabel finns det vissa begränsningar och det finns fortfarande ingen tillhörande statistik.

Även med relativt blygsamma radantal kan du stöta på frågor om prestandafrågor om du försöker utföra en fråga som är en koppling och du glömmer att definiera en PRIMARY KEY eller UNIQUE begränsning för den kolumn du använder för kopplingen. Utan de metadata som de tillhandahåller har optimeraren ingen kunskap om den logiska ordningen på data, eller om data i kopplingskolumnen innehåller dubbla värden och kommer sannolikt att välja ineffektiva kopplingsåtgärder, vilket resulterar i långsamma frågor. Om du arbetar med en tabellvariabelhöjd kan du bara använda den en enkel lista som sannolikt kommer att bearbetas i en enda sluk (tabellsökning). Om du kombinerar båda användningen av OPTION (RECOMPILE) tipset för exakta kardinalitetsuppskattningar och en nyckel i kopplingskolumnen för att ge optimeraren användbar metadata kan du för mindre datamängder ofta uppnå fråghastigheter som liknar eller är bättre än att använda en lokal tillfällig tabell.

När radantalet ökar utöver en tabellvariabels komfortzon, eller så måste du göra mer komplexa data bearbetning, då byter du bäst för att använda tillfälliga tabeller. Här har du alla tillgängliga alternativ för indexering, och optimeraren har lyxen att använda statistik för vart och ett av dessa index. Självklart är nackdelen att tillfälliga bord har en högre underhållskostnad. Du måste se till att rensa efter dig för att undvika trängsel i tempdb. Om du ändrar en tillfällig tabell eller ändrar data i dem kan du få omkompiler av den överordnade rutinen.

Tillfälliga tabeller är bättre när det finns ett krav på ett stort antal raderingar och insättningar (delning av radset ). Detta gäller särskilt om data måste tas bort helt från tabellen, eftersom endast temporära tabeller stöder trunkering. Kompromisserna i utformningen av tabellvariabler, som brist på statistik och återkompilering, fungerar mot dem om uppgifterna är flyktiga.

När det lönar sig att använda tabellvariabler

Vi Börja med ett exempel där en tabellvariabel är perfekt och resulterar i bättre prestanda. Vi kommer att skapa en lista över anställda för Adventureworks, vilken avdelning de arbetar i och de skift de jobbar på. Vi har att göra med en liten datamängd (291 rader).

Vi lägger resultaten i en andra tillfällig tabell, som om vi skickar resultatet vidare till nästa batch. Listning 1 visar koden.

Och här är ett typiskt resultat på min långsamma testmaskin:

Att använda en tillfällig tabell är konsekvent långsammare, även om enskilda körningar kan variera mycket.

Skalproblemen och att glömma att ge en nyckel eller en ledtråd

Hur är prestanda om vi går med i två tabellvariabler? Låt oss prova det. För det här exemplet behöver vi två enkla tabeller, en med alla vanliga ord på engelska (CommonWords), och den andra med en lista över alla orden i Bram Stokers Dracula (WordsInDracula). TestTVsAndTTs-nedladdningen innehåller skriptet för att skapa dessa två tabeller och fylla i var och en från tillhörande textfil. Det finns 60 000 vanliga ord, men Bram Stoker använde bara 10 000 av dem. Den förstnämnda ligger långt utanför break-even-punkten, där man föredrar tillfälliga tabeller.

Vi använder fyra enkla, yttre sammanfogningsfrågor och testar resultatet för NULL värden, för att ta reda på vanliga ord som inte finns i Dracula, vanliga ord som finns i Dracula, ord i Dracula som är ovanliga, och slutligen en annan fråga för att hitta vanliga ord i Dracula, men gå med i motsatt riktning. Du kommer att se frågorna snart när jag visar koden för testriggen.

Nedan följer resultaten av de första testkörningarna. I den första körningen har båda tabellvariablerna primära nycklar, och i den andra är de båda högar, bara för att se om jag överdriver problemen med att inte deklarera ett index i en tabellvariabel. Slutligen kör vi samma frågor med temporära tabeller. Alla tester kördes medvetet på en långsam utvecklingsserver för att illustrera; du får väldigt olika resultat med en produktionsserver.

Resultaten visar att när tabellvariablerna är stora, riskerar du att frågan körs i tio minuter snarare än 100 millisekunder. Dessa ger ett utmärkt exempel på den hemska prestanda du kan uppleva om du inte känner till reglerna. Men även när vi använder primära nycklar betyder antalet rader vi har att göra att det är dubbelt så snabbt att använda tillfälliga tabeller.

Jag kommer inte att gräva i detaljerna i exekveringsplanerna bakom dessa prestandamätvärden, förutom att ge några breda förklaringar till de viktigaste skillnaderna. För temp-tabellfrågorna väljer optimizer, utrustad med full kunskap om kardinalitet och metadata från de primära nyckelbegränsningarna, en effektiv Merge Join-operatör för att utföra kopplingsoperationen.För tabellerna variabla med primära nycklar känner optimeraren till ordningen på raderna i kopplingskolumnen och att de inte innehåller några dubbletter, men antar att den bara handlar om en rad och väljer i stället en kapslad koppling. Här skannar den en tabell och utför sedan för varje rad som returneras individuella sökningar från den andra tabellen. Detta blir mindre effektivt ju större datauppsättningarna är, och är särskilt dåligt i de fall där det skannar CommonWords tabellvariabeln, eftersom det resulterar i över 60 000 sökningar av Dracula tabellvariabel. Nested Loops-anslutningen når toppineffektivitet för två, tio minuters frågor med hjälp av tabellvariabla högar, eftersom det innebär tusentals tabellskanningar av CommonWords. Intressant är att de två ”vanliga orden i Dracula” -frågorna fungerar mycket bättre och det beror på att optimizer istället valde en Hash Match-anslutning för dessa två.

Sammantaget ser temp-tabellerna ut som det bästa valet , men vi är inte färdiga ännu! Låt oss lägga till OPTION (RECOMPILE) -tipsen till frågorna som använder tabellvariablerna med primära nycklar och kör om testerna för dessa frågor och de ursprungliga frågorna med de tillfälliga tabellerna. Vi utelämnar de dåliga högarna för tillfället.

Som du kan se försvinner prestandafördelen med den tillfälliga tabellen. Beväpnad med rätt radantal och ordnade ingångar, optimeraren väljer den mycket effektivare Merge Join.

Vad, undrar du, skulle hända om du gav de dåliga massorna OPTION (RECOMPILE) antydan också? Tja, berättelsen ändras för dem så att alla tre tidpunkterna är mycket närmare.

Intressant är att de två ”vanliga orden i Dracula” frågar som var snabbt även på högar är nu mycket långsammare. Beväpnad med rätt radantal ändrar optimeraren sin strategi, men eftersom den fortfarande inte har någon av de användbara metadata som är tillgängliga för oss när vi definierar begränsningar och nycklar, gör det ett dåligt val. Den skannar CommonWords högen och försöker sedan en ”partiell aggregering” och uppskattar att den kommer att samlas ner från 60 000 rader till några hundra. Den vet inte att det inte finns några dubbletter, så i själva verket aggregerar det inte alls, och aggregeringen och efterföljande går med spill till tempdb.

Testriggen

Observera att detta är testriggen i sin slutliga form visar ungefär lika prestanda för de tre olika typerna av tabeller. Du måste ta bort OPTION (RECOMPILE) för att komma tillbaka till originalet.

Listing 2

Slutsatser

Det finns inget hänsynslöst med att använda tabellvariabler. De ger bättre prestanda när de används för de ändamål som de var avsedda för, och de gör sin egen mopping-up. Vid en viss tidpunkt blir kompromisserna som ger dem bättre prestanda (inte utlöser rekompileringar, tillhandahåller inte statistik, ingen återställning, ingen parallellitet) deras undergång.

Ofta kommer SQL Server-kunden att ge sage råd om storleken på resultatet som kommer att orsaka problem för en tabellvariabel. Resultaten som jag har visat dig i den här artikeln kommer att föreslå för dig att detta förenklar problemen. Det finns två viktiga faktorer: om du har ett resultat av över, låt oss säga, 1000 rader (och den här siffran beror på kontext) måste du ha en PRIMARY KEY eller UNIQUE för alla frågor som går med i en tabellvariabel. Vid en viss tidpunkt måste du också utlösa en omkompilering för att få en anständig exekveringsplan, som har sin egen kostnad.

Även då kan prestanda drabbas dåligt, särskilt om du utför mer komplex bearbetning , eftersom optimeraren fortfarande inte har tillgång till statistik, och så ingen kunskap om selektiviteten hos något frågepredikat. I sådana fall måste du byta till att använda tillfälliga tabeller.

Ytterligare läsning