Tämä artikkeli on neljäs osa sarjassa T-SQL-virheistä, sudenkuopista ja parhaista käytännöistä. Aiemmin käsittelin determinismiä, alikyselyjä ja yhdistelmiä. Tämän kuun artikkelissa keskitytään ikkunafunktioihin liittyviin virheisiin, sudenkuoppiin ja parhaisiin käytäntöihin. Kiitos Erland Sommarskogille, Aaron Bertrandille, Alejandro Mesalle, Umachandar Jayachandranille (UC), Fabiano Neves Amorimille, Milos Radivojevicille, Simon Sabinille, Adam Machanicille, Thomas Grohserille, Chan Ming Manille ja Paul Whitelle, jotka tarjositte ideoitanne!

Esimerkeissäni käytän esimerkkitietokantaa nimeltä TSQLV5. Löydät skriptin, joka luo ja täyttää tämän tietokannan, täältä ja sen ER-kaavion täältä.

Ikkunafunktioihin liittyy kaksi yleistä sudenkuoppaa, jotka molemmat johtuvat SQL-standardin asettamista vastakkaisista implisiittisistä oletusarvoista. Toinen sudenkuoppa liittyy juoksevien kokonaissummien laskentaan, jossa saat ikkunakehyksen implisiittisellä RANGE-valinnalla. Toinen sudenkuoppa liittyy jonkin verran tähän, mutta sillä on vakavammat seuraukset, ja siihen liittyy implisiittinen kehysmäärittely FIRST_VALUE- ja LAST_VALUE-funktioille.

Ikkunakehys implisiittisellä RANGE-vaihtoehdolla

Ensimmäinen sudenkuoppa liittyy juoksevien loppusummien laskentaan aggregoidulla ikkunafunktiolla, jossa määritetään nimenomaisesti ikkunan järjestyslause mutta ei määritetä eksplisiittisesti ikkunan kehyksen yksikköä (ROWS tai RANGE) ja siihen liittyvää ikkunakehyksen laajuutta, esim, ROWS UNBOUNDED EDELLINEN. Implisiittinen oletusarvo on epäintuitiivinen, ja sen seuraukset voivat olla yllättäviä ja tuskallisia.

Tämän sudenkuopan havainnollistamiseksi käytän taulukkoa nimeltä Transactions, jossa on kaksi miljoonaa pankkitilitapahtumaa, joissa on hyvityksiä (positiivisia arvoja) ja veloituksia (negatiivisia arvoja). Suorituskykyrangaistuksella on merkitystä vain silloin, kun ikkunafunktio optimoidaan rivitilan käsittelyoperaattoreilla. SQL Server 2016 esittelee eräajotilassa toimivan Window Aggregate -operaattorin, joka poistaa sudenkuopan suorituskykysakko-osuuden, mutta ennen SQL Server 2019:ää tätä operaattoria käytetään vain, jos tiedoissa on läsnä columnstore-indeksi. SQL Server 2019 ottaa käyttöön rowstore-tuen eräajotilan, joten voit saada eräajotilassa tapahtuvan käsittelyn, vaikka tiedoissa ei olisikaan columnstore-indeksejä. Voit havainnollistaa rivitilan käsittelyn suorituskykyhaittaa, jos käytät tämän artikkelin koodinäytteitä SQL Server 2019:ssä tai uudemmassa SQL Server 2019:ssä tai Azure SQL Database -tietokannassa, käytä seuraavaa koodia asettaaksesi tietokannan yhteensopivuustason 140:ksi, jotta rivitilan eräajotilaa ei vielä oteta käyttöön:

 ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 140;

Käytä seuraavaa koodia ottaaksesi aika- ja I/O-tilastot käyttöön istunnossa:

 SET STATISTICS TIME, IO ON;

