SQLShack (Dansk)

Indledning

Har du nogensinde skrevet et kompleks forespørgsel ved hjælp af det Fælles Bord Udtryk (CTEs) kun at være skuffet over resultaterne? Har du beskyldt CTE? Denne artikel ser på dette problem for at vise, at det er lidt dybere end et bestemt syntaksvalg og giver nogle tip til, hvordan man forbedrer ydeevnen.

CTEs Revisited

almindelige Tabeludtryk blev først vist i s .l Server 2005., Kombineret med med-erklæringen giver de en måde at omorganisere et kompliceret hierarki af underforespørgsler til en letlæselig lineær form. CTE ‘ er giver også mulighed for rekursiv adfærd, selvom det er uden for rammerne af denne artikel. Citat fra bøger Online angiver en CTE:

et midlertidigt navngivet resultatsæt, kendt som et fælles tabeludtryk (CTE). Dette er afledt af en simpel forespørgsel og defineret i udførelsen omfanget af en enkelt SELECT, INSERT, UPDATE eller DELETE sætning. Denne klausul kan også bruges i en CREATE vie. – erklæring som en del af dens definerende SELECT-erklæring., Et fælles tabeludtryk kan indeholde henvisninger til sig selv. Dette kaldes en rekursiv fælles tabel udtryk.

generelt opretter du en forespørgsel ved hjælp af CTEs som denne:

Du kan have et hvilket som helst antal CTEs, og den endelige erklæring (som kan være enhver gyldig DML) kan bruge alle, nogle eller ingen af dem., Hvis der er blot to, som ovenfor, dette kan skrives ved hjælp af underforespørgsler:

1
2
3
4
5
6
7
8
9
10

VÆLG …
fra (
vælg …
) cte1
Deltag (
vælg …
) cte2
PÅ cte1.,<Deltag kolonne> = cte2.<deltag i kolonne>

Der er ingen forskel mellem den første forespørgsel og den anden så langt som, hvordan det er parset, der er udarbejdet og gennemført. Udførelsesplanerne vil være identiske. Forskellen er i læsbarhed. Når underforespørgsler er komplekse, kan det være lettere at trække dem ud og sætte dem i CTEs og derefter kombinere dem i en med erklæring.

ydeevne?,

måske har du skrevet eller fejlrettet en CTE, der ser ud til at køre langsomt. Eller måske har du set indlæg i nogle s .l Server forum klager over, at nogle CTE kører langsomt. Hvad klageren virkelig antyder, er, at når S .l Server på en eller anden måde udarbejder en forespørgsel Bygget fra CTEs, gør den det anderledes end den samme forespørgsel ved hjælp af underforespørgsler og på en eller anden måde gør et værre stykke arbejde med det. Det er simpelthen ikke tilfældet., Faktisk, hvis forumplakaten havde omformateret forespørgslen til (muligvis indlejrede) underforespørgsler og ikke bruge strukturen med…CTE, ville de samme ydeevneegenskaber uden tvivl være blevet observeret.

så hvorfor kører det så langsomt! Godt spørgsmål. For at forsøge at forstå hvorfor, lad os se på et simpelt eksempel:

Hvad gør dette? Efter tabellen variabel erklæring, det udfylder tabellen med de første en million rækker fra det kartesiske produkt af sys.all_columns udsigt., Afhængigt af hvor stor databasen er, at du kører dette mod, kan der være Tusinder, Titusinder og måske mange flere rækker i den tabel. En million rækker i resultatet er nok til vores formål. Efter at have udfyldt tabelvariablen fortsætter scriptet i en med erklæring ved hjælp af en CTE, der bare trækker rækker fra tabelvariablen. Det ser ikke så slemt ud, vel? Før du kører det på nogle handy produktionssystem, selvom, læs videre.

lidt matematik hjælper dig med at se, hvad der står på spil., Hvis jeg bare køre:

1
2
3

SELECT COUNT(*)FROM sys.all_columns;

