Denna artikel är den fjärde delen i en serie om T-SQL buggar, fallgropar och bästa praxis. Tidigare har jag behandlat determinism, subqueries och joins. Fokus i den här månadens artikel är buggar, fallgropar och bästa praxis relaterade till fönsterfunktioner. Tack Erland Sommarskog, Aaron Bertrand, Alejandro Mesa, Umachandar Jayachandran (UC), Fabiano Neves Amorim, Milos Radivojevic, Simon Sabin, Adam Machanic, Thomas Grohser, Chan Ming Man och Paul White för att ni har bjudit på era idéer!

I mina exempel kommer jag att använda mig av en exempeldatabas kallad TSQLV5. Du kan hitta skriptet som skapar och fyller den här databasen här, och dess ER-diagram här.

Det finns två vanliga fallgropar som involverar fönsterfunktioner, som båda är resultatet av kontraintuitiva implicita standardinställningar som införs av SQL-standarden. En fallgrop har att göra med beräkningar av löpande totaler där man får en fönsterram med det implicita alternativet RANGE. En annan fallgrop är något besläktad, men har allvarligare konsekvenser, och involverar en implicit ramdefinition för funktionerna FIRST_VALUE och LAST_VALUE.

Fönsterram med implicit RANGE-alternativ

Vår första fallgrop involverar beräkningen av löpande summor med hjälp av en aggregerad fönsterfunktion, där du uttryckligen specificerar klausulen för fönsterordning, men inte uttryckligen specificerar fönsterramens enhet (ROWS eller RANGE) och den relaterade fönsterramens omfattning, t.ex, ROWS UNBOUNDED FÖREGÅENDE. Det implicita standardvärdet är kontraintuitivt och dess konsekvenser kan bli överraskande och smärtsamma.

För att demonstrera denna fallgrop använder jag en tabell som heter Transactions och som innehåller två miljoner bankkontotransaktioner med krediter (positiva värden) och debiter (negativa värden). Kör följande kod för att skapa tabellen Transactions och fylla den med provdata:

 SET NOCOUNT ON; USE TSQLV5; -- http://tsql.solidq.com/SampleDatabases/TSQLV5.zip DROP TABLE IF EXISTS dbo.Transactions; CREATE TABLE dbo.Transactions ( actid INT NOT NULL, tranid INT NOT NULL, val MONEY NOT NULL, CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid) -- creates POC index ); DECLARE @num_partitions AS INT = 100, @rows_per_partition AS INT = 20000; INSERT INTO dbo.Transactions WITH (TABLOCK) (actid, tranid, val) SELECT NP.n, RPP.n, (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM(NEWID())%5)) FROM dbo.GetNums(1, @num_partitions) AS NP CROSS JOIN dbo.GetNums(1, @rows_per_partition) AS RPP;

Vår fallgrop har både en logisk sida med ett potentiellt logiskt fel och en prestandasida med ett prestandapåslag. Prestandaavdraget är endast relevant när fönsterfunktionen optimeras med bearbetningsoperatorer i radläge. SQL Server 2016 introducerar operatorn Window Aggregate i batch-läge, som tar bort prestandabestraffdelen av fallgropen, men före SQL Server 2019 används den här operatorn endast om du har ett columnstore-index närvarande på data. SQL Server 2019 introducerar batch-läge på rowstore-stöd, så att du kan få batch-läge bearbetning även om det inte finns några columnstore-index närvarande på data. För att demonstrera prestandabortfallet med row-mode-bearbetning, om du kör kodproverna i den här artikeln på SQL Server 2019 eller senare, eller på Azure SQL Database, använder du följande kod för att ställa in databaskompatibilitetsnivån till 140 för att inte aktivera batch-mode on rowstore ännu:

 ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 140;

Använd följande kod för att aktivera tids- och I/O-statistik i sessionen:

 SET STATISTICS TIME, IO ON;

För att slippa vänta på att två miljoner rader ska skrivas ut i SSMS föreslår jag att du kör kodexemplen i det här avsnittet med alternativet Slösa bort resultat efter exekvering aktiverat (gå till Frågealternativ, Resultat, Rutnät och markera Slösa bort resultat efter exekvering).

