SQLShack (Norsk)

Innledning

Har du noen gang skrevet opp en sammensatt spørring ved hjelp av Felles Bord Uttrykk (CTEs) bare for å bli skuffet av ytelsen? Har du vært å skylde på CTE? Denne artikkelen ser på det problemet, for å vise at det er litt dypere enn en bestemt syntaks valg og tilbyr noen tips om hvordan å forbedre ytelsen.

CTEs Revisited

Felles Bord Uttrykk dukket først opp i SQL Server 2005., Kombinert med MED uttalelse, de gir en måte å organisere en komplisert hierarki av sub-spørringer til en lett-å-lese-lineær form. CTEs også tillate rekursiv atferd, selv om det er utenfor omfanget av denne artikkelen. Siterer fra Bøker Online, et CTE:

Angir en midlertidig heter resultat, kjent som et felles bord uttrykk (CTE). Dette er hentet fra en enkel spørring og definert innen gjennomføring omfanget av et enkelt SELECT, INSERT, UPDATE eller DELETE uttalelse. Denne bestemmelsen kan også brukes i en CREATE VIEW uttalelse som en del av sin definere SELECT-setning., Et felles bord uttrykk kan inneholde referanser til seg selv. Dette er referert til som en rekursiv felles bord uttrykk.

Vanligvis, du kan sette opp en spørring ved hjelp av CTEs som dette:

Du kan ha en rekke CTEs og den endelige uttalelse (som kan være et hvilket som helst gyldig DML) kan bruke alle, noen eller ingen av dem., Forutsatt at det ikke er bare to, som ovenfor, denne kan omskrives ved hjelp av subqueries:

1
2
3
4
5
6
7
8
9
10

VELG …
FRA
VELG …
) cte1
DELTA (
VELG …
) cte2
PÅ cte1.,<delta i kolonne> = cte2.<delta i kolonne>

Det er ingen forskjell mellom den første spørringen, og den andre så langt det er hvordan den er lest, samlet og henrettet. Gjennomføring planer vil være identiske. Forskjellen er i lesbarhet. Når subqueries er komplekse, kan det være lettere å trekke dem ut og sette dem inn i CTEs deretter kombinere dem i en uttalelse.

Ytelse?,

Kanskje du har skrevet eller feilsøkt en CTE som ser ut til å kjøre langsomt. Eller, kanskje du har sett oppslagene i noen SQL Server forum klager på at noen CTE går sakte. Hva klager er virkelig noe som tyder på at det er noe når SQL Server kompilerer en spørring bygget fra CTEs den gjør det på en annen måte enn i samme spørring ved hjelp av sub-spørringer og på en måte gjør en dårligere jobb med det. Det er rett og slett ikke tilfelle., Faktisk, hvis forum plakat hadde formatert søk i (muligens nestede) subqueries og ikke bruke MED…ETC struktur, samme ytelse egenskaper ville uten tvil ha blitt observert.

Så, hvorfor det går så sakte! Godt spørsmål. For å prøve å forstå hvorfor, la oss se på et enkelt eksempel:

Hva gjør dette? Etter tabellen variabel erklæring, fyller ut tabellen med den første million rader fra den Kartesiske produktet av sys.all_columns vise., Avhengig av hvor stor database er at du kjører dette mot at det kan være tusener, titusener, og kanskje mange flere rader i tabellen. En million rader i resultatet er nok for vårt formål. Etter at du fyller tabell variabel, skriptet fortsetter MED uttalelse ved hjelp av en CTE som trekker bare rader fra tabell variabel. Ser ikke så ille ut, ikke sant? Før du kjører den på noen praktiske production system, skjønt, bør du lese videre.

litt matte vil hjelpe deg å se hva som står på spill., Hvis jeg bare kjøre:

1
2
3

SELECT COUNT(*)FRA sys.all_columns;