jeg kan få et resultat af 7364 på mit system – og det er bare LocalDB! Da det er forbundet med det kartesiske produkt af samme opfattelse, kan det resultere i 7.364 milliarder rækker. Jeg lader det køre på egen hånd (ikke-produktion!) system., Jeg var nødt til at stoppe det, før det kunne fuldføre. Jeg indrømmer at være lidt utålmodig!

nysgerrig efter at se, hvad der foregår bag gardinet, viste jeg udførelsesplanen. Det ser sådan ud:

Bemærk, at en nestet Sløjfeoperatør blev valgt, og at det estimerede antal rækker, der kommer fra min tabelvariabel, er 1! Selvom jeg lige har indlæst det med 1 million rækker! Hvordan kan det være? Den triste sandhed er, at s .l Server ikke opretholder statistikker for tabelvariabler, så på det tidspunkt, hvor forespørgslen er udarbejdet, ved den ikke, hvordan rækker i virkeligheden er der., Hvis jeg kun havde en række, ville en indlejret løkke være fint! Sådan er det defineret i BOL:

de indlejrede sløjfer deltager, også kaldet indlejret iteration, bruger en sammenføjningsindgang som den ydre indtastningstabel (vist som den øverste indgang i den grafiske udførelsesplan) og en som den indre (nederste) indtastningstabel. Den ydre sløjfe forbruger den ydre indgangstabel række for række. Den indre sløjfe, udført for hver ydre række, søger efter matchende rækker i den indre indtastningstabel.

så for hver eneste af de millioner rækker i min tabelvariabel foretager s .l en scanning af objektkatalogvisningen. Nej! Godt!,

mit andet forsøg erstatter tabelvariablen med en temp-tabel. Scriptet er identiske bortset fra, at det starter med:

1
2
3

CREATE TABLE #t (id INT, navn SYSNAME);

Og erstatter @t #t i resten af scriptet., Denne gang ser udførelsesplanen sådan ud:

dette ser bedre ud! For det første er det anslåede antal rækker, der kommer fra den midlertidige tabel, korrekt. For en anden valgte complier en hash – kamp-et meget bedre valg til denne deltagelse end de bordscanninger, vi havde før.

Jeg lader denne forespørgsel køre til færdiggørelse. Det løb for lidt over 3 minutter på min (ganske vist gammel og langsom!) laptop og produceret 16,7 milliarder rækker før rammer en ud af hukommelsesfejl (det er en masse rækker!,) At reducere antallet af rækker i den midlertidige tabel til 100.000 gjorde det muligt at afslutte på komfortable ni sekunder.

alt dette betyder, at der ikke er noget galt med CTE-konstruktionen; noget andet skal foregå. At noget andet er statistik. S .l Server compiler bruger statistik (f.eks række tæller) for at informere optimi .er. Statistikker oprettes eller opdateres dog ikke for tabelvariabler. Dette er “Aha!”øjeblik! Ændring af scriptet til at bruge en midlertidig tabel i stedet for en tabelvariabel betød, at statistikker var tilgængelige for at vælge en bedre eksekveringsplan.,

grave dybere

at noget andet er i den lille sætning nær i begyndelsen af citatet fra BOL:

specificerer et midlertidigt navngivet resultatsæt

hvorfor er det signifikant? Kan du tænke på en anden type midlertidig navngivet resultat sæt? Hvad med en tabel variabel! Vi fandt allerede ud af, at tabelvariabler, som ikke har nogen statistik, kan ødelægge udførelsesplaner på grund af forkerte rækketællinger. En CTE har dog noget lignende i et midlertidigt resultatsæt. Når man udarbejder en kompleks CTE, har den muligvis ikke tilstrækkelig information til rådighed til at udlede en optimal plan., Når jeg tror, det sker, er her hvad jeg gør:

  1. Test hver CTE på egen hånd fra top til bund for at se, om / hvor eksekveringstider eller rækketællinger eksploderer., Det er nemt at gøre i SSMS ved at tilføje en
    1
    2
    3

    SELECT * FROM &lt;CTE navn&gt;

    Lige før de vigtigste query, der trækker CTEs sammen og fremhæve og kører scriptet ned til dette punkt.,

  2. Kontroller, at synderen er korrekt skrevet med korrekte prædikater.
  3. sørg for, at prædikaterne indekseres, hvis det er muligt (mere om det senere).
  4. Genoptag test fra den CTE frem til slutningen.

