Nabhan Hub

T-SQL-ikkunan toiminnot tehdä kirjallisesti monet kyselyt helpompaa, ja ne tarjoavat usein paremman suorituskyvyn kuin hyvin, yli vanhempia tekniikoita. Esimerkiksi LAG-funktion käyttäminen on niin paljon parempi kuin itse liittyminen. Saada parempi suorituskyky yleisesti, kuitenkin, sinun täytyy ymmärtää käsitteen rajaus ja miten-ikkunan toiminnot ovat riippuvaisia lajittelu antaa tuloksia.

huomautus: Katso Uusi artikkelini, miten Optimizerin parannukset vuonna 2019 vaikuttavat suorituskykyyn!,

OVER-lauseke ja lajittelu

OVER-lausekkeessa on kaksi vaihtoehtoa, jotka voivat aiheuttaa lajittelua: PARTITION BY ja ORDER BY. Osio BY tukee kaikki ikkunatoiminnot, mutta se on valinnainen. Tilaus on pakollinen useimmille funktioille. Riippuen siitä, mitä yrität saavuttaa, tiedot lajitellaan perustuu YLI lauseke, ja se voisi olla suorituskyvyn pullonkaula oman kyselyn.

OVER-lausekkeessa oleva järjestys option mukaan vaaditaan niin, että tietokantamoottori pystyy niin sanotusti jonottamaan rivejä, jotta toimintoa voidaan soveltaa oikeassa järjestyksessä., Sano esimerkiksi, että haluat, että ROW_NUMBER-funktiota sovelletaan Myyntidendin mukaisessa järjestyksessä. Tulokset näyttävät erilaisilta kuin jos haluat funktion sovellettavan totalduen järjestyksessä laskevassa järjestyksessä., Tässä on esimerkki:

Koska ensimmäinen kysely on käyttää klusterin keskeinen kuin ORDER BY vaihtoehto, ei lajittelu on tarpeen.

toinen kysely on kallis tavallaan toiminta.,

järjestä YLI-lauseke ei ole kytketty ORDER BY-lausekkeen lisätään yleistä kyselyn, joka voisi olla aivan erilainen., Here is an example showing what happens if the two are different:

aihekokonaisuuksien indeksi avain on SalesOrderID, mutta rivit on ensin lajiteltu TotalDue alenevassa järjestyksessä ja sitten takaisin SalesOrderID. Katso suorittamisen suunnitelma:

OSIO mainittiin, tuettu, mutta vapaaehtoinen, kaikille T-SQL-ikkunan toiminnot aiheuttaa myös lajittelu. Se on samanlainen, mutta ei aivan kuten, ryhmä lausekkeittain yhteenlasketut kyselyt., This example starts the row numbers over for each customer.

toteutus suunnitelma osoittaa, vain yksi sellainen toiminta, yhdistelmä CustomerID ja SalesOrderID.

ainoa tapa voittaa suorituskykyä vaikutus lajittelu on luoda indeksi erityisesti YLI lauseke. Kirjassaan Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions Itzik Ben-Gan suosittelee POC-indeksiä. POC tarkoittaa (P)ARTITION BY, (O)RDER BY ja (c)overing., Hän suosittelee lisäämään kaikki sarakkeet, joita käytetään suodattamiseen ennen osiota ja tilaamaan sarakkeet avaimessa. Lisää sitten kaikki muut sarakkeet, joita tarvitaan kattavan indeksin luomiseen mukana olevien sarakkeiden mukaisesti. Aivan kuten kaikki muu, sinun täytyy testata, miten tällainen indeksi vaikuttaa kyselyn ja yleinen työmäärä. Tietenkin, et voi lisätä indeksi jokaisen kyselyn, joka kirjoitat, mutta jos suorituskyky tietyn kyselyn, joka käyttää ikkunan toiminto on tärkeä, voit kokeilla tätä ohjetta.,

Here is an index that will improve the previous query:

Kun olet suorittaa kyselyn, lajitella toiminta on nyt mennyt suorittamisen suunnitelma:

Muotoilu

– mielestäni muotoilu on kaikkein vaikea käsite ymmärtää, kun oppia T-SQL-ikkunan toimintoja. Lisätietoja syntaksista on T-SQL-ikkunatoimintojen johdannossa., Kehystys on tarpeen, seuraavasti:

• Ikkunan aggregaatit JÄRJESTYS, jota käytetään käynnissä yhteensä tai liukuvia keskiarvoja, esimerkiksi
• FIRST_VALUE
• LAST_VALUE

Onneksi, kehystys ei tarvita suurimman osan ajasta, mutta valitettavasti, se on helppo ohittaa rungon ja käyttää oletuksena. Oletuskehys on aina rajattoman edeltävän ja nykyisen rivin välillä. Kun saat oikeita tuloksia niin kauan kuin JÄRJESTYS vaihtoehto koostuu ainutlaatuinen sarake tai sarakkeet, näet suorituskykyä osuma.,id=”4b64f77d3b”>

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

SET TILASTOT IO EDELLEEN; MENNÄ VALITSE CustomerID, SalesOrderID, TotalDue, SUMMA(TotalDue) YLI(OSIO CustomerID JÄRJESTYS SalesOrderID) KUTEN RunningTotal Myynnistä.,SalesOrderHeader; VALITSE CustomerID, SalesOrderID, TotalDue, SUMMA(TotalDue) YLI(OSIO CustomerID JÄRJESTYS SalesOrderID RIVIEN VÄLISTÄ RAJATON EDELLISEN JA NYKYISEN RIVIN) KUTEN RunningTotal Myynnistä.SalesOrderHeader;

tulokset ovat samat, mutta suorituskyky on hyvin erilainen. Valitettavasti toteutus suunnitelma ei kerro totuutta tässä tapauksessa., Se kertoo, että jokainen kyselyn otti 50% luonnonvaroja:

Jos et tarkistaa tilastot IO-arvot, näet eron:

oikea kehys, on vieläkin tärkeämpää, jos tilauksesi vaihtoehto ei ole ainutlaatuinen tai jos käytät LAST_VALUE. Tässä esimerkissä KOLUMNIKOHTAINEN tilaus on järjestysnumero, mutta jotkut asiakkaat ovat tehneet useamman kuin yhden tilauksen tiettynä päivänä. Jos et aseta runko, tai käyttämällä RANGE-toiminto herkkuja matching osana samaan ikkunaan.,”>

syy tähän epäsuhtaan on se, että VÄLILLÄ näkee tiedot loogisesti, kun RIVIT näkee sen positionally. Tähän ongelmaan on kaksi ratkaisua. Yksi on varmistaa, että tilaus vaihtoehto on ainutlaatuinen. Toinen ja tärkeämpi vaihtoehto on aina määritellä kehys, jossa sitä tuetaan.

toinen paikka, jossa rajaus aiheuttaa loogisia ongelmia, on LAST_VALUE., LAST_VALUE palauttaa lausekkeen kehyksen viimeiseltä riviltä. Koska oletuksena runko (VÄLILLÄ RAJATON EDELLISEN JA NYKYISEN RIVIN) vain menee jopa nykyisen rivin viimeinen rivi runko on rivi, jossa laskenta suoritetaan.,

Ikkuna-Aggregaattien

Yksi kätevimmät ominaisuus T-SQL-ikkunan toimintoja on kyky lisätä yhteenlaskettu ilmaisu ei-aggregaatti kyselyn. Valitettavasti tämä voi usein toimia huonosti. Nähdäksesi ongelman, sinun täytyy tarkastella tilastoja IO tuloksia, jossa näet suuren määrän loogisia lukemia., Minun neuvoja, kun sinun täytyy palauttaa arvot eri granularities saman kyselyn suuri määrä rivejä on käyttää yksi vanhempi tekniikoita, kuten yhteisen pöydän ilme (CTE), temp-table, tai jopa muuttuva. Jos on mahdollista Pre-aggregoida ennen ikkunan aggregaatin käyttöä, se on toinen vaihtoehto.,ows ero ikkuna aggregaatti ja toinen tekniikka:

ensimmäinen kysely vain skannaa kerran, mutta se on 28,823 looginen lukee terävät kappaleet eivät vahingoita alumiinikuorta. Toinen menetelmä skannaa taulukon kaksi kertaa, mutta se ei tarvitse worktable.