jeg får et resultat av 7364 på mitt system – og det er bare LocalDB! Siden det er å være sammen med Kartesiske produktet av samme vis, som kan resultere i 7.364 milliarder rader. Jeg la det kjøre på min egen (ikke-produksjon!) system., Jeg hadde for å stoppe det før det kunne fullføre. Jeg må innrømme at jeg blir litt utålmodig!

Nysgjerrig på å se hva som foregår bak teppet, jeg vist gjennomføringsplan. Det ser ut som dette:

vær Oppmerksom på at en Nestede Løkker operatør ble valgt, og at anslått antall rader som kommer fra mitt bord variabelen er 1! Selv om jeg bare lastet den med 1 million rader! Hvordan kan dette være? Den triste sannheten er at SQL Server ikke har statistikk for tabell variabler, så på den tiden spørringen er satt sammen, det gjør ikke vet hvordan du kan rader er virkelig det., Hvis jeg hadde bare én rad, en nestet loop ville være fint! Her er hvordan det er definert i BOL:

Den nestede løkker bli med, også kalt nestede iterasjon, bruker man bli med innspill som den ytre inngang tabell (vist som den øverste inngang i det grafiske gjennomføringsplan) og en som indre (nederst) inn-tabell. Den ytre sløyfen bruker den ytre inngang tabell rad for rad. Den indre løkken, utført for hver ytre rad, kan du søke etter matchende rader i indre input-tabellen.

Så, for hver og en av de millioner rader i mitt bord variabel, SQL vil gjøre en skanning av objektet catalog view. Ikke! Det er bra!,

Mitt andre forsøk erstatter tabell variabel med en temp tabellen. Skriptet er identiske bortsett fra at det starter med:

1
2
3

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

Og erstatter @t med #t i resten av skriptet., Denne gangen gjennomføringsplan ser ut som dette:

Dette ser bedre! For én ting, estimert antall rader som kommer fra den midlertidige tabellen er riktig. For en annen, complier valgte en hash-match – et mye bedre valg for dette delta enn tabellen skanner vi hadde før.

jeg la denne spørringen kjøres til ferdigstillelse. Det gikk for litt over 3 minutter på min (riktignok gammel og treg!) bærbare pc-en og produsert 16,7 milliarder kroner rader før du treffer en tom for minne-feil (som det er mye av rader!,) Redusere antall rader i den midlertidige tabellen til 100.000 lov til det til slutt på en komfortabel ni sekunder.

Alt dette betyr at det er ingenting galt med CTE konstruere; noe annet må være å gå på. At noe annet er statistikk. SQL Server-kompilatoren bruker statistikk (f.eks. rad teller) for å informere optimizer. Men, statistikken er ikke opprettet eller oppdatert for tabell variabler. Dette er den «Aha!»øyeblikk! Endre skript for å bruke en midlertidig tabell i stedet for en tabell variabel ment at statistikk var tilgjengelig for å velge en bedre gjennomføringsplan.,

Grave Dypere

At noe annet er i liten frase i nærheten i begynnelsen av sitat fra BOL:

Angir en midlertidig heter resultat

Hvorfor er det viktig? Kan du tenke på en annen type midlertidig heter resultat? Hva med en tabell variabel! Vi har allerede funnet ut at bordet variabler, som har ingen statistikk, kan skape kaos med gjennomføringsplaner på grunn av feil rad teller. Imidlertid, en CTE har noe som ligner på et midlertidig resultat. Når du skriver en kompleks ETC, kan det hende det ikke er nok informasjon tilgjengelig for å oppnå en optimal plan., Når jeg tenker på at det er som skjer, her er hva jeg gjør:

  1. Tester hvert CTE på egen hånd, fra øverst til nederst for å se om/hvor utførelsestid eller rad teller eksplodere., Dette er lett å gjøre i SSMS ved å legge til en
    1
    2
    3

    SELECT * FROM &lt;CTE navn&gt;

    Like før den viktigste spørring som trekker CTEs sammen og utheving og kjører skriptet ned til det punktet.,

  2. Kontroller at den skyldige er riktig skrevet, med riktig predikater.
  3. Sørge for at predikater er indeksert, hvis det er mulig (mer om det senere).
  4. Gjenoppta testing fra at CTE videre til slutt.