Der er mindst et problem, der ikke kan løses med denne proces. Det er ikke altid muligt at sikre, at alle prædikater indekseres, især for afledte resultater., I dette tilfælde er der en anden mulighed for at bryde flaskehalsen:

Opdel CTE ‘ en i en midlertidig tabel

Dette vil betyde omstrukturering af din forespørgsel. Grundlæggende vil du tage den øverste halvdel af forespørgslen og skrive resultaterne til en midlertidig tabel, derefter indeksere tabellen og derefter genoptage forespørgslen, startende med den midlertidige tabel. Antag for eksempel, at jeg har:

Antag yderligere, at problemet er ved CTE10.,

Nu, at vi har de hidtil opnåede resultater i en midlertidig tabel, indeks det:

1
2
3

OPRET INDEKS IX_#CTE9 PÅ #CTE9(col1, col2, …)

Nu, lad os afslutte forespørgslen:

Bemærk, at du stadig kan have til at fortsætte processen, der er beskrevet ovenfor, indtil du har nået dit mål.,

og en anden ting

uanset hvad du gør, vænne dig til at læse Eksekveringsplaner. Der er et væld af oplysninger der og hundreder, hvis ikke tusinder af store ressourcer til at forstå dem. Som en endelig sundhedskontrol skal du dog højreklikke på den venstre post i den grafiske udførelsesplan. Det vil sandsynligvis være en SELECT, UPDATE, INSERT eller DELETE. Vælg NU Egenskaber i kontekstmenuen. Sørg for, at du ser dette omtrent halvvejs ned:

Hvis du ikke ser Optimeringsniveauet fuldt ud, er det en indikator for, at din forespørgsel er for kompleks., Overvej at bryde det op nogle flere, ved hjælp af den midlertidige tabel teknik skitseret ovenfor.

konklusion

almindelige Tabeludtryk er bare en anden måde at skrive underforespørgsler på. De er ikke magiske, og S .l Server behandler dem heller ikke anderledes end normale, indlejrede underforespørgsler, bortset fra deres rekursive fætre. CTEs kan gøre kode lettere at læse og vedligeholde, da du, når den bruges korrekt, kan adskille bekymringer fra CTE. (Bemærk, hvordan jeg sneg I et objektorienteret programmeringsprincip?)

når de udfører dårligt, skal du dog ikke bebrejde CTE., Grav dybere for at forstå, hvad der foregår under dækkene. Udførelsesplanen er den perfekte måde at gøre det på.

  • Forfatter
  • Seneste Indlæg
Gerald Britton er en Senior SQL Server Solution Designer, Forfatter, Software Udvikler, Underviser og en Microsoft Data Platform MVP. Han har mange års erfaring i IT-branchen i forskellige roller.,
Gerald har specialiseret sig i at løse s .l Server queryuery performance problemer, især da de vedrører Business Intelligence-løsninger. Han er også en medforfatter af e-bog” Kom godt i gang med Python ” og en ivrig Python udvikler, lærer, og Pluralsight forfatter.
Du kan finde ham på LinkedIn, på Twitter på twitter.,com/GeraldBritton eller @GeraldBritton, og på Pluralsight
Se alle indlæg skrevet af Gerald Britton

Seneste indlæg af Gerald Britton (se alle)
  • Snapshot Isolation i SQL Server – August 5, 2019
  • Faldende i din database ved hjælp DBCC SHRINKFILE – August 16, 2018
  • Delvis stored procedures på SQL Server – juni 8, 2018

Skriv et svar

Din e-mailadresse vil ikke blive publiceret. Krævede felter er markeret med *