seuraava esimerkki käyttää windows yhteenlaskettu soveltaa yhteenlaskettu ilme:

Kun käytät ikkunan toimintojen yhteenlaskettu kyselyn, ilme on noudatettava samoja sääntöjä kuin SELECT-ja ORDER BY-lausekkeita., Tällöin ikkunafunktiota sovelletaan summaan (TotalDue). Se näyttää kuin sisäkkäisiä yhteensä, mutta se on todella ikkuna toiminto soveltaa yhteenlaskettu ilme.

Koska tiedot on koottu ennen ikkunan toiminto on sovellettu, suorituskyky on hyvä:

Siellä on yksi mielenkiintoinen asia tietää, kun käyttää ikkuna-aggregaatit. Jos käytät useita lausekkeita, jotka käyttävät vastaavia lausekkeiden määritelmiä, et näe suorituskyvyn heikkenemistä.

neuvoni on käyttää tätä toimintoa varoen., Se on melko kätevä, mutta ei skaalaa niin hyvin.

Suorituskykyvertailut

tähän mennessä esitetyissä esimerkeissä on käytetty pientä myyntiä.SalesOrderHeader taulukko AdventureWorks ja tarkistetaan toteutussuunnitelmat ja looginen lukee. Tosielämässä asiakkaasi eivät välitä toteutussuunnitelmasta tai loogisista lukemista; he välittävät siitä, kuinka nopeasti kyselyt kulkevat. Nähdäkseni juoksuaikojen eron paremmin käytin Adam Machanicin Thinking Big (Adventure) – käsikirjoitusta twistillä.

skripti luo bigTransactionHistory-nimisen taulukon, joka sisältää yli 30 miljoonaa riviä., Näytettyäni Adamin käsikirjoituksen, loin kaksi kappaletta lisää hänen pöytäänsä, jossa oli 15 ja 7,5 miljoonaa riviä. Muutin myös Hävittämään tuloksia suorittamisen jälkeen kiinteistön kyselyeditori niin, että asuttavat verkkoon ei vaikuttanut ajaa kertaa. Juoksin jokaisen testin kolme kertaa ja tyhjennetään puskuri cache ennen jokaista ajaa.,

I can’t say enough about how important it is to use the frame when it’s supported., Nähdä ero, testasin laskea käynnissä yhteensä neljällä menetelmiä:

• Kohdistin ratkaisu
• Korreloi sub-kysely
• – Ikkunassa toiminto oletuksena kehys
• – Ikkunassa toiminto RIVIT

juoksin testi kolme uutta taulukoita. Tässä ovat tulokset kaavion muodossa:

Kun käynnissä RIVIÄ runko, 7,5 miljoonaa euroa rivi taulukko kesti vähemmän kuin toinen ajaa järjestelmän käytin, kun testin suorittamista. 30 miljoonan rivitaulukon läpimeno kesti noin minuutin., 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., Tässä tapauksessa käytin vain 30 miljoonan rivin taulukkoa, mutta tein yhden, kaksi tai kolme laskelmaa käyttäen samaa rakeisuutta ja siten samaa lauseketta. Vertasin ikkunan yhteenlaskettu suorituskyky CTE ja korreloi alikysely.

ikkuna yhteenlaskettu suoritetaan pahin, noin 1,75 minuuttia kussakin tapauksessa. CTE suoriutui parhaiten laskelmien määrää nostaessaan, sillä taulukkoon koskettiin vain kerran kaikkien kolmen kohdalla., Korreloi alikysely suoritetaan pahempi, kun kasvava määrä laskelmia, koska jokainen laskenta piti suorittaa erikseen, ja se kosketti pöydän yhteensä neljä kertaa.,v>

Johtopäätös

T-SQL-ikkunan toimintoja on edistetty niin on hyvä suorituskyky. Mielestäni ne helpottavat kyselyjen kirjoittamista, mutta niitä pitää ymmärtää hyvin, jotta saadaan hyvä suoritus. Indeksoinnilla voi olla merkitystä, mutta jokaista kirjoittamaasi kyselyä varten ei voi luoda indeksiä., Rajaus ei ehkä ole helppo ymmärtää, mutta se on niin tärkeää, jos sinun täytyy skaalata jopa suuria taulukoita.

---