Redgate Hub (Norsk)

T-SQL vinduet funksjoner gjør skriving av mange forespørsler lettere, og de gir ofte bedre ytelse så vel over eldre teknikker. For eksempel, ved hjelp av LAG-funksjonen er så mye bedre enn å gjøre en selv-bli med. For å få bedre ytelse totalt sett, men du trenger å forstå begrepet framing og hvordan vinduet funksjoner stole på sortering for å gi resultater.

MERK: Se min nye artikkelen for å lære hvordan forbedringer til optimizer i 2019 påvirke ytelsen!,

OVER Punkt og Sortering

Det er to alternativer i OVER punkt som kan føre til sortering: – PARTISJONEN AV og BESTILLING AV. PARTISJON som er støttet av alle vindu funksjoner, men det er valgfritt. REKKEFØLGEN er nødvendig for de fleste av funksjonene. Avhengig av hva du prøver å oppnå, vil data bli sortert basert på OVER punkt, og som kan være ytelsen flaskehals for søket ditt.

ORDREN VED valget i OVER punkt er nødvendig, slik at databasemotoren kan stille opp rader, så å si, for å bruke den funksjonen i riktig rekkefølge., For eksempel, si at du ønsker det ROW_NUMBER funksjon for å bli tatt i bruk for av SalesOrderID. Resultatene vil se annerledes ut enn hvis du vil at funksjonen brukes for TotalDue i synkende rekkefølge., Her er et eksempel:

Siden den første spørringen ved hjelp av klyngen tast som den REKKEFØLGEN VED alternativ, ingen sortering er nødvendig.

Den andre spørringen har en dyr form drift.,

REKKEFØLGEN i OVER klausul er ikke koblet til ORDER BY-setningsdel lagt til den generelle spørring som kan være ganske forskjellige., Here is an example showing what happens if the two are different:

samlet indeks-tasten er SalesOrderID, men radene må først bli sortert etter TotalDue i synkende rekkefølge, og deretter tilbake til SalesOrderID. Ta en titt på gjennomføringsplan:

PARTISJONEN MED klausul, som støttes, men valgfritt, for alle T-SQL vinduet funksjoner fører også til sortering. Det ligner, men ikke akkurat som GRUPPEN AV punkt for samlet spørringer., This example starts the row numbers over for each customer.

gjennomføringsplan viser bare en form drift, en kombinasjon av kunde-id og SalesOrderID.

Den eneste måten å overvinne ytelse virkningen av sortering er å opprette en indeks spesielt for de OVER punkt. I sin bok Microsoft SQL Server 2012 med Høy Ytelse T-SQL Bruke Vinduet Funksjoner, Itzik Ben-Gan anbefaler POC-indeksen. POC står for (P)ARTITION AV (O)RDER AV, og (c)overing., Han anbefaler å legge til kolonner som brukes for filtrering før PARTISJON AV og REKKEFØLGE AV kolonner i nøkkelen. Deretter legge til noen ekstra kolonner som trengs for å skape et dekke indeks som er inkludert kolonner. Akkurat som alt annet, vil du trenger for å teste å se hvordan en slik indeks konsekvenser søket, og samlet arbeidsbelastning. Selvfølgelig, du kan ikke legge til en indeks for hver spørring som du skriver, men dersom utførelsen av et bestemt spørring som bruker et vindu funksjon er viktig, og du kan prøve ut dette rådet.,

Here is an index that will improve the previous query:

Når du kjører spørringen, den typen drift er nå borte fra gjennomføringsplan:

Innramming

etter min mening, rammer er den mest vanskelig konsept å forstå når lære om T-SQL vinduet funksjoner. For å lære mer om syntaks se introduksjon til T-SQL vinduet funksjoner., Framing er nødvendig for følgende:

• Vindu aggregater med REKKEFØLGEN av, som brukes til å kjøre består av, eller glidende gjennomsnitt, for eksempel
• FIRST_VALUE
• LAST_VALUE

Heldigvis, rammer er ikke nødvendig mesteparten av tiden, men dessverre, det er lett å hoppe over rammen og bruker standard. Standard ramme er alltid variere MELLOM UBEGRENSET FOREGÅENDE OG NÅVÆRENDE RAD. Mens du vil få riktige resultater så lenge ORDREN VED alternativet består av en unik kolonne eller et sett med kolonner, vil du se en ytelse hit.,id=»4b64f77d3b»>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

ANGI STATISTIKK IO PÅ; GÅ VELG kunde-id, SalesOrderID, TotalDue, SUM(TotalDue) OVER(PARTISJON AV CustomerID BESTILLING AV SalesOrderID) SOM RunningTotal FRA Salg.,SalesOrderHeader; VELG kunde-id, SalesOrderID, TotalDue, SUM(TotalDue) OVER(PARTISJON AV CustomerID BESTILLING AV SalesOrderID RADER MELLOM UBEGRENSET FOREGÅENDE OG NÅVÆRENDE RAD) SOM RunningTotal FRA Salg.SalesOrderHeader;

resultatene er De samme, men resultatene er svært forskjellige. Dessverre, gjennomføringsplan ikke fortelle deg sannheten i denne saken., Det rapporter om at hver spørring tok 50% av ressursene:

Hvis du leser statistikken IO verdier, vil du se forskjellen:

ved Hjelp av det riktige bildet er enda mer viktig hvis din ORDRE VIA alternativet er ikke unike eller hvis du bruker LAST_VALUE. I dette eksemplet er BESTILLING AV kolonnen er OrderDate, men noen kunder har plassert mer enn én ordre på et gitt tidspunkt. Når du ikke angi rammen, eller ved hjelp av UTVALG, funksjon behandler matchende datoer som en del av det samme vinduet.,»>