Det er minst ett problem som ikke kan fikses med denne prosessen. Det er ikke alltid mulig å garantere at alle predikater er indeksert, spesielt for avledet resultater., I så fall, det er et annet alternativ å bryte flaskehals:

Split CTE ut i en midlertidig tabell.

Dette vil bety restrukturering søket ditt. I utgangspunktet, vil du ta den øverste halvdelen av spørringen, og skrive resultatet til en midlertidig tabell, deretter indeks tabellen, og deretter fortsette søket, som starter med den midlertidige tabellen. For eksempel, tenk at jeg har:

Anta videre at problemet er på CTE10.,

Nå som vi har resultatene så langt i en midlertidig tabell, indeks det:

1
2
3

CREATE INDEX IX_#CTE9 PÅ #CTE9(col1, col2, …)

Nå, la oss fullføre spørring:

Merk at du kanskje har fortsatt å fortsette den prosessen som er beskrevet ovenfor, inntil du kommer resultatene dine mål.,

Og en Annen Ting

Uansett hva du gjør, bli vant til å lese gjennomføringsplaner. Det er et vell av informasjon der, og hundrevis om ikke tusenvis av store ressurser på å forstå dem. Som en siste kontroll, skjønt, høyre-klikk på venstre-entry i den grafiske gjennomføringsplan. Det vil trolig være en VELGER, OPPDATERE, SETTER inn eller SLETTER. Nå i kontekst menyen, velg Egenskaper. Sørg for at du ser dette, omtrent halvveis ned:

Hvis du ikke ser Optimalisering Nivå FULL, det er en indikator på at søket er for kompleks., Vurdere å bryte det opp noen mer, ved hjelp av den midlertidige tabellen teknikk som er skissert ovenfor.

Konklusjon

Felles Bord Uttrykk er bare en annen måte å skrive subqueries. De er ikke magi, heller ikke SQL Server behandle dem på noen annen måte enn vanlig, nestede subqueries, bortsett fra deres rekursiv søskenbarn. CTEs kan gjøre koden enklere å lese og vedlikeholde, siden, når de brukes riktig, kan du skille bekymringer ved ETC. (Legg merke til hvordan jeg snek seg inn et Objekt-Orientert programmering prinsippet?)

Når de utfører dårlig, skjønt, ikke klandre ETC., Grave dypere for å forstå hva som skjer under den dekker. Den gjennomføringsplan er den perfekte måten å gjøre det på.

  • Forfatter
  • Siste Innlegg
Gerald Britton er en Ledende SQL Server-Løsningen, Designer, Forfatter, Programvare Utvikler, Lærer og en Microsoft-Plattform MVP. Han har mange års erfaring i IT-bransjen i ulike roller.,
Gerald spesialiserer seg i å løse SQL Server ytelse problemer, spesielt som de forholder seg til Business Intelligens løsninger. Han er også medforfatter av ebok «Komme i Gang Med Python» og en ivrig Python-utvikler, Lærer, og Pluralsight forfatter.
Du finner ham på LinkedIn på Twitter på twitter.,com/GeraldBritton eller @GeraldBritton, og på Pluralsight
Vis alle innlegg av Gerald Britton

Siste innlegg av Gerald Britton (se alle)
  • Snapshot Isolation i SQL Server – August 5, 2019
  • Krympende databasen ved hjelp av DBCC SHRINKFILE – August 16, 2018
  • Delvis lagrede prosedyrer i SQL Server – 8. juni 2018

Legg igjen en kommentar

Din e-postadresse vil ikke bli publisert. Obligatoriske felt er merket med *