SQLShack (Svenska)

introduktion

har du någonsin skrivit upp en komplex fråga med hjälp av vanliga Tabelluttryck (CTEs) bara för att bli besviken över resultatet? Har du klandrat CTE? Den här artikeln tittar på det problemet för att visa att det är lite djupare än ett visst syntaxval och erbjuder några tips om hur man förbättrar prestanda.

CTES Revisited

vanliga Tabelluttryck uppträdde först i SQL Server 2005., I kombination med with-uttalandet ger de ett sätt att omorganisera en komplicerad hierarki av underfrågor till en lättläst linjär form. CTEs tillåter också rekursivt beteende, även om det ligger utanför denna artikels räckvidd. Citera från böcker Online, en CTE:

anger en tillfällig namngiven resultatuppsättning, känd som ett vanligt tabelluttryck (CTE). Detta härleds från en enkel fråga och definieras inom exekveringsområdet för en enskild SELECT, INSERT, UPDATE eller DELETE-sats. Denna klausul kan också användas i en CREATE VIEW-sats som en del av dess definierande SELECT-sats., Ett vanligt tabelluttryck kan innehålla referenser till sig själv. Detta kallas ett rekursivt gemensamt tabelluttryck.

generellt ställer du in en fråga med hjälp av CTEs så här:

Du kan ha valfritt antal CTEs och det slutliga uttalandet (vilket kan vara vilket som helst giltigt DML) kan använda alla, vissa eller ingen av dem., Förutsatt att det bara finns två, som ovan, kan detta skrivas om med hjälp av underqueries:

1
2
3
4
5
6
7
8
9
10

välj …
från (
välj …
) CTE1
gå med (
välj …
) CTE2
på CTE1.,<gå med i kolumnen> = cte2.<gå med i kolumnen>

det finns ingen skillnad mellan den första frågan och den andra så långt som hur den tolkas, sammanställs och exekveras. Genomförandeplanerna kommer att vara identiska. Skillnaden i läsbarhet. När subqueries är komplexa, kan det vara lättare att dra ut dem och sätta dem i CTEs sedan kombinera dem i ett med uttalande.

prestanda?,

kanske du har skrivit eller felsöka en CTE som verkar köra långsamt. Eller kanske du har sett inlägg i vissa SQL Server forum klagar på att vissa CTE körs långsamt. Vad klaganden verkligen föreslår är att på något sätt när SQL Server sammanställer en fråga byggd från CTEs gör det annorlunda än samma fråga med hjälp av underfrågor och på något sätt gör ett sämre jobb med det. Så är det helt enkelt inte., I själva verket, om forumet affischen hade omformaterat frågan i (eventuellt kapslade) underkvällar och inte använda med…CTE struktur, samma prestandaegenskaper skulle utan tvekan ha observerats.

så, varför går det så långsamt! Bra fråga. För att försöka förstå varför, låt oss titta på ett enkelt exempel:

vad gör det här? Efter tabellen variabel Deklaration, fyller den tabellen med de första en miljon rader från den kartesiska produkten av sys.all_columns visa., Beroende på hur stor databasen är att du kör detta mot det kan finnas tusentals, tiotusentals och kanske många fler rader i tabellen. En miljon rader i resultatet är tillräckligt för våra ändamål. Efter att fylla tabellen variabeln, skriptet fortsätter i en med uttalande med hjälp av en CTE som bara drar rader från tabellen variabeln. Det ser inte så illa ut, eller hur? Innan du kör det på något praktiskt produktionssystem, läs vidare.

lite matte hjälper dig att se vad som står på spel., Om jag bara kör:

1
2
3

välj count(*)från sys.all_columns;

jag får ett resultat av 7364 på mitt system – och det är bara LocalDB! Eftersom det förenas med den kartesiska produkten av samma uppfattning, kan det leda till 7.364 miljarder rader. Jag lät det köras på egen hand (icke-produktion!) systemet., Jag var tvungen att stoppa det innan det kunde slutföras. Jag erkänner att jag är lite otålig!

nyfiken på att se vad som händer bakom gardinen, visade jag exekveringsplanen. Det ser ut så här:

Observera att en nästlad Loopsoperatör valdes och att det uppskattade antalet rader som kommer från min tabellvariabel är 1! Även om jag bara laddade den med 1 miljon rader! Hur kan det vara så? Den sorgliga sanningen är att SQL Server inte upprätthåller statistik för tabellvariabler, så när frågan sammanställs vet den inte hur kan rader verkligen finns där., Om jag bara hade en rad, Skulle en kapslad slinga vara bra! Så här definieras det i BOL:

de kapslade looparna, även kallade kapslade iteration, använder en koppling som den yttre inmatningstabellen (visas som den övre ingången i den grafiska exekveringsplanen) och en som den inre (nedre) inmatningstabellen. Den yttre slingan förbrukar den yttre inmatningstabellen rad för rad. Den inre slingan, utförd för varje yttre rad, söker efter matchande rader i den inre inmatningstabellen.

så, för var och en av de miljoner raderna i min tabellvariabel, kommer SQL att göra en genomsökning av objektkatalog vyn. Nej! Bra!,

mitt andra försök ersätter tabellvariabeln med en temp-tabell. Skriptet är identiskt förutom att det börjar med:

1
2
3

Skapa tabell #t (id int, namn sysname);

och ersätter @t med #T i resten av skriptet., Den här gången ser exekveringsplanen ut så här:

det här ser bättre ut! För en sak är det uppskattade antalet rader som kommer från det tillfälliga bordet korrekt. För en annan valde complier en hashmatch – ett mycket bättre val för denna koppling än tabellskanningarna vi hade tidigare.

Jag lät den här frågan gå till färdigställande. Det sprang i drygt 3 minuter på min (visserligen Gammal och långsam!) bärbar dator och producerade 16.7 miljarder rader innan du slår ut ett minnesfel (det är många rader!,) Att minska antalet rader i det tillfälliga bordet till 100 000 gjorde det möjligt att avsluta på en bekväm nio sekunder.

allt detta innebär att det inte är något fel med CTE-konstruktionen; något annat måste vara på gång. Att något annat är statistik. SQL Server använder kompilatorn statistik (exempelvis rad räknas) att informera optimizer. Statistik skapas eller uppdateras dock inte för tabellvariabler. Det här är ”Aha!”ögonblick! Att ändra skriptet för att använda en tillfällig tabell i stället för en tabellvariabel innebar att statistik var tillgänglig för att välja en bättre exekveringsplan.,

gräva djupare

att något annat är i den lilla frasen nära i början av citatet från BOL:

anger en tillfällig namngiven resultatuppsättning

Varför är det signifikant? Kan du tänka dig en annan typ av tillfällig namngiven resultatuppsättning? Vad sägs om en tabell variabel! Vi har redan upptäckt att tabellvariabler, som inte har någon statistik, kan orsaka förödelse med exekveringsplaner på grund av felaktiga radtal. En CTE har dock något liknande i en tillfällig resultatuppsättning. Vid sammanställningen av en komplex CTE, det kanske inte har tillräckligt med information tillgänglig för att härleda en optimal plan., När jag tror att det händer, här är vad jag gör:

  1. testa varje CTE på egen hand från topp till botten för att se om / var exekveringstider eller rad räknas explodera., Detta är lätt att göra i SSMS genom att lägga till en
    1
    2
    3

    välj * från &lt;CTE name&gt;

    bara innan huvudfrågan som drar CTES tillsammans och markerar och kör skriptet ner till den punkten.,

  2. kontrollera att den skyldige är korrekt skriven med korrekta predikat.
  3. se till att predikaten indexeras, om möjligt (mer om det senare).
  4. fortsätt testa från den CTE framåt till slutet.

det finns minst ett problem som inte kan lösas med den här processen. Det är inte alltid möjligt att se till att alla predikat indexeras, särskilt för härledda resultat., I så fall finns det ett annat alternativ för att bryta flaskhalsen:

dela upp CTE i en tillfällig tabell

detta kommer att innebära en omstrukturering av din fråga. I grund och botten tar du den övre halvan av frågan och skriver resultaten till en tillfällig tabell, indexerar sedan tabellen och fortsätter sedan frågan, börjar med den tillfälliga tabellen. Anta till exempel att jag har:

Antag vidare att problemet är vid CTE10.,

nu när vi har resultaten hittills i en tillfällig tabell, indexera det:

1
2
3

skapa index ix_#cte9 på #cte9(col1, col2,…)

låt oss nu avsluta frågan:

Observera att du kanske fortfarande måste fortsätta processen som beskrivs ovan tills du når dina prestationsmål.,

och en annan sak

vad du än gör, vänja dig vid att läsa Exekveringsplaner. Det finns en mängd information där och hundratals om inte tusentals stora resurser för att förstå dem. Som en slutlig sanity-kontroll högerklickar du på den vänstra posten i den grafiska exekveringsplanen. Det kommer sannolikt att vara en välj, Uppdatera, infoga eller ta bort. Nu i snabbmenyn väljer du Egenskaper. Se till att du ser detta, ungefär halvvägs ner:

Om du inte ser Optimeringsnivån FULL är det en indikator på att din fråga är för komplex., Överväg att bryta upp det lite mer, med hjälp av den tillfälliga bordstekniken som beskrivs ovan.

slutsats

vanliga Tabelluttryck är bara ett annat sätt att skriva underqueries. De är inte magiska, inte heller SQL Server behandla dem något annorlunda från normala, kapslade underqueries, med undantag för deras rekursiva kusiner. CTEs kan göra koden lättare att läsa och underhålla, eftersom, när den används på rätt sätt, du kan skilja oro av CTE. (Lägg märke till hur jag smög in en objektorienterad programmeringsprincip?)

när de utför dåligt, skyll inte på CTE., Gräva djupare för att förstå vad som händer under omslaget. Avrättningsplanen är det perfekta sättet att göra det.

  • författare
  • Senaste inlägg
Gerald Britton är en Senior SQL Server-Lösningsdesigner, författare, mjukvaruutvecklare, lärare och en avdelningschef.Microsoft data platform MVP. Han har många års erfarenhet inom IT-branschen i olika roller.,
Gerald specialiserar sig på att lösa SQL Server query prestandaproblem, särskilt eftersom de relaterar till Business Intelligence-lösningar. Han är också en medförfattare till eBook ”komma igång med Python” och en ivrig Python utvecklare, lärare och Pluralsight författare.
Du kan hitta honom på LinkedIn, på Twitter på twitter.,Gerald Britton

Senaste inlägg av Gerald Britton (se alla)
  • Snapshot isolering i SQL Server – Augusti 5, 2019
  • krymper din databas med DBCC shrinkfile – Augusti 16, 2018
  • partiella lagrade procedurer i SQL Server – Juni 8, 2018

Lämna ett svar

Din e-postadress kommer inte publiceras. Obligatoriska fält är märkta *