Kiezen tussen tabelvariabelen en tijdelijke tabellen (ST011, ST012)

Mensen kunnen, en doen, veel discussiëren over de relatieve verdiensten van tabelvariabelen en tijdelijke tabellen. Soms, zoals bij het schrijven van functies, heb je geen keus; maar als je dat doet, zul je merken dat beide hun nut hebben, en het is gemakkelijk om voorbeelden te vinden waarbij beide sneller zijn. In dit artikel zal ik de belangrijkste factoren uitleggen die betrokken zijn bij het kiezen van de een of de ander, en een paar eenvoudige regels demonstreren om de beste prestaties te krijgen.

Ervan uitgaande dat u de basisregels voor betrokkenheid volgt , dan moet u tabelvariabelen als eerste keuze beschouwen wanneer u met relatief kleine gegevenssets werkt. Ze zijn gemakkelijker om mee te werken en ze activeren minder hercompilaties in de routines waarin ze worden gebruikt, vergeleken met het gebruik van tijdelijke tabellen. Tabelvariabelen vereisen ook minder vergrendelingsbronnen omdat ze ‘privé’ zijn voor het proces en de batch waarmee ze zijn gemaakt. SQL Prompt implementeert deze aanbeveling als een codeanalyseregel, ST011 – Overweeg om een tabelvariabele te gebruiken in plaats van een tijdelijke tabel.

Als u meer complexe verwerking van tijdelijke gegevens uitvoert of meer dan redelijk kleine hoeveelheden moet gebruiken gegevens erin, dan zijn lokale tijdelijke tabellen waarschijnlijk een betere keuze. SQL Code Guard bevat een regel voor codeanalyse, gebaseerd op zijn aanbeveling, ST012 – Overweeg om een tijdelijke tabel te gebruiken in plaats van een tabelvariabele, maar deze is momenteel niet geïmplementeerd in SQL Prompt.

Voors en tegens van tabelvariabelen en tijdelijke tabellen

Tabelvariabelen hebben de neiging om slechte druk te krijgen, omdat zoekopdrachten die ze af en toe gebruiken, resulteren in zeer inefficiënte uitvoeringsplannen. Als je echter een paar eenvoudige regels volgt, zijn ze een goede keuze voor tussenliggende werk-tabellen en voor het doorgeven van resultaten tussen routines, waar de datasets klein zijn en de vereiste verwerking relatief eenvoudig is.

Tabelvariabelen zijn heel eenvoudig te gebruiken, vooral omdat ze “onderhoudsvrij” zijn. Ze zijn afgestemd op de batch of routine waarin ze zijn gemaakt, en worden automatisch verwijderd zodra de uitvoering is voltooid, zodat ze kunnen worden gebruikt in een langdurige verbinding riskeert geen resource hogging-problemen in tempdb. Als een tabelvariabele wordt gedeclareerd in een opgeslagen procedure, is deze lokaal voor die opgeslagen procedure en kan er niet naar worden verwezen in een geneste procedure. Er zijn ook geen op statistieken gebaseerde hercompilaties voor tabelvariabelen en je kunt er geen ALTER één, dus routines die ze gebruiken, hebben doorgaans minder hercompilaties dan die welke tijdelijke tabellen gebruiken. Ze zijn ook niet volledig gelogd, dus het maken en vullen ervan gaat sneller en vereist minder ruimte in de t ransaction logboek. Wanneer ze worden gebruikt in opgeslagen procedures, is er minder discussie over systeemtabellen, onder omstandigheden van hoge gelijktijdigheid. Kortom, het is makkelijker om dingen netjes en opgeruimd te houden.