Innan vi kommer till fallgropen kan du tänka på följande fråga (kalla den Fråga 1) som beräknar saldot på bankkontot efter varje transaktion genom att tillämpa en löpande totalsumma med hjälp av en fönsteraggregatfunktion med en uttrycklig ramspecifikation:

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

Planen för den här frågan, med användning av bearbetning i rakläge, visas i figur 1.

Figur 1: Plan för fråga 1, bearbetning i radläge

Planen hämtar data i förbestämd ordning från tabellens klustrade index. Sedan använder den operatörerna Segment och Sequence Project för att beräkna radnummer för att ta reda på vilka rader som hör till den aktuella radens ram. Sedan använder den operatörerna Segment, Window Spool och Stream Aggregate för att beräkna den aggregerade funktionen för fönstret. Operatören Window Spool används för att spola ramraderna som sedan måste aggregeras. Utan någon särskild optimering skulle planen ha varit tvungen att per rad skriva alla tillämpliga ramrader till spolen och sedan sammanställa dem. Detta skulle ha resulterat i en kvadratisk, eller N2, komplexitet. Den goda nyheten är att när ramen börjar med UNBOUNDED PRECEDING identifierar SQL Server fallet som ett snabbspår, där den helt enkelt tar föregående rads löpande totalsumma och lägger till den aktuella radens värde för att beräkna den aktuella radens löpande totalsumma, vilket resulterar i linjär skalning. I detta snabbspårsläge skriver planen endast två rader till spoolen per inmatningsrad – en med aggregatet och en med detaljen.

Fensterspoolen kan fysiskt implementeras på ett av två sätt. Antingen som en snabb spool i minnet som är särskilt utformad för fönsterfunktioner, eller som en långsam spool på disken, som i huvudsak är en temporär tabell i tempdb. Om antalet rader som måste skrivas till spoolen per underliggande rad kan överstiga 10 000, eller om SQL Server inte kan förutsäga antalet, kommer den att använda den långsammare spoolen på disken. I vår frågeplan har vi exakt två rader som skrivs till spoolen per underliggande rad, så SQL Server använder spoolen i minnet. Tyvärr finns det inget sätt att se i planen vilken typ av spool du får. Det finns två sätt att ta reda på detta. Det ena är att använda en utökad händelse som heter window_spool_ondisk_warning. Ett annat alternativ är att aktivera STATISTICS IO och kontrollera antalet logiska läsningar som rapporteras för en tabell som heter Worktable. Om antalet är större än noll betyder det att du har fått on-disk spool. Noll betyder att du fick spoolen i minnet. Här är I/O-statistiken för vår fråga:

Som du kan se har vi använt spoolen i minnet. Detta är i allmänhet fallet när du använder ramenheten ROWS i fönstret med UNBOUNDED PRECEDING som första avgränsare.

Här är tidsstatistiken för vår fråga:

Det tog ungefär 4,5 sekunder för denna fråga att slutföra på min maskin med resultaten bortkastade.

Nu kommer fångsten. Om du använder alternativet RANGE i stället för ROWS, med samma avgränsare, kan det vara en subtil skillnad i betydelse, men en stor skillnad i prestanda i radläge. Skillnaden i betydelse är bara relevant om du inte har total ordning, dvs. om du ordnar efter något som inte är unikt. Alternativet ROWS UNBOUNDED PRECEDING stannar med den aktuella raden, så vid oavgjordhet är beräkningen icke-deterministisk. Alternativet RANGE UNBOUNDED PRECEDING däremot tittar framåt i tiden från den aktuella raden och tar med oavgjort om det finns oavgjort. Det använder liknande logik som alternativet TOP WITH TIES. När du har total ordning, dvs. när du ordnar efter något unikt, finns det inga bindningar att inkludera, och därför blir ROWS och RANGE logiskt likvärdiga i ett sådant fall. Problemet är att när du använder RANGE använder SQL Server alltid spoolen på disken vid behandling i radläge, eftersom den vid behandling av en viss rad inte kan förutsäga hur många fler rader som kommer att inkluderas. Detta kan ge en allvarlig prestandaförlust.