grunnen til avviket er at UTVALGET ser på data logisk mens RADER ser det positionally. Det er to løsninger på dette problemet. Det ene er å sørge for at REKKEFØLGEN AV alternativet er unik. Den andre og mer viktig alternativ er å alltid oppgi ramme der det er støttet.

Det andre stedet som rammer fører til logiske problemer med LAST_VALUE., LAST_VALUE returnerer et uttrykk fra den siste raden av rammen. Siden standard ramme (varierer MELLOM UBEGRENSET FOREGÅENDE OG NÅVÆRENDE RAD) bare går opp til den aktuelle raden, den siste raden av rammen er rad hvor beregningen utføres.,

– Vinduet Aggregater

En av de handiest funksjon av T-SQL vinduet funksjoner er muligheten til å legge til et samlet uttrykk for en ikke-samlet spørring. Dessverre, dette kan ofte fungerer dårlig. For å se problemet, må du se på statistikken for IO resultater hvor du vil se et stort antall av logiske leser., Mitt råd når du trenger å returnere verdier på ulike granularities innenfor den samme spørringen for et stort antall rader er å bruke en av de eldre teknikker, som for eksempel et felles bord uttrykk (CTE), temp tabellen, eller til og med en variabel. Hvis det er mulig å pre-samlet før du bruker window-gruppen, som er et annet alternativ.,ows forskjellen mellom vinduet samlede og en annen teknikk:

Den første spørringen bare skanner bordet en gang, men det har 28,823 logiske leser i en worktable. Den andre metoden skanner bordet to ganger, men det trenger ikke de worktable.

Den neste eksempel bruker en windows samlede lagt til et samlet uttrykk:

Når du bruker vinduet funksjoner i en samlet spørring, uttrykket må følge de samme reglene som de VELGER og BESTILLING AV klausuler., I dette tilfellet, vindu funksjon er lagt til SUM(TotalDue). Det ser ut som en nestet samlet, men det er egentlig et vindu funksjon lagt til et samlet uttrykk.

Siden de data som har vært samlet før vinduet funksjonen ble anvendt, de resultater som er bra:

Det er en mer interessante ting å vite om hvordan du bruker vinduet gruppene. Hvis du bruker flere uttrykk som bruker matchende OVER punkt definisjoner, vil du ikke se en ytterligere redusert ytelse.

Mitt råd er å bruke denne funksjonen med forsiktighet., Det er ganske hendig, men ikke skala som godt.

Ytelse Sammenligninger

eksemplene som er presentert så langt har brukt lite Salg.SalesOrderHeader tabell fra AdventureWorks og anmeldt for kjøring planer og logiske leser. I det virkelige liv, vil kundene dine ikke bryr seg om gjennomføringsplan eller den logiske leser; de vil bry seg om hvor fort kjøre spørringer. For å bedre se forskjellen i løp ganger, jeg brukte Adam Machanic Tenker Stor (Eventyr) manus med en vri.

skriptet lager en tabell kalt bigTransactionHistory som inneholder over 30 millioner rader., Etter å ha kjørt Adams manus, laget jeg to eksemplarer av hans bord, med 15 og 7,5 millioner rader henholdsvis. Jeg har også slått på Forkast-resultater etter gjennomføring eiendom i Query Editor slik at du fyller rutenett ikke påvirke kjøre ganger. Jeg løp hver testen tre ganger, og ryddet buffer cache før hver kjøring.,

I can’t say enough about how important it is to use the frame when it’s supported., For å se forskjellen, jeg kjørte en test for å beregne kjører tilsammen med fire metoder:

• Markøren løsning
• Korrelert sub-spørring
• Vindu funksjon med standard ramme
• Vindu funksjon med RADER

jeg kjørte testen på tre nye tabeller. Her er resultatene i et diagram-format:

Når du kjører med RADER rammen, 7,5 millioner rad tabell tok mindre enn et sekund å kjøre på systemet, jeg var med når du utfører testen. De 30 millioner rad tabell tok omtrent ett minutt å kjøre., id=»4b64f77d3b»>

1 2 3 4 5

SELECT ProductID, SUM(ActualCost) OVER(PARTITION BY ProductID ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM bigTransactionHistory;

I also performed a test to see how window aggregates performed compared to traditional techniques., I dette tilfellet, jeg brukte bare 30 millioner rad tabellen, men utførte én, to, eller tre beregninger ved hjelp av den samme detaljer og, derfor, samme OVER punkt. Jeg sammenlignet vinduet samlede ytelsen til et CTE og til en korrelert subquery.

vinduet samlede utført det verste, om 1.75 minutter i hvert enkelt tilfelle. CTE utført best når øke antall beregninger siden bordet var bare berørt en gang for alle tre., Den korrelert subquery utført verre når øke antall beregninger siden hver beregning måtte kjøre separat, og det rørte ved bordet totalt fire ganger.,v>

Konklusjon

T-SQL vinduet funksjoner har blitt fremmet som blir flott for ytelse. I min mening, de gjøre skriftlig spørsmål enklere, men du må forstå dem godt for å få god ytelse. Indeksering kan gjøre en forskjell, men du kan ikke opprette en indeks for hver spørring du skriver., Rammer kan ikke være lett å forstå, men det er så viktig hvis du trenger til å skalere opp til store tabeller.

---