Välttääksesi kahden miljoonan rivin tulostuksen odottamisen SSMS:ssä suosittelen, että suoritat tämän osan koodinäytteet siten, että Discard results after execution (Hylkää tulokset suorituksen jälkeen) -vaihtoehto on käytössä (siirry Query Options (Kyselyn asetukset), Results (Tulokset), Grid (Ruudukko) -vaihtoehtoon ja ruksaa Discard results after execution (Hylkää tulokset suorituksen jälkeen).

Kuva 1: Kyselyn 1 suunnitelma, row-mode-prosessointi

Suunnitelma vetää tiedot esijärjestettynä taulun klusteroidusta indeksistä. Sitten se käyttää Segment- ja Sequence Project -operaattoreita rivinumeroiden laskemiseen selvittääkseen, mitkä rivit kuuluvat nykyisen rivin kehykseen. Sitten se käyttää Segment-, Window Spool- ja Stream Aggregate -operaattoreita laskeakseen ikkuna-aggregaattifunktion. Window Spool -operaattoria käytetään keräilemään kehysrivit, jotka on sitten aggregoitava. Ilman erityistä optimointia suunnitelman olisi pitänyt kirjoittaa rivikohtaisesti kaikki sovellettavat kehysrivit spooliin ja sitten aggregoida ne. Tämä olisi johtanut nelinkertaiseen eli N2-kompleksisuuteen. Hyvä uutinen on se, että kun kehys alkaa UNBOUNDED PRECEDING -merkinnällä, SQL Server tunnistaa tapauksen fast track -tapaukseksi, jossa se yksinkertaisesti ottaa edellisen rivin juoksevan kokonaissumman ja lisää nykyisen rivin arvon laskeakseen nykyisen rivin juoksevan kokonaissumman, mikä johtaa lineaariseen skaalaukseen. Tässä fast track -tilassa suunnitelma kirjoittaa spooliin vain kaksi riviä syöttöriviä kohden – yhden aggregaatin ja yhden yksityiskohdan.

Ikkunan spool voidaan toteuttaa fyysisesti kahdella tavalla. Joko nopeana muistissa olevana spoolina, joka on suunniteltu erityisesti ikkunatoimintoja varten, tai hitaana levyllä olevana spoolina, joka on lähinnä väliaikainen taulukko tempdb:ssä. Jos niiden rivien määrä, jotka on kirjoitettava spooliin per taustalla oleva rivi, voi ylittää 10 000 riviä tai jos SQL Server ei pysty ennustamaan lukumäärää, se käyttää hitaampaa levyllä olevaa spoolia. Kyselysuunnitelmassamme on tasan kaksi riviä, jotka on kirjoitettu spooliin jokaista taustalla olevaa riviä kohti, joten SQL Server käyttää muistissa olevaa spoolia. Valitettavasti suunnitelmasta ei voi mitenkään päätellä, millainen spooleri on kyseessä. Tämä voidaan selvittää kahdella tavalla. Yksi on käyttää laajennettua tapahtumaa nimeltä window_spool_ondisk_warning. Toinen vaihtoehto on ottaa käyttöön STATISTICS IO ja tarkistaa Worktable-nimisen taulukon raportoitujen loogisten lukujen määrä. Nollaa suurempi luku tarkoittaa, että sinulla on levyllä oleva spool. Nolla tarkoittaa, että sinulla on muistissa oleva spool. Tässä ovat kyselymme I/O-tilastot:

Kuten näet, saimme käytettyä muistin sisäistä spoolia. Näin on yleensä silloin, kun käytät ROWS-ikkunan kehysyksikköä, jossa UNBOUNDED PRECEDING on ensimmäinen rajaaja.

Tässä ovat kyselymme aikatilastot:

Tämän kyselyn suorittaminen kesti koneellani noin 4,5 sekuntia, kun tulokset hylättiin.

Nyt on vuorossa juju. Jos käytät ROWS-vaihtoehdon sijasta RANGE-vaihtoehtoa samoilla rajausmerkeillä, merkityksessä voi olla hienoinen ero, mutta suorituskyvyssä rivitilassa suuri ero. Merkitysero on merkityksellinen vain, jos sinulla ei ole täydellistä järjestystä, eli jos järjestät jonkun muun kuin yksikäsitteisen parametrin mukaan. ROWS UNBOUNDED PRECEDING -vaihtoehto pysähtyy nykyiselle riville, joten tasapisteiden tapauksessa laskenta on epämääräinen. Sitä vastoin RANGE UNBOUNDED PRECEDING -vaihtoehto katsoo nykyisen rivin edelle ja sisällyttää tasapisteet, jos niitä on. Se käyttää samanlaista logiikkaa kuin TOP WITH TIES -vaihtoehto. Kun sinulla on totaalinen järjestys, eli järjestät jonkin ainutkertaisen ominaisuuden mukaan, siteitä ei ole sisällytettävissä, ja siksi ROWS ja RANGE ovat loogisesti samanarvoisia tällaisessa tapauksessa. Ongelmana on se, että kun käytät RANGEa, SQL Server käyttää aina levyllä olevaa spool-tietokantaa rivitilassa tapahtuvassa käsittelyssä, koska tiettyä riviä käsiteltäessä se ei voi ennustaa, kuinka monta muuta riviä sisällytetään.

Kuvio 2: Suunnitelma kyselylle 2, rivitilan käsittely

Kysely 2 vastaa loogisesti kyselyä 1, koska meillä on totaalijärjestys; koska se kuitenkin käyttää RANGEa, se optimoidaan levyllä olevan spoolin avulla. Huomaa, että kyselyn 2 suunnitelmassa Window Spool näyttää samalta kuin kyselyn 1 suunnitelmassa, ja arvioidut kustannukset ovat samat.

Tässä ovat aika- ja I/O-tilastot kyselyn 2 suorittamisesta:

Huomaa suuri määrä loogisia lukuja Worktablea vastaan, mikä osoittaa, että olet saanut levyllä olevan spoolin. Suoritusaika on yli neljä kertaa pidempi kuin kyselyllä 1.

Jos ajattelet, että jos näin on, vältät yksinkertaisesti RANGE-vaihtoehdon käyttämistä, ellet todella tarvitse sisällyttää sidoksia, se on hyvä ajatus. Ongelma on se, että jos käytät ikkunafunktiota, joka tukee kehystä (aggregaatit, FIRST_VALUE, LAST_VALUE), jossa on eksplisiittinen ikkunan järjestyslauseke, mutta jossa ei mainita ikkunan kehysyksikköä ja siihen liittyvää laajuutta, saat oletuksena RANGE UNBOUNDED PRECEDING. Tämä oletusarvo on SQL-standardin määräämä, ja standardi valitsi sen, koska se yleensä suosii deterministisempiä vaihtoehtoja oletusarvoina. Asia on niin, että koska funktio käyttää kokonaisjärjestystä, saat saman tuloksen kuin ROWS:lla, joten tuloksesta ei voi päätellä, että kyseessä on ongelma. Saamasi suorituskykyluvut ovat kuitenkin samat kuin kyselyssä 2. Näen ihmisten lankeavan tähän ansaan jatkuvasti.

Paras käytäntö tämän ongelman välttämiseksi on, että tapauksissa, joissa käytät ikkunafunktiota kehyksen kanssa, ilmoita selkeästi ikkunan kehysyksikkö ja sen laajuus ja suosi yleensä ROWSia. Varaa RANGE:n käyttö vain tapauksiin, joissa järjestys ei ole yksiselitteinen ja sinun täytyy sisällyttää siteet.

 orderdate orderid val sumrows sumrange ---------- -------- -------- -------- --------- 2017-07-04 10248 440.00 440.00 440.00 2017-07-05 10249 1863.40 2303.40 2303.40 2017-07-08 10250 1552.60 3856.00 4510.06 2017-07-08 10251 654.06 4510.06 4510.06 2017-07-09 10252 3597.90 8107.96 8107.96 ...

Huomaa ero tuloksissa niiden rivien osalta, joissa sama tilauspäivä esiintyy useammin kuin kerran, kuten 8. heinäkuuta 2017. Huomaa, että ROWS-vaihtoehto ei sisällä sidonnaisuuksia ja on siten epädeterministinen, ja että RANGE-vaihtoehto sisältää sidonnaisuudet ja on siten aina deterministinen.

On kuitenkin kyseenalaista, onko käytännössä tapauksia, joissa tilaatte jotakin, joka ei ole ainutkertainen, ja tarvitsette sidonnaisuuksien sisällyttämistä, jotta laskennasta tulisi deterministinen. Mitä luultavasti paljon yleisempää käytännössä on tehdä jompikumpi kahdesta asiasta. Toinen on rikkoa sidonnaisuudet lisäämällä ikkunan järjestykseen jotain, joka tekee siitä ainutkertaisen, ja näin saadaan aikaan deterministinen laskenta, esimerkiksi näin:

 SELECT orderdate, orderid, val, SUM(val) OVER( ORDER BY orderdate, orderid ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues ORDER BY orderdate;

Tämä kysely tuottaa seuraavanlaisen tuloksen:

 orderdate orderid val runningsum ---------- -------- --------- ----------- 2017-07-04 10248 440.00 440.00 2017-07-05 10249 1863.40 2303.40 2017-07-08 10250 1552.60 3856.00 2017-07-08 10251 654.06 4510.06 2017-07-09 10252 3597.90 8107.96 ...

Toinen vaihtoehto on soveltaa alustavaa ryhmittelyä, meidän tapauksessamme tilauspäivämäärän mukaan, seuraavasti:

 SELECT orderdate, SUM(val) AS daytotal, SUM(SUM(val)) OVER( ORDER BY orderdate ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues GROUP BY orderdate ORDER BY orderdate;

Tämä kysely tuottaa seuraavan tulosteen, jossa kukin tilauspäivämäärä esiintyy vain kerran:

 orderdate daytotal runningsum ---------- --------- ----------- 2017-07-04 440.00 440.00 2017-07-05 1863.40 2303.40 2017-07-08 2206.66 4510.06 2017-07-09 3597.90 8107.96 ...

Jokatapauksessa muistakaa tässä yhteydessä paras käytäntö!

Hyvä uutinen on se, että jos käytät SQL Server 2016:ta tai uudempaa SQL Serveriä ja tiedoissa on läsnä columnstore-indeksi (vaikka se olisi väärennetty suodatettu columnstore-indeksi) tai jos käytät SQL Server 2019:ää tai uudempaa SQL Serveriä tai Azure SQL Database -tietokantaa, riippumatta columnstore-indeksien läsnäolosta, kaikki kolme edellä mainittua kyselyä saadaan optimoitua eräajotilassa olevalla Window Aggregate -operaattorilla. Tämän operaattorin avulla monet rivitilan käsittelyn tehottomuudet poistuvat. Tämä operaattori ei käytä lainkaan spool-operaattoria, joten kysymystä muistissa tai levyllä olevasta spool-operaattorista ei ole. Se käyttää kehittyneempää käsittelyä, jossa se voi soveltaa useita rinnakkaisia läpikäyntejä muistissa olevaan riviikkunaan sekä ROWS- että RANGE-operaattorin osalta.

 SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;

Tämän kyselyn suunnitelma näkyy kuvassa 3.

Kuva 3: Suunnitelma kyselylle 1, eräajotilassa tapahtuva käsittely

Tässä ovat suorituskykytilastot, jotka sain tälle kyselylle:

 SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS firstval, LAST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS lastval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;

Jos uskot virheellisesti, että nämä funktiot toimivat koko ikkunan osiossa, mihin uskovat monet näitä funktioita ensimmäistä kertaa käyttävät, oletat luonnollisesti, että FIRST_VALUE palauttaisi asiakkaan ensimmäisen tilauksen tilausarvon ja LAST_VALUE palauttaisi asiakkaan viimeisimmän tilauksen arvon. Käytännössä nämä toiminnot kuitenkin tukevat kehystä. Muistutuksena mainittakoon, että kun kehystä tukevissa funktioissa määritetään ikkunan järjestyslauseke mutta ei ikkunan kehysyksikköä ja siihen liittyvää laajuutta, oletuksena on RANGE UNBOUNDED PRECEDING. FIRST_VALUE-funktiolla saat odotetun tuloksen, mutta jos kyselysi optimoidaan rivitilan operaattoreilla, maksat rangaistuksen levyllä olevan spoolin käytöstä. LAST_VALUE-funktion kanssa tilanne on vielä pahempi. Sen lisäksi, että maksat rangaistuksen levyllä olevan spoolin käytöstä, sen sijaan, että saisit arvon osion viimeiseltä riviltä, saat arvon nykyiseltä riviltä!

Tässä on yllä olevan kyselyn tuloste:

 custid orderdate orderid val firstval lastval ------- ---------- -------- ---------- ---------- ---------- 1 2018-08-25 10643 814.50 814.50 814.50 1 2018-10-03 10692 878.00 814.50 878.00 1 2018-10-13 10702 330.00 814.50 330.00 1 2019-01-15 10835 845.80 814.50 845.80 1 2019-03-16 10952 471.20 814.50 471.20 1 2019-04-09 11011 933.50 814.50 933.50 2 2017-09-18 10308 88.80 88.80 88.80 2 2018-08-08 10625 479.75 88.80 479.75 2 2018-11-28 10759 320.00 88.80 320.00 2 2019-03-04 10926 514.40 88.80 514.40 3 2017-11-27 10365 403.20 403.20 403.20 3 2018-04-15 10507 749.06 403.20 749.06 3 2018-05-13 10535 1940.85 403.20 1940.85 3 2018-06-19 10573 2082.00 403.20 2082.00 3 2018-09-22 10677 813.37 403.20 813.37 3 2018-09-25 10682 375.50 403.20 375.50 3 2019-01-28 10856 660.00 403.20 660.00 ...

Usein, kun ihmiset näkevät tällaisen tulosteen ensimmäistä kertaa, he luulevat, että SQL Serverissä on jokin virhe. Mutta näin ei tietenkään ole, vaan se on yksinkertaisesti SQL-standardin oletusarvo. Kyselyssä on virhe. Kun tajuat, että kyseessä on kehys, haluat olla selkeä kehysmäärittelyn suhteen ja käyttää minimikehystä, joka kuvaa haluamasi rivin. Varmista myös, että käytät ROWS-yksikköä. Jos haluat siis saada osion ensimmäisen rivin, käytä FIRST_VALUE-funktiota ja kehystä ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Jos haluat saada osion viimeisen rivin, käytä LAST_VALUE-funktiota ja kehystä ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING. Jos mietit asiaa, käytät niitä useimmiten saadaksesi jotain osiossa olevilta ensimmäisiltä tai viimeisiltä riveiltä. Jos tarvitset arvon vaikkapa kaksi riviä ennen nykyistä, sen sijaan, että käyttäisit FIRST_VALUEa kehyksen kanssa, joka alkaa 2 PRECEDING, eikö ole paljon helpompaa käyttää LAG:ia, jossa on eksplisiittinen offset 2, esimerkiksi näin:

 SELECT custid, orderdate, orderid, val, LAG(val, 2) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS prevtwoval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;

Tämä kysely tuottaa seuraavan tulosteen:

 custid orderdate orderid val prevtwoval ------- ---------- -------- ---------- ----------- 1 2018-08-25 10643 814.50 NULL 1 2018-10-03 10692 878.00 NULL 1 2018-10-13 10702 330.00 814.50 1 2019-01-15 10835 845.80 878.00 1 2019-03-16 10952 471.20 330.00 1 2019-04-09 11011 933.50 845.80 2 2017-09-18 10308 88.80 NULL 2 2018-08-08 10625 479.75 NULL 2 2018-11-28 10759 320.00 88.80 2 2019-03-04 10926 514.40 479.75 3 2017-11-27 10365 403.20 NULL 3 2018-04-15 10507 749.06 NULL 3 2018-05-13 10535 1940.85 403.20 3 2018-06-19 10573 2082.00 749.06 3 2018-09-22 10677 813.37 1940.85 3 2018-09-25 10682 375.50 2082.00 3 2019-01-28 10856 660.00 813.37 ...

Ilmeisesti edellä esitetyn LAG-funktion ja FIRST_VALUE-funktion, jossa on kehys, joka alkaa kahdella PRECEDING-arvolla. Ensin mainitulla funktiolla, jos riviä ei ole halutussa offsetissa, saat oletusarvoisesti NULL:n. Jälkimmäisessä tapauksessa saat edelleen arvon ensimmäiseltä riviltä, joka on olemassa, eli osion ensimmäisen rivin arvon. Tarkastellaan seuraavaa kyselyä:

 SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS prevtwoval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;

Tämä kysely tuottaa seuraavan tulosteen:

 custid orderdate orderid val prevtwoval ------- ---------- -------- ---------- ----------- 1 2018-08-25 10643 814.50 814.50 1 2018-10-03 10692 878.00 814.50 1 2018-10-13 10702 330.00 814.50 1 2019-01-15 10835 845.80 878.00 1 2019-03-16 10952 471.20 330.00 1 2019-04-09 11011 933.50 845.80 2 2017-09-18 10308 88.80 88.80 2 2018-08-08 10625 479.75 88.80 2 2018-11-28 10759 320.00 88.80 2 2019-03-04 10926 514.40 479.75 3 2017-11-27 10365 403.20 403.20 3 2018-04-15 10507 749.06 403.20 3 2018-05-13 10535 1940.85 403.20 3 2018-06-19 10573 2082.00 749.06 3 2018-09-22 10677 813.37 1940.85 3 2018-09-25 10682 375.50 2082.00 3 2019-01-28 10856 660.00 813.37 ...

Huomaa, että tällä kertaa tulosteessa ei ole yhtään NULLia. FIRST_VALUEn ja LAST_VALUEn sisältävän kehyksen tukemisella on siis jotain arvoa. Varmista vain, että muistat parhaan käytännön, jonka mukaan näiden funktioiden kanssa on aina oltava yksiselitteinen kehysmäärittely ja käytettävä ROWS-vaihtoehtoa minimaalisella kehyksellä, joka sisältää etsimäsi rivin.

Johtopäätös

Tässä artikkelissa keskityttiin ikkunafunktioihin liittyviin virheisiin, sudenkuoppiin ja parhaisiin käytäntöihin. Muista, että sekä ikkuna-aggregaattifunktiot että FIRST_VALUE- ja LAST_VALUE-ikkunan offset-funktiot tukevat kehystä ja että jos määrittelet ikkunan järjestyslausekkeen, mutta et määrittele ikkunan kehysyksikköä ja siihen liittyvää laajuutta, saat oletusarvoisesti RANGE UNBOUNDED PRECEDING. Tästä aiheutuu suorituskykysyistä haittaa, kun kyselyä optimoidaan rivitilan operaattoreilla. LAST_VALUE-funktion kanssa tämä johtaa siihen, että arvot saadaan nykyiseltä riviltä osion viimeisen rivin sijasta. Muista ilmoittaa kehys selvästi ja suosia yleensä ROWS-vaihtoehtoa RANGE-vaihtoehdon sijaan. On hienoa nähdä suorituskyvyn paraneminen eräajotilassa olevan Window Aggregate -operaattorin avulla. Kun se on sovellettavissa, ainakin suorituskyvyn sudenkuoppa on eliminoitu.