Konsultera följande fråga (kalla den Fråga 2), som är densamma som Fråga 1, men som använder alternativet RANGE i stället för ROWS:

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

Planen för den här frågan visas i figur 2.

Figur 2: Plan för fråga 2, bearbetning i radläge

Fråga 2 är logiskt likvärdig med fråga 1 eftersom vi har total ordning, men eftersom den använder RANGE optimeras den med spolen på disken. Observera att i planen för fråga 2 ser Window Spool likadan ut som i planen för fråga 1, och de uppskattade kostnaderna är desamma.

Här är tids- och I/O-statistiken för utförandet av fråga 2:

Bemärk det stora antalet logiska läsningar mot Worktable, vilket tyder på att du fick spolen på disken. Körtiden är mer än fyra gånger längre än för Query 1.

Om du tänker att du i så fall helt enkelt undviker att använda RANGE-alternativet, såvida du inte verkligen behöver inkludera band, så är det bra tänkt. Problemet är att om du använder en fönsterfunktion som stöder en ram (aggregat, FIRST_VALUE, LAST_VALUE) med en uttrycklig ordningsklausul för fönstret, men utan att nämna fönsterramens enhet och dess tillhörande omfattning, får du RANGE UNBOUNDED PRECEDING som standard. Denna standard är dikterad av SQL-standarden, och standarden valde den eftersom den i allmänhet föredrar mer deterministiska alternativ som standardalternativ. Följande fråga (kalla den Fråga 3) är ett exempel som faller i denna fälla:

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

Oftast skriver folk så här och tror att de får ROWS UNBOUNDED PRECEDING som standard, utan att inse att de i själva verket får RANGE UNBOUNDED PRECEDING. Saken är den att eftersom funktionen använder total ordning får du samma resultat som med ROWS, så du kan inte se att det finns ett problem utifrån resultatet. Men prestandasiffrorna som du får är som för fråga 2. Jag ser folk falla i den här fällan hela tiden.

Den bästa metoden för att undvika det här problemet är att i de fall du använder en fönsterfunktion med en ram, vara explicit om fönsterramens enhet och dess omfattning, och i allmänhet föredra ROWS. Reservera användningen av RANGE endast för fall där ordningsföljden inte är unik och du behöver inkludera band.

Tänk på följande fråga som illustrerar ett fall där det finns en konceptuell skillnad mellan ROWS och RANGE:

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

 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 ...

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

 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 ...

 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;

 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 ...

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

Planen för denna fråga visas i figur 3.

Figur 3: Plan för fråga 1, bearbetning i batch-läge

Här är prestandastatistiken som jag fick för den här frågan:

Körningstiden sjönk till 1 sekund!

Kör fråga 2 med det explicita RANGE-alternativet igen:

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

Planen för denna fråga visas i Figur 4.

Figur 2: Plan för fråga 2, bearbetning i batch-läge

Här är prestandastatistiken som jag fick för den här frågan:

Prestationen är densamma som för fråga 1.

Kör fråga 3 igen, med det implicita alternativet RANGE:

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

Planen och prestandasiffrorna är förstås desamma som för fråga 2.

När du är klar kör du följande kod för att stänga av prestandastatistiken:

 SET STATISTICS TIME, IO OFF;

Genomför inte heller att stänga av alternativet Slösa bort resultat efter utförandet i SSMS.

Implicit ram med FIRST_VALUE och LAST_VALUE

Funktionerna FIRST_VALUE och LAST_VALUE är förskjutningsfönsterfunktioner som returnerar ett uttryck från den första respektive sista raden i fönsterramen. Det knepiga med dem är att ofta när folk använder dem för första gången inser de inte att de stöder en ram, utan tror snarare att de gäller för hela partitionen.

Tänk på följande försök att returnera orderinformation, plus värdena för kundens första och sista order:

 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;

Om du felaktigt tror att dessa funktioner fungerar på hela fönsterpartitionen, vilket många som använder dessa funktioner för första gången tror, förväntar du dig förstås att FIRST_VALUE ska återge ordervärdet för kundens första order och att LAST_VALUE ska återge ordervärdet för kundens sista order. I praktiken stöder dessa funktioner dock en ram. Som en påminnelse kan nämnas att när du i funktioner med stöd för en ram anger klausulen för fönsterordning men inte enheten för fönsterramen och dess tillhörande omfattning, får du som standard RANGE UNBOUNDED PRECEDING (Räckvidd utan begränsning). Med funktionen FIRST_VALUE får du det förväntade resultatet, men om din fråga optimeras med operatorer i radläge får du betala straffet för att använda spoolen på disken. Med funktionen LAST_VALUE är det ännu värre. Inte nog med att du får betala straffet för on-disk spool, men istället för att få värdet från den sista raden i partitionen får du värdet från den aktuella raden!

Här är resultatet av ovanstående fråga:

 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 ...

Oftast när folk ser ett sådant resultat för första gången tror de att SQL Server har en bugg. Men det har den naturligtvis inte; det är helt enkelt SQL-standardens standard. Det finns en bugg i frågan. När du inser att det finns en ram inblandad vill du vara tydlig med ramspecifikationen och använda den minsta ram som fångar den rad som du är ute efter. Se också till att du använder enheten ROWS. För att få fram den första raden i partitionen använder du alltså funktionen FIRST_VALUE med ramen ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. För att få fram den sista raden i partitionen använder du funktionen LAST_VALUE med ramen ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

Här är vår reviderade fråga med felet åtgärdat:

 SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS firstval, LAST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS lastval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;

Den här gången får du det korrekta resultatet:

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

Det man kan fråga sig är vad som var motivet för att standarden överhuvudtaget skulle stödja en ram med dessa funktioner. Om man tänker efter kommer man oftast att använda dem för att få ut något från de första eller sista raderna i partitionen. Om du behöver värdet från t.ex. två rader före den aktuella, i stället för att använda FIRST_VALUE med en ram som börjar med 2 PRECEDING, är det då inte mycket enklare att använda LAG med en explicit förskjutning på 2, så här:

 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;

Denna fråga genererar följande utdata:

 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 ...

Det finns tydligen en semantisk skillnad mellan ovanstående användning av LAG-funktionen och FIRST_VALUE med en ram som börjar med 2 PRECEDING. Med den förstnämnda funktionen får man som standard NULL om det inte finns någon rad i den önskade förskjutningen. Med den senare funktionen får du fortfarande värdet från den första raden som finns, dvs. värdet från den första raden i partitionen. Tänk på följande fråga:

 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;

Denna fråga genererar följande utdata:

 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 ...

Observera att det den här gången inte finns några NULLs i utdata. Det finns alltså ett visst värde i att stödja en ram med FIRST_VALUE och LAST_VALUE. Se bara till att du kommer ihåg den bästa metoden att alltid vara explicit om ramspecifikationen med dessa funktioner, och att använda ROWS-alternativet med den minimala ramen som innehåller den rad som du är ute efter.

Slutsats

Denna artikel fokuserade på buggar, fallgropar och bästa metoder relaterade till fönsterfunktioner. Kom ihåg att både fönsteraggregatfunktioner och FIRST_VALUE- och LAST_VALUE-funktionerna för fönsterförskjutning har stöd för en ram, och att om du anger klausulen för fönsterbeställning men inte anger fönsterramens enhet och dess tillhörande omfattning, får du som standard RANGE UNBOUNDED PRECEDING. Detta medför en prestandaförlust när frågan optimeras med operatorer i radläge. Med funktionen LAST_VALUE resulterar detta i att värdena hämtas från den aktuella raden i stället för från den sista raden i partitionen. Kom ihåg att vara explicit om ramen och att i allmänhet föredra ROWS-alternativet framför RANGE. Det är fantastiskt att se prestandaförbättringarna med operatorn Window Aggregate i batch-läge. När den är tillämplig är åtminstone prestandafällan eliminerad.