Als je met relatief kleine datasets werkt, zijn ze sneller dan de vergelijkbare tijdelijke tabel. Naarmate het aantal rijen echter toeneemt, meer dan ongeveer 15.000 rijen, maar afhankelijk van de context, kunt u problemen tegenkomen, voornamelijk vanwege hun gebrek aan ondersteuning voor statistieken. Zelfs de indexen die PRIMARY KEY en UNIQUE beperkingen op tafelvariabelen afdwingen, hebben geen statistieken . Daarom zal de optimizer een hardgecodeerde schatting gebruiken van 1 rij die wordt geretourneerd door een tabelvariabele, en zal dus de neiging hebben om operators te kiezen die optimaal zijn voor het werken met kleine gegevenssets (zoals de operator Nested Loops voor joins). Hoe meer rijen in de tabelvariabele, hoe groter de discrepanties tussen schatting en werkelijkheid, en des te inefficiënter worden de plankeuzes van de optimizer. Het resulterende plan is soms beangstigend.

De ervaren ontwikkelaar of DBA zal uitkijken naar dit soort problemen en klaar zijn om het OPTION (RECOMPILE) vraaghint naar de instructie die de tabelvariabele gebruikt. Als we een batch indienen die een tabelvariabele bevat, compileert de optimizer eerst de batch, waarna de tabelvariabele leeg is. Wanneer de batch begint met uitvoeren, zorgt de hint ervoor dat alleen die enkele instructie opnieuw wordt gecompileerd, waarna de tabelvariabele wordt gevuld en de optimizer het werkelijke aantal rijen kan gebruiken om een nieuw plan voor die instructie te compileren. Soms, maar zelden, helpt zelfs dit niet. Overmatig vertrouwen op deze hint zal ook tot op zekere hoogte het voordeel teniet doen dat tabelvariabelen hebben dat ze minder hercompilaties veroorzaken dan tijdelijke tabellen.

Ten tweede worden bepaalde indexbeperkingen met tabelvariabelen meer een factor bij het omgaan met grote datasets. Hoewel u nu de inline syntaxis voor het maken van indexen kunt gebruiken om niet-geclusterde indexen voor een tabelvariabele te maken, zijn er enkele beperkingen en zijn er nog steeds geen bijbehorende statistieken.

Zelfs bij relatief bescheiden rijenaantallen kunt u prestatieproblemen met querys tegenkomen als u een query probeert uit te voeren die een join is, en u vergeet een PRIMARY te definiëren KEY of UNIQUE beperking voor de kolom die u gebruikt voor de join. Zonder de metagegevens die ze leveren, heeft de optimizer geen kennis van de logische volgorde van de gegevens, of dat de gegevens in de join-kolom dubbele waarden bevatten, en zal hij waarschijnlijk inefficiënte join-bewerkingen kiezen, wat resulteert in langzame zoekopdrachten. Als u met een heap van tabelvariabelen werkt, kunt u deze alleen gebruiken als een eenvoudige lijst die waarschijnlijk in één keer wordt verwerkt (tabelscan). Als u beide gebruik van de OPTION (RECOMPILE) hint combineert voor nauwkeurige schattingen van de kardinaliteit, en een sleutel in de kolom join om de optimizer nuttig te maken metadata, dan kunt u voor kleinere datasets vaak query-snelheden behalen die vergelijkbaar zijn met of beter zijn dan het gebruik van een lokale tijdelijke tabel.

Zodra het aantal rijen toeneemt buiten de comfortzone van een tabelvariabele, of u moet complexere gegevens uitvoeren verwerking, dan kun je het beste overschakelen om tijdelijke tabellen te gebruiken. Hier heb je alle opties voor indexering tot je beschikking, en de optimizer zal de luxe hebben om statistieken voor elk van deze indexen te gebruiken. Het nadeel is natuurlijk dat tijdelijke tafels hogere onderhoudskosten met zich meebrengen. U moet ervoor zorgen dat u na uzelf opruimt om tempdb-congestie te voorkomen. Als u een tijdelijke tabel wijzigt, of de gegevens erin wijzigt, kan het zijn dat u de bovenliggende routine opnieuw moet compileren.

Tijdelijke tabellen zijn beter wanneer er een groot aantal verwijderingen en invoegingen nodig is ( ). Dit geldt vooral als de gegevens volledig uit de tabel moeten worden verwijderd, aangezien alleen tijdelijke tabellen afkappen ondersteunen. De compromissen bij het ontwerp van tabelvariabelen, zoals het ontbreken van statistieken en hercompilaties, werken tegen hen als de gegevens vluchtig zijn.

Wanneer het loont om tabelvariabelen te gebruiken

Wij Ik begin met een voorbeeld waarin een tabelvariabele ideaal is en resulteert in betere prestaties. Voor Adventureworks maken we een lijst met medewerkers, op welke afdeling ze werken en in welke ploegen ze werken. We hebben te maken met een kleine gegevensset (291 rijen).

We plaatsen de resultaten in een tweede tijdelijke tabel, alsof we het resultaat doorgeven aan de volgende batch. Listing 1 toont de code.

En hier is een typisch resultaat op mijn langzame testmachine:

Het gebruik van een tijdelijke tabel is altijd langzamer, hoewel individuele runs behoorlijk kunnen variëren.

Schaalproblemen en het vergeten een sleutel of een hint te geven

Hoe is de prestatie als we twee tabelvariabelen samenvoegen? Laten we het eens proberen. Voor dit voorbeeld hebben we twee eenvoudige tabellen nodig, een met alle gebruikelijke woorden in de Engelse taal (CommonWords), en de andere met een lijst van alle woorden in Bram Stokers Dracula (WordsInDracula). De TestTVsAndTTs-download bevat het script om deze twee tabellen te maken en ze allemaal te vullen vanuit het bijbehorende tekstbestand. Er zijn 60.000 veel voorkomende woorden, maar Bram Stoker gebruikte er maar 10.000. De eerste bevindt zich ruim buiten het break-evenpunt, waar men de voorkeur begint te geven aan tijdelijke tabellen.

We gebruiken vier eenvoudige buitenste join-queries, waarbij we het resultaat testen voor NULL waarden, om de gewone woorden te vinden die niet in Dracula staan, gewone woorden die in Dracula voorkomen, woorden in Dracula die ongebruikelijk zijn, en tot slot nog een vraag om gewone woorden in Dracula te vinden, maar in de tegenovergestelde richting meedoen. Je zult de vragen binnenkort zien, wanneer ik de code voor de testopstelling laat zien.

Hieronder volgen de resultaten van de eerste testruns. In de eerste run hebben beide tabelvariabelen primaire sleutels, en in de tweede zijn ze allebei hopen, gewoon om te zien of ik de problemen van het niet declareren van een index in een tabelvariabele overdrijf. Ten slotte voeren we dezelfde querys uit met tijdelijke tabellen. Ter illustratie werden alle tests met opzet op een langzame ontwikkelingsserver uitgevoerd; je krijgt heel verschillende resultaten met een productieserver.

De resultaten laten zien dat wanneer de tabelvariabelen hopen zijn, je het risico loopt dat de query tien minuten loopt in plaats van 100 milliseconden. Deze geven een goed voorbeeld van de gruwelijke prestatie die je kunt ervaren als je de regels niet kent. Zelfs als we primaire sleutels gebruiken, betekent het aantal rijen waarmee we te maken hebben dat het gebruik van tijdelijke tabellen nu twee keer zo snel is.

Ik zal niet ingaan op de details van de uitvoeringsplannen erachter prestatiestatistieken, behalve om een paar brede uitleg te geven van de belangrijkste verschillen. Voor de tijdelijke tabelquerys kiest de optimizer, gewapend met een volledige kennis van kardinaliteit en de metagegevens van de primaire sleutelbeperkingen, een efficiënte samenvoegingsoperator om de samenvoegbewerking uit te voeren.Voor de tabellenvariabele met primaire sleutels kent het optimalisatieprogramma de volgorde van de rijen in de samenvoegkolom en dat ze geen duplicaten bevatten, maar gaat ervan uit dat het slechts om één rij gaat en kiest daarom in plaats daarvan een samenvoeging met geneste lussen. Hier scant het een tabel en voert vervolgens voor elke geretourneerde rij individuele zoekacties uit naar de andere tabel. Dit wordt minder efficiënt naarmate de gegevenssets groter zijn, en is vooral slecht in de gevallen waarin de tabelvariabele CommonWords wordt gescand, omdat het resulteert in meer dan 60.000 zoekopdrachten van de Dracula tabelvariabele. De samenvoeging van geneste lussen bereikt een ‘piekinefficiëntie’ voor twee zoekopdrachten van tien minuten met behulp van tabelvariabelenhopen, omdat het duizenden tabelscans van CommonWords met zich meebrengt. Interessant is dat de twee “gewone woorden in Dracula” -querys veel beter presteren en dit komt doordat de optimizer voor die twee in plaats daarvan een Hash Match-join heeft gekozen.

Over het algemeen lijken de tijdelijke tabellen de beste keuze te zijn , maar we zijn nog niet klaar! Laten we de hint OPTION (RECOMPILE) toevoegen aan de zoekopdrachten die de tabelvariabelen met primaire sleutels gebruiken, en Voer de tests voor deze zoekopdrachten en de oorspronkelijke zoekopdrachten opnieuw uit met behulp van de tijdelijke tabellen. We laten voorlopig de arme hopen weg.

Zoals u kunt zien, verdwijnt het prestatievoordeel van de tijdelijke tabel. Gewapend met juiste rijen tellen en geordende invoer, kiest de optimizer de veel efficiëntere Merge Join.

Wat zou er gebeuren als je die arme hopen de OPTION (RECOMPILE) hint ook? Kijk, het verhaal verandert voor hen zodat alle drie de timing veel dichterbij komen.

Interessant is dat de twee gewone woorden in Dracula vragen waren snel, zelfs op hopen, zijn nu veel langzamer. Gewapend met het juiste aantal rijen, verandert de optimizer zijn strategie, maar omdat hij nog steeds geen van de bruikbare metadata tot zijn beschikking heeft wanneer we beperkingen en sleutels definiëren, is hij een slechte keuze. Het scant de CommonWords heap en probeert vervolgens een “gedeeltelijke aggregatie”, waarbij wordt geschat dat het zal worden geaggregeerd van 60.000 rijen naar een paar honderd. Het weet niet dat er geen duplicaten zijn, dus in feite aggregeert het helemaal niet, en de aggregatie en daaropvolgende samenvoeging spill naar tempdb.

De testopstelling

Let op: dit is de testopstelling in zijn uiteindelijke vorm toont ongeveer gelijke prestaties voor de drie verschillende soorten tabellen. U moet de OPTION (RECOMPILE) hints verwijderen om terug te gaan naar het origineel.

Listing 2

Conclusies

Er is niets roekeloos aan het gebruik van tabelvariabelen. Ze presteren beter wanneer ze worden gebruikt voor de doeleinden waarvoor ze bedoeld zijn, en ze doen hun eigen opruimen. Op een bepaald punt worden de compromissen die hen betere prestaties opleveren (geen hercompilaties activeren, geen statistieken verstrekken, geen rollback, geen parallellisme) hun ondergang.

Vaak geeft de SQL Server-expert wijs advies over de grootte van het resultaat dat problemen zal veroorzaken voor een tabelvariabele. De resultaten die ik u in dit artikel heb laten zien, suggereren dat dit de problemen te simpel maakt. Er zijn twee belangrijke factoren: als je een resultaat hebt van meer dan, laten we zeggen, 1000 rijen (en dit cijfer is afhankelijk van de context), dan heb je een PRIMARY KEY of UNIQUE -sleutel voor alle querys die worden samengevoegd met een tabelvariabele. Op een gegeven moment zal je ook een hercompilatie moeten starten om een fatsoenlijk uitvoeringsplan te krijgen, dat zijn eigen overhead heeft.

Zelfs dan kunnen de prestaties eronder lijden, vooral als je complexere verwerking uitvoert , omdat het optimalisatieprogramma nog steeds geen toegang heeft tot statistieken en dus geen kennis heeft van de selectiviteit van een querypredikaat. In dergelijke gevallen moet u overschakelen naar het gebruik van tijdelijke tabellen.

Verder lezen