Dit artikel biedt een optimalisatieoplossing voor het probleem van het berekenen van magazijnsaldi met Transact SQL. Toegepast: verdeling van tabellen en gematerialiseerde weergaven.

Verklaring van het probleem

Het probleem moet worden opgelost op SQL Server 2014 Enterprise Edition (x64). Het bedrijf beschikt over veel magazijnen. Elk magazijn verwerkt dagelijks enkele duizenden zendingen en ontvangsten van producten. Er is een tabel met goederenbewegingen in het magazijn, ontvangst/kosten. Noodzaak om te implementeren:

Saldoberekening voor de geselecteerde datum en tijd (tot op het uur nauwkeurig) voor alle/alle magazijnen voor elk product. Voor analyses is het noodzakelijk om een ​​object (functie, tabel, weergave) te maken met behulp waarvan voor het geselecteerde datumbereik de gegevens van de oorspronkelijke tabel worden weergegeven en een extra berekeningskolom voor het geselecteerde datumbereik voor alle magazijnen en producten.

Er wordt verwacht dat deze berekeningen worden uitgevoerd volgens een schema met verschillende datumbereiken en binnen aanvaardbare tijden. Die. als het nodig is om een ​​tabel met saldi van het afgelopen uur of de afgelopen dag weer te geven, dan moet de uitvoeringstijd zo snel mogelijk zijn, en ook als het nodig is om dezelfde gegevens van de afgelopen 3 jaar weer te geven om vervolgens in de database te worden geladen. analytische database.

Technische details. De tafel zelf:

Tabel dbo.Omzet maken (id int identiteit primaire sleutel, dt datetime niet null, ProductID int niet null, StorehouseID int niet null, bewerking smallint niet null controle (bewerking in (-1,1)), -- +1 aankomst op de magazijn, -1 kosten uit magazijn Hoeveelheid numeriek (20,2) niet nul, kosten geld niet nul)

Dt - Datum en tijdstip van ontvangst/afschrijving naar/van het magazijn.
ProductID - Product
StorehouseID - magazijn
Werking - 2 waarden inkomend of uitgaand
Aantal - hoeveelheid product op voorraad. Het kan reëel zijn als het product niet in stukjes zit, maar bijvoorbeeld in kilogrammen.
Kosten - kosten van de productbatch.

Bestudeer het probleem

Laten we een voltooide tabel maken. Om het samen met mij te kunnen testen en de resultaten te kunnen zien, raad ik aan om de dbo.Omzettabel te maken en te vullen met een script:

Als object_id("dbo.Turnover","U") niet nul is, zet u tabel dbo.Turnover neer; ga met tijden als (selecteer 1 id unie, selecteer allemaal id+1 uit tijden waarbij id< 10*365*24*60 -- 10 лет * 365 дней * 24 часа * 60 минут = столько минут в 10 лет) , storehouse as (select 1 id union all select id+1 from storehouse where id < 100 -- количество складов) select identity(int,1,1) id, dateadd(minute, t.id, convert(datetime,"20060101",120)) dt, 1+abs(convert(int,convert(binary(4),newid()))%1000) ProductID, -- 1000 - количество разных продуктов s.id StorehouseID, case when abs(convert(int,convert(binary(4),newid()))%3) in (0,1) then 1 else -1 end Operation, -- какой то приход и расход, из случайных сделаем из 3х вариантов 2 приход 1 расход 1+abs(convert(int,convert(binary(4),newid()))%100) Quantity into dbo.Turnover from times t cross join storehouse s option(maxrecursion 0); go --- 15 min alter table dbo.Turnover alter column id int not null go alter table dbo.Turnover add constraint pk_turnover primary key (id) with(data_compression=page) go -- 6 min
Op mijn pc met een SSD-schijf draaide dit script ongeveer 22 minuten, en de tabelgrootte nam ongeveer 8 GB in beslag op de harde schijf. U kunt het aantal jaren en het aantal magazijnen verminderen om de tijd voor het maken en invullen van de tabel te verkorten. Maar ik raad aan om een ​​flink bedrag over te houden voor het evalueren van queryplannen, minimaal 1-2 gigabyte.

Laten we de gegevens groeperen tot een uur

Vervolgens moeten we de hoeveelheden per product in het magazijn groeperen voor de periode die we bestuderen, dit is een uur (tot een minuut, tot 15 minuten, per dag is mogelijk). (tot milliseconden heeft bijna niemand rapportage nodig). Voor vergelijkingen in de sessie (venster) waarin we onze vragen uitvoeren, zullen we het commando uitvoeren - set statistieken time on;. Vervolgens voeren we de queries zelf uit en kijken we naar de queryplannen:

Selecteer top(1000) convert(datetime,convert(varchar(13),dt,120)+":00",120) als dt, -- rond af op het dichtstbijzijnde uur ProductID, StorehouseID, som(Bewerking*Aantal) als Hoeveelheid van dbo. Omzetgroep op convert(datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID

Verzoekkosten - 12406
(verwerkte rijen: 1000)
Uptime van SQL Server:
CPU-tijd = 2096594 ms, verstreken tijd = 321797 ms.

Als we een resulterende zoekopdracht maken met een saldo, dat wordt beschouwd als een cumulatief totaal van onze hoeveelheid, dan zijn de zoekopdracht en het zoekopdrachtplan als volgt:

Selecteer top(1000) convert(datetime,convert(varchar(13),dt,120)+":00",120) als dt, -- rond af op het dichtstbijzijnde uur ProductID, StorehouseID, som(Bewerking*Aantal) als Hoeveelheid , sum (sum(Operation*Quantity)) over (partition by StorehouseID, ProductID order by convert(datetime,convert(varchar(13),dt,120)+":00",120)) als Saldo van dbo.Omzetgroep door convert (datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID

Aanvraagkosten - 19329
(verwerkte rijen: 1000)
Uptime van SQL Server:
CPU-tijd = 2413155 ms, verstreken tijd = 344631 ms.

Optimalisatie van groepering

Alles is hier vrij eenvoudig. De query zelf, zonder lopend totaal, kan worden geoptimaliseerd met een gematerialiseerde weergave (indexweergave). Om een ​​gerealiseerde weergave op te bouwen, mag wat wordt opgeteld geen NULL-waarde hebben. We sommeren som (Operatie*Hoeveelheid), of maken elk veld NIET NULL of voegen isnull/coalesce toe aan de expressie. Ik stel voor om een ​​gematerialiseerde visie te creëren.

Maak een weergave dbo.TurnoverHour met schemabinding als select convert(datetime,convert(varchar(13),dt,120)+":00",120) als dt, -- rond af op het dichtstbijzijnde uur ProductID, StorehouseID, sum(isnull( Bewerking* Hoeveelheid,0)) als Hoeveelheid, count_big(*) aantal uit dbo. Omzetgroep per convert(datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID go
En bouw er een geclusterde index op. In de index geven we de volgorde van de velden aan op dezelfde manier als bij groeperen (voor groeperen is de volgorde niet zo belangrijk, het is belangrijk dat alle groeperingsvelden in de index staan) en het cumulatieve totaal (de volgorde is belangrijk hier - eerst wat er opgedeeld is door, dan wat er in volgorde is):

Unieke geclusterde index uix_TurnoverHour maken op dbo.TurnoverHour (StorehouseID, ProductID, dt) met (data_compression=page) - 19 min.

Nu we de geclusterde index hebben gebouwd, kunnen we de query's opnieuw uitvoeren door de som-aggregatie te wijzigen zoals in de weergave:

Selecteer top(1000) convert(datetime,convert(varchar(13),dt,120)+":00",120) als dt, -- rond af op het dichtstbijzijnde uur ProductID, StorehouseID, sum(isnull(Operation*Quantity, 0) ) als Hoeveelheid uit dbo. Omzetgroep op convert(datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID selecteer top(1000) convert(datetime,convert(varchar (13 ),dt,120)+":00",120) as dt, -- rond af op het dichtstbijzijnde uur ProductID, StorehouseID, sum(isnull(Bewerking*Aantal,0)) als Hoeveelheid, sum(sum(isnull( Bewerking*Aantal, 0))) over (partitie op StorehouseID, ProductID volgorde op convert(datetime,convert(varchar(13),dt,120)+":00",120)) als Saldo van dbo. Omzetgroep op convert (datetime,convert (varchar(13),dt,120)+":00",120), ProductID, StorehouseID

Queryplannen werden:

Kosten 0,008

Kosten 0,01

Uptime van SQL Server:
CPU-tijd = 31 ms, verstreken tijd = 116 ms.
(verwerkte rijen: 1000)
Uptime van SQL Server:
CPU-tijd = 0 ms, verstreken tijd = 151 ms.

We zien dus dat de query bij een geïndexeerde weergave niet een tabel scant waarin de gegevens zijn gegroepeerd, maar een geclusterde index waarin alles al is gegroepeerd. En dienovereenkomstig werd de uitvoeringstijd teruggebracht van 321.797 milliseconden naar 116 ms, d.w.z. 2774 keer.

Dit zou het einde van onze optimalisatie kunnen zijn, ware het niet dat we vaak niet de hele tabel (view) nodig hebben, maar een deel ervan voor het geselecteerde bereik.

Tussenbalansen

Als gevolg hiervan hebben we een snelle uitvoering van de volgende query nodig:

Datumformaat instellen ymd; declare @start datetime = "2015-01-02", @finish datetime = "2015-01-03" selecteer * van (selecteer dt, StorehouseID, ProductId, Hoeveelheid, som (Hoeveelheid) over (partitie op StorehouseID, ProductID volgorde op dt) als Saldo van dbo.TurnoverHour with(noexpand) waarbij dt<= @finish) as tmp where dt >= @begin

Plankosten = 3103. Stel je eens voor wat er zou zijn gebeurd als ik niet aan de hand van de gematerialiseerde representatie was gegaan, maar van de tafel zelf.

Uitvoer van gematerialiseerde weergave- en balansgegevens voor elk product in het magazijn vanaf de datum afgerond op het dichtstbijzijnde uur. Om het saldo te berekenen, moet u vanaf het begin (vanaf het nulsaldo) alle hoeveelheden tot de opgegeven laatste datum (@finish) optellen en vervolgens de gegevens na de startparameter in de opgetelde resultaatset afsnijden.

Hier zullen uiteraard tussentijdse berekende saldi helpen. Bijvoorbeeld op de 1e van elke maand of op elke zondag. Als u dergelijke saldi heeft, komt de taak erop neer dat u de eerder berekende saldi moet optellen en het saldo niet vanaf het begin moet berekenen, maar vanaf de laatst berekende datum. Laten we voor experimenten en vergelijkingen een extra niet-geclusterde index op datum maken:

Maak index ix_dt op dbo.TurnoverHour (dt) include (Quantity) with(data_compression=page); --7 min En ons verzoek ziet er als volgt uit: set dateformat ymd; declare @start datetime = "2015-01-02", @finish datetime = "2015-01-03" declare @start_maand datetime = convert(datetime,convert(varchar(9),@start,120)+"1", 120) selecteer * uit (selecteer dt, StorehouseID, ProductId, Hoeveelheid, sum(Quantity) over (partition by StorehouseID, ProductID order by dt) als Saldo van dbo.TurnoverHour with(noexpand) waarbij dt tussen @start_month en @finish) as tmp waarbij dt >
Over het algemeen zal deze zoekopdracht, zelfs als deze een index op datum heeft die alle velden omvat die in de zoekopdracht betrokken zijn, onze geclusterde index en scan selecteren. En niet zoeken op datum en dan sorteren. Ik stel voor om de volgende twee zoekopdrachten uit te voeren en te vergelijken wat we hebben, en laten we dan analyseren welke beter is:

Datumformaat instellen ymd; declare @start datetime = "2015-01-02", @finish datetime = "2015-01-03" declare @start_maand datetime = convert(datetime,convert(varchar(9),@start,120)+"1", 120) selecteer * uit (selecteer dt, StorehouseID, ProductId, Hoeveelheid, sum(Quantity) over (partition by StorehouseID, ProductID order by dt) als Saldo van dbo.TurnoverHour with(noexpand) waarbij dt tussen @start_month en @finish) as tmp waarbij dt >= @start bestelling op StorehouseID, ProductID, dt select * from (selecteer dt, StorehouseID, ProductId, Hoeveelheid, sum(Quantity) over (partitie op StorehouseID, ProductID op volgorde van dt) als Saldo van dbo.TurnoverHour with( noexpand,index=ix_dt) waarbij dt tussen @start_maand en @finish) als tmp waarbij dt >= @start bestelling op StorehouseID, ProductID, dt

Uptime van SQL Server:
CPU-tijd = 33860 ms, verstreken tijd = 24247 ms.

(verwerkte rijen: 145608)

(verwerkte rijen: 1)

Uptime van SQL Server:
CPU-tijd = 6374 ms, verstreken tijd = 1718 ms.
CPU-tijd = 0 ms, verstreken tijd = 0 ms.

Vanaf het moment is te zien dat de datumindex veel sneller is. Maar de queryplannen zien er in vergelijking als volgt uit:

De kosten van de eerste zoekopdracht met een automatisch geselecteerde geclusterde index = 2752, maar de kosten met een index op zoekopdrachtdatum = 3119.

Hoe het ook zij, hier hebben we twee taken uit de index nodig: sorteren en bereikselectie. We kunnen dit probleem niet oplossen met slechts één index die tot onze beschikking staat. In dit voorbeeld is het gegevensbereik slechts 1 dag, maar als er sprake is van een langere periode, maar niet de gehele periode, bijvoorbeeld 2 maanden, dan zal zoeken op index zeker niet effectief zijn vanwege de sorteerkosten.

Hier, uit de zichtbare optimale oplossingen, zie ik:

1. Maak een berekend veld Jaar-Maand en maak een index (Jaar-Maand, andere velden van de geclusterde index). In de situatie waarbij dt tussen @start_maand en eindig is, vervangt u deze door Jaar-Maand=@maand, en past u daarna een filter toe op de vereiste datums.
2. Gefilterde indexen - de index zelf is als een clusterindex, maar gefilterd op datum, voor de gewenste maand. En maak zoveel van zulke indexen als we in totaal maandenlang hebben gedaan. Het idee is dicht bij een oplossing, maar als het bereik van voorwaarden uit twee gefilterde indexen bestaat, is een verbinding vereist en is sorteren in de toekomst nog steeds onvermijdelijk.
3. We verdelen de geclusterde index zo dat elke sectie slechts gegevens voor één maand bevat.

In het project heb ik uiteindelijk de derde optie gemaakt. Partitioneren van een geclusterde gematerialiseerde weergave-index. En als het monster over een periode van een maand wordt genomen, beïnvloedt de optimalisatie in feite slechts één sectie en scant deze zonder te sorteren. En het afsnijden van ongebruikte gegevens vindt plaats op het niveau van het afsnijden van ongebruikte secties. Als de zoekopdracht hier van de 10e tot de 20e is, voeren we geen exacte zoekopdracht uit naar deze datums, maar zoeken we naar gegevens van de 1e tot de laatste dag van de maand en scannen we dit bereik vervolgens in een gesorteerde index met filtering tijdens scannen volgens de ingestelde data.

We verdelen de geclusterde index van de weergave. Laten we eerst alle indexen uit de weergave verwijderen:

Index ix_dt op dbo.TurnoverHour verlagen; index uix_TurnoverHour op dbo.TurnoverHour neerzetten;
En laten we een functie- en partitieschema maken:

Datumformaat instellen ymd; maak een partitiefunctie pf_TurnoverHour(datetime) als bereikrecht voor waarden ("2006-01-01", "2006-02-01", "2006-03-01", "2006-04-01", "2006- 01-05", "01-06-2006", "01-07-2006", "01-08-2006", "01-09-2006", "01-10-2006", "11-2006 01", "01-12-2006", "01-01-2007", "01-02-2007", "01-03-2007", "01-04-2007", "01-05-2007" , "01-06-2007", "01-07-2007", "01-08-2007", "01-09-2007", "01-10-2007", "01-11-2007", " 01-12-2007", "01-01-2008", "01-02-2008", "01-03-2008", "01-04-2008", "01-05-2008", "01-2008- 01-06", "01-07-2008", "01-08-2008", "01-09-2008", "01-10-2008", "01-11-2008", "01-2008-12- 01", "01-01-2009", "01-02-2009", "01-03-2009", "01-04-2009", "01-05-2009", "01-06-2009" , "01-07-2009", "01-08-2009", "01-09-2009", "01-10-2009", "01-11-2009", "01-12-2009", " 01-01-2010", "01-02-2010", "01-03-2010", "01-04-2010", "01-05-2010", "01-06-2010", "01-2010- 01-07", "01-08-2010", "01-09-2010", "01-10-2010", "01-11-2010", "01-12-2010", "01-2011- 01", "01-02-2011", "01-03-2011", "01-04-2011", "01-05-2011", "01-06-2011", "01-07-2011" , "01-08-2011", "01-09-2011", "01-10-2011", "01-11-2011", "01-12-2011", "01-01-2012", " 01-02-2012", "01-03-2012", "01-04-2012", "01-05-2012", "01-06-2012", "01-07-2012", "01-2012- 01-08", "01-09-2012", "01-10-2012", "01-11-2012", "01-12-2012", "01-01-2013", "02-2013 01", "01-03-2013", "01-04-2013", "01-05-2013", "01-06-2013", "01-07-2013", "01-08-2013" , "01-09-2013", "01-10-2013", "01-11-2013", "01-12-2013", "01-01-2014", "01-02-2014", " 01-03-2014", "01-04-2014", "01-05-2014", "01-06-2014", "01-07-2014", "01-08-2014", "01-08-2014", 01-09", "01-10-2014", "01-11-2014", "01-12-2014", "01-01-2015", "01-02-2015", "03-2015 01", "01-04-2015", "01-05-2015", "01-06-2015", "01-07-2015", "01-08-2015", "01-09-2015" , "01-10-2015", "01-11-2015", "01-12-2015", "01-01-2016", "01-02-2016", "01-03-2016", " 01-04-2016", "01-05-2016", "01-06-2016", "01-07-2016", "01-08-2016", "01-09-2016", "01-09-2016", 10-01", "2016-11-01", "2016-12-01", "2017-01-01", "2017-02-01", "2017-03-01", "2017-04- 01", "01-05-2017", "01-06-2017", "01-07-2017", "01-08-2017", "01-09-2017", "01-10-2017" , "01-11-2017", "01-12-2017", "01-01-2018", "01-02-2018", "01-03-2018", "01-04-2018", " 01-05-2018", "01-06-2018", "01-07-2018", "01-08-2018", "01-09-2018", "01-10-2018", "01-08-2018", 11-01", "2018-12-01", "2019-01-01", "2019-02-01", "2019-03-01", "2019-04-01", "2019-05- 01", "01-06-2019", "01-07-2019", "01-08-2019", "01-09-2019", "01-10-2019", "01-11-2019" , "01-12-2019"); ga een partitieschema ps_TurnoverHour maken als partitie pf_TurnoverHour all to (); go Nou, de geclusterde index die we al kennen, zit alleen in het gemaakte partitieschema: maak een unieke geclusterde index uix_TurnoverHour op dbo. TurnoverHour (StorehouseID, ProductID, dt) met (data_compression=page) op ps_TurnoverHour(dt); --- 19 min En laten we nu eens kijken wat we hebben. Het verzoek zelf: set dateformat ymd; declare @start datetime = "2015-01-02", @finish datetime = "2015-01-03" declare @start_maand datetime = convert(datetime,convert(varchar(9),@start,120)+"1", 120) selecteer * uit (selecteer dt, StorehouseID, ProductId, Hoeveelheid, sum(Quantity) over (partition by StorehouseID, ProductID order by dt) als Saldo van dbo.TurnoverHour with(noexpand) waarbij dt tussen @start_month en @finish) as tmp waarbij dt >= @startvolgorde op StorehouseID, ProductID, dt-optie (opnieuw compileren);

Uptime van SQL Server:
CPU-tijd = 7860 ms, verstreken tijd = 1725 ms.
Parseer- en compilatietijd van SQL Server:
CPU-tijd = 0 ms, verstreken tijd = 0 ms.
Kosten voor queryplan = 9,4

In wezen worden gegevens in één sectie vrij snel geselecteerd en gescand tegen een geclusterde index. Wat hier moet worden toegevoegd, is dat wanneer een query wordt geparametriseerd, het onaangename effect van het snuiven van parameters optreedt, dat wordt verholpen door optie (opnieuw compileren).

De SQL 2003-syntaxis wordt op alle platforms ondersteund.

VLOER (uitdrukking)

Als u een positief getal aan een functie doorgeeft, verwijdert de functie alles na de komma.

SELECTEER VERDIEPING (100.1) UIT dubbel;

Houd er echter rekening mee dat voor negatieve getallen het naar beneden afronden gelijk staat aan het verhogen van de absolute waarde.

SELECTEER VERDIEPING (-100.1) UIT dubbel;

Om het tegenovergestelde effect van de FLOOR-functie te verkrijgen, gebruikt u de CEIL-functie.

LN

De functie LN retourneert de natuurlijke logaritme van een getal, dat wil zeggen de macht waartoe de wiskundige constante e (ongeveer 2,718281) moet worden verhoogd om het gegeven getal te verkrijgen.

SQL 2003-syntaxis

LN (expressie)

DB2, Oracle, PostgreSQL

De DB2-, Oracle- en PostgreSQL-platforms ondersteunen de LN-functie in de syntaxis van SQL 2003. DB2 en PostgreSQL ondersteunen ook de LOG-functie als synoniem voor LN.

MySQL en SQLServer

MySQL en SQL Server hebben hun eigen functie voor het berekenen van de natuurlijke logaritme - LOG.

LOG (expressie)

Het volgende Oracle-voorbeeld berekent de natuurlijke logaritme van een getal dat een wiskundige constante benadert.

SELECTEER LN(2.718281) VAN dubbel;

Om de tegenovergestelde handeling uit te voeren, gebruikt u de EXP-functie.

MOD

De MOD-functie retourneert de rest wanneer het deeltal wordt gedeeld door de deler. Alle platforms ondersteunen de SQL 2003-standaard MOD-instructiesyntaxis.

SQL 2003-syntaxis

MOD (dividend, deler)

De standaard MOD-functie is ontworpen om de rest te verkrijgen bij het delen van het deeltal door de deler. Als de deler nul is, wordt het deeltal teruggegeven.

Hieronder ziet u hoe u de MOD-functie in een SELECT-instructie kunt gebruiken.

SELECTEER MOD(12, 5) UIT NUMMERS 2;

POSITIE

De functie POSITION retourneert een geheel getal dat de startpositie van de tekenreeks in de zoekreeks aangeeft.

SQL 2003-syntaxis

POSITIE(regel 1 IN regel2)

De standaard POSITION-functie is ontworpen om de positie te verkrijgen van de eerste keer dat een bepaalde string (string!) voorkomt in een zoekstring (string!). De functie retourneert 0 als de tekenreeks! komt niet voor in de string!, en NULL - als een van de argumenten NULL is.

DB2

DB2 heeft een gelijkwaardige functie, POSSTR.

MySQL

Het MySQL-platform ondersteunt de POSITION-functie volgens de SQL 2003-standaard.

STROOM

De POWER-functie wordt gebruikt om een ​​getal tot een bepaalde macht te verheffen.

SQL 2003-syntaxis

VERMOGEN (basis, indicator)

Het resultaat van het uitvoeren van deze functie is de basis verhoogd tot de macht bepaald door de indicator. Als het grondtal negatief is, moet de exponent een geheel getal zijn.

DB2, Oracle, PostgreSQL en SQL Server

Al deze leveranciers ondersteunen de syntaxis van SQL 2003.

Orakel

Oracle heeft een gelijkwaardige INSTR-functie.

PostgreSQL

Het PostgreSQL-platform ondersteunt de POSITION-functie volgens de SQL 2003-standaard.

MySQL

Het MySQL-platform ondersteunt deze functionaliteit, niet het POW-trefwoord.

P0W (basis, indicator)

Het verheffen van een positief getal tot een macht ligt voor de hand.

SELECTEER VERMOGEN (10.3) VAN dubbel;

Elk getal tot de macht 0 is gelijk aan 1.

SELECTEER VERMOGEN (0,0) UIT dubbel;

Een negatieve exponent verplaatst de komma naar links.

KIES VERMOGEN(10, -3) UIT dubbel;

SOORT

De functie SQRT retourneert de vierkantswortel van een getal.

SQL 2003-syntaxis

Alle platforms ondersteunen de syntaxis van SQL 2003.

SORTEREN (expressie)

SELECTEER SQRT(100) UIT dubbel;

BREEDTE EMMER

De WIDTH BUCKET-functie wijst waarden toe aan de kolommen van een histogram met gelijke breedte.

SQL 2003-syntaxis

In de volgende syntaxis vertegenwoordigt de expressie een waarde die is toegewezen aan een histogramkolom. Normaal gesproken is de expressie gebaseerd op een of meer tabelkolommen die door de query worden geretourneerd.

BREEDTE BUCKET(expressie, min, max, histogrambalken)

De parameter histogramkolommen toont het aantal gemaakte histogramkolommen, variërend van min tot max. De waarde van de min-parameter is opgenomen in het bereik, maar de waarde van de max-parameter is niet inbegrepen. De waarde van de uitdrukking wordt toegewezen aan een van de histogramkolommen, waarna de functie het nummer van de overeenkomstige histogramkolom retourneert. Als de expressie niet binnen het opgegeven kolombereik valt, retourneert de functie 0 of max + 1, afhankelijk van of de expressie kleiner is dan min of groter dan of gelijk is aan max.

Het volgende voorbeeld verdeelt gehele waarden van 1 tot 10 tussen twee histogramkolommen.

Het volgende voorbeeld is interessanter. De 11 waarden van 1 tot 10 zijn verdeeld over de drie kolommen van het histogram om het verschil te illustreren tussen de minimale waarde, die binnen het bereik valt, en de maximale waarde, die niet in het bereik valt.

SELECT x, WIDTH_BUCKET(x, 1.10.3) FROM draaipunt;

Besteed speciale aandacht aan de resultaten cX =, X= 9,9 en X- 10. De invoerwaarde van de parameter, dat wil zeggen in dit voorbeeld - 1, valt in de eerste kolom en geeft de ondergrens van het bereik aan, aangezien kolom nr. 1 wordt gedefinieerd als x >= min. De maximale invoerwaarde is echter niet opgenomen in de maximale kolom. In dit voorbeeld valt het getal 10 in de overloopkolom, genummerd max + 1. De waarde 9,9 valt in kolom nummer 3, en dit illustreert de regel dat de bovengrens van het bereik wordt gedefinieerd als x< max.

Alle wiskundige functies retourneren NULL als er een fout optreedt.

Unair min. Verandert het teken van het argument: mysql> SELECT - 2; 693147 mysql> SELECTEER LOGBOEK(-2); 9233482386203 mysql> SELECTEER RAND(20);

Als de retourwaarde wordt gebruikt in een context van gehele getallen (INTEGER), of als alle argumenten gehele getallen zijn, worden ze vergeleken als gehele getallen.

Als de retourwaarde wordt gebruikt in de context van reële getallen (REAL) of als alle argumenten reële getallen zijn, worden ze vergeleken als getallen van het type REAL.

Als een van de argumenten een hoofdlettergevoelige tekenreeks is, worden de argumenten op een hoofdlettergevoelige manier vergeleken.

Anders worden de argumenten vergeleken als niet-hoofdlettergevoelige tekenreeksen.

mysql> SELECTEER MINST(2,0);

Vóór SQL Server 2012 waren set-gebaseerde oplossingen die werden gebruikt om lopende totalen te berekenen extreem arbeidsintensief. Mensen neigden dus naar iteratieve oplossingen, die langzaam waren, maar in sommige situaties nog steeds sneller dan op sets gebaseerde oplossingen. Met uitgebreide ondersteuning voor vensterfuncties in SQL Server 2012 kunnen lopende totalen worden berekend met behulp van eenvoudige set-gebaseerde code die veel beter presteert dan oudere op T-SQL gebaseerde oplossingen, zowel set-gebaseerd als iteratief. Ik zou de nieuwe oplossing kunnen laten zien en door kunnen gaan naar de volgende sectie; maar om je te helpen de reikwijdte van de verandering echt te begrijpen, zal ik de oude manieren beschrijven en hun prestaties vergelijken met de nieuwe aanpak. Uiteraard bent u vrij om alleen het eerste deel, waarin de nieuwe aanpak wordt beschreven, te lezen en de rest van het artikel over te slaan.

Ik gebruik rekeningsaldi om verschillende oplossingen te demonstreren. Hier is de code die de tabel Transacties maakt en vult met een kleine hoeveelheid testgegevens:

ZET GEEN AANTAL IN; GEBRUIK TSQL2012; ALS OBJECT_ID("dbo.Transactions", "U") GEEN NULL DROP TABLE is dbo.Transactions; MAAK TABEL dbo.Transactions (actid INT NOT NULL, -- partitiekolom tranid INT NOT NULL, -- bestelkolom val MONEY NOT NULL, -- meet CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid)); GO - kleine testgegevensset INSERT INTO dbo. Transacties (actid, tranid, val) WAARDEN (1, 1, 4,00), (1, 2, -2,00), (1, 3, 5,00), (1, 4, 2,00), (1, 5, 1,00), (1, 6, 3,00), (1, 7, -4,00), (1, 8, -1,00), (1, 9, -2,00), (1, 10 , -3,00), (2, 1, 2,00), (2, 2, 1,00), (2, 3, 5,00), (2, 4, 1,00), (2, 5, -5,00), (2, 6 , 4,00), (2, 7, 2,00), (2, 8, -4,00), (2, 9, -5,00), (2, 10, 4,00), (3, 1, -3,00), (3, 2, 3,00), (3, 3, -2,00), (3, 4, 1,00), (3, 5, 4,00), (3, 6, -1,00), (3, 7, 5,00), (3, 8, 3,00), (3, 9, 5,00), (3, 10, -3,00);

Elke rij van de tabel vertegenwoordigt een banktransactie op een rekening. Stortingen worden gemarkeerd als transacties met een positieve waarde in de val-kolom, en opnames worden gemarkeerd als een negatieve transactiewaarde. Onze taak is om het rekeningsaldo op elk tijdstip te berekenen door de transactiebedragen in de val-rij op te tellen, gesorteerd op de tranid-kolom, en dit moet voor elke rekening afzonderlijk worden gedaan. Het gewenste resultaat zou er als volgt uit moeten zien:

Om beide oplossingen te testen zijn meer gegevens nodig. Dit kan met een query als deze:

DECLARE @num_partitions AS INT = 10, @rows_per_partition AS INT = 10000; AFKORTE TABEL dbo.Transacties; INSERT INTO dbo.Transacties MET (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;

U kunt uw invoer instellen om het aantal secties (accounts) en rijen (transacties) in een sectie te wijzigen.

Set-gebaseerde oplossing met behulp van vensterfuncties

Ik begin met een set-gebaseerde oplossing die gebruikmaakt van de SUM-vensteraggregatiefunctie. De definitie van een venster is hier vrij duidelijk: je moet het venster indelen op actid, ordenen op tranid en een filter gebruiken om de lijnen in het frame te selecteren, van de onderste (ONBOUNDED PRECEDING) tot de huidige. Hier is het bijbehorende verzoek:

SELECTEER actid, tranid, val, SUM(val) OVER(VERDELING DOOR actid VOLGORDE DOOR tranid RIJEN TUSSEN ONGEBONDEN VOORGAANDE EN HUIDIGE RIJ) ALS saldo VAN dbo.Transacties;

Deze code is niet alleen eenvoudig en duidelijk, maar ook snel. Het plan voor deze query wordt weergegeven in de afbeelding:

De tabel heeft een geclusterde index die voldoet aan de POC-vereisten en kan worden gebruikt door vensterfuncties. Concreet is de indexsleutellijst gebaseerd op een partitie-element (actid), gevolgd door een ordeningselement (tranid), en omvat de index ook alle andere kolommen in de query (val) om dekking te bieden. Het plan bevat een geordende scan, gevolgd door de berekening van het regelnummer voor interne behoeften en vervolgens de vensteraggregaat. Omdat er een POC-index is, hoeft de optimizer geen sorteeroperator aan het plan toe te voegen. Dit is een zeer effectief plan. Bovendien schaalt het lineair. Later, als ik de resultaten van prestatievergelijkingen laat zien, zul je zien hoeveel effectiever deze methode is vergeleken met oudere oplossingen.

Vóór SQL Server 2012 werden subquery's of joins gebruikt. Wanneer u een subquery gebruikt, worden lopende totalen berekend door alle rijen te filteren met dezelfde actid-waarde als de buitenste rij en een tranid-waarde die kleiner is dan of gelijk is aan de waarde in de buitenste rij. Aggregatie wordt vervolgens toegepast op de gefilterde rijen. Hier is het bijbehorende verzoek:

Een soortgelijke aanpak kan worden geïmplementeerd met behulp van verbindingen. Hetzelfde predikaat wordt gebruikt als in de WHERE-clausule van de subquery in de ON-clausule van de join. In dit geval vindt u voor de N-de transactie van dezelfde rekening A in de instantie die is aangeduid als T1, N overeenkomsten in de instantie T2, waarbij de transactienummers lopen van 1 tot N. Als resultaat van de overeenkomsten zijn de rijen in T1 herhaald, dus u moet de rijen over alle elementen uit T1 groeperen om informatie over de huidige transactie te krijgen en aggregatie toe te passen op het val-attribuut uit T2 om het lopende totaal te berekenen. Het voltooide verzoek ziet er ongeveer zo uit:

SELECTEER T1.actid, T1.tranid, T1.val, SUM(T2.val) ALS saldo VAN dbo.Transacties AS T1 JOIN dbo.Transacties AS T2 OP T2.actid = T1.actid EN T2.tranid<= T1.tranid GROUP BY T1.actid, T1.tranid, T1.val;

In onderstaande figuur zijn de plannen voor beide oplossingen weergegeven:

Houd er rekening mee dat in beide gevallen een volledige scan van de geclusterde index wordt uitgevoerd op exemplaar T1. Vervolgens is er voor elke rij in het plan een zoekbewerking in de index van het begin van de lopende rekeningsectie op de eindpagina van de index, die alle transacties leest waarin T2.tranid kleiner is dan of gelijk is aan T1. tranide. Het punt waar rijaggregatie plaatsvindt, verschilt enigszins in de plannen, maar het aantal gelezen rijen is hetzelfde.

Om te begrijpen naar hoeveel rijen wordt gekeken, moet u rekening houden met het aantal gegevenselementen. Laat p het aantal secties (rekeningen) zijn, en r het aantal rijen in de sectie (transactie). Dan is het aantal rijen in de tabel ongeveer gelijk aan p*r, als we ervan uitgaan dat transacties gelijkmatig over de rekeningen zijn verdeeld. De bovenstaande scan omvat dus p*r-rijen. Maar wat ons het meest interesseert, is wat er gebeurt in de Nested Loops-iterator.

In elke sectie voorziet het plan in het lezen van 1 + 2 + ... + r rijen, wat in totaal (r + r*2) / 2 is. Het totale aantal verwerkte rijen in de plannen is p*r + p* (r + r2) / 2. Dit betekent dat het aantal bewerkingen in het plan kwadratisch toeneemt met toenemende sectiegrootte, dat wil zeggen dat als u de sectiegrootte met f keer vergroot, de hoeveelheid werk met ongeveer f 2 keer toeneemt. Dit is slecht. 100 regels komen bijvoorbeeld overeen met 10.000 regels, en duizend regels komen overeen met een miljoen, enz. Simpel gezegd leidt dit tot een aanzienlijke vertraging in de uitvoering van zoekopdrachten met een vrij grote sectiegrootte, omdat de kwadratische functie erg snel groeit. Dergelijke oplossingen werken bevredigend met enkele tientallen lijnen per sectie, maar niet meer.

Cursor-oplossingen

Cursorgebaseerde oplossingen worden frontaal geïmplementeerd. Er wordt een cursor gedeclareerd op basis van een query die de gegevens sorteert op actid en tranid. Hierna wordt een iteratieve passage door de cursorrecords uitgevoerd. Wanneer een nieuw account wordt gedetecteerd, wordt de variabele die het aggregaat bevat, opnieuw ingesteld. Bij elke iteratie wordt het bedrag van de nieuwe transactie aan de variabele toegevoegd, waarna de rij wordt opgeslagen in een tabelvariabele met informatie over de huidige transactie plus de huidige waarde van het lopende totaal. Na een iteratieve doorgang wordt het resultaat van de tabelvariabele geretourneerd. Hier is de code voor de voltooide oplossing:

DECLARE @Resultaat ALS TABEL (actid INT, tranid INT, val MONEY, saldo MONEY); VERKLAREN @actid ALS INT, @prvactid ALS INT, @tranid ALS INT, @val ALS GELD, @balance ALS GELD; DECLARE C CURSOR FAST_FORWARD VOOR SELECT actid, tranid, val FROM dbo. Transacties ORDER OP actid, tranid; OPEN C HALEN VOLGENDE VAN C NAAR @actid, @tranid, @val; SELECTEER @prvactid = @actid, @balance = 0; WHILE @@fetch_status = 0 BEGIN ALS @actid<>@prvactid SELECT @prvactid = @actid, @balance = 0;

SET @balans = @balans + @val;

INSERT IN @Result WAARDEN(@actid, @tranid, @val, @balance);

HALEN VOLGENDE VAN C NAAR @actid, @tranid, @val; EIND DICHT C; VERWIJDERING C; SELECTEER * UIT @Resultaat;

Het queryplan met behulp van een cursor wordt weergegeven in de afbeelding:

Dit plan schaalt lineair omdat de gegevens uit de index slechts één keer in een specifieke volgorde worden gescand. Bovendien heeft elke bewerking om een ​​rij van een cursor op te halen ongeveer dezelfde kosten per rij. Als we aannemen dat de belasting die ontstaat door het verwerken van één cursorregel gelijk is aan g, kunnen de kosten van deze oplossing worden geschat als p*r + p*r*g (zoals u zich herinnert, is p het aantal secties, en r is het aantal rijen in de sectie). Dus als u het aantal rijen per sectie met f keer verhoogt, zal de belasting van het systeem p*r*f + p*r*f*g zijn, dat wil zeggen dat het lineair zal groeien. De verwerkingskosten per rij zijn hoog, maar vanwege de lineaire aard van het schalen zal deze oplossing vanaf een bepaalde partitiegrootte een betere schaalbaarheid vertonen dan geneste op query's en joins gebaseerde oplossingen vanwege de kwadratische schaalbaarheid van deze oplossingen. Prestatiemetingen die ik heb gedaan laten zien dat het getal waarbij de cursoroplossing sneller is een paar honderd rijen per sectie bedraagt. CLR (Common Language Runtime) is in wezen een vorm van oplossing met behulp van een cursor. Het verschil is dat u in plaats van een T-SQL-cursor te gebruiken, die veel bronnen verspilt om de volgende rij op te halen en te herhalen, .NET SQLDataReader en .NET-iteraties gebruikt, die veel sneller zijn. Een van de kenmerken van de CLR die deze optie sneller maakt, is dat de resulterende rij niet nodig is in een tijdelijke tabel: de resultaten worden rechtstreeks naar het aanroepende proces verzonden. De logica van een op CLR gebaseerde oplossing is vergelijkbaar met die van een cursor- en T-SQL-oplossing. Hier is de C#-code die de opgeslagen procedure voor het oplossen definieert:

Systeem gebruiken; met behulp van System.Data; met behulp van System.Data.SqlClient; met behulp van System.Data.SqlTypes; met behulp van Microsoft.SqlServer.Server; openbare gedeeltelijke klasse StoredProcedures ( public static void AccountBalances() ( met behulp van (SqlConnection conn = new SqlConnection("context connectie=true;")) ( SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = @" " + "SELECT actid, tranid, val " + "VAN dbo.Transactions " + "ORDER BY actid, tranid;"; SqlMetaData columns = new SqlMetaData; columns = new SqlMetaData("actid" , SqlDbType.Int); columns = new SqlMetaData("tranid" , SqlDbType.Int); kolommen = new SqlMetaData("val", SqlDbType.Money); kolommen = new SqlMetaData("balance", SqlDataRecord(kolommen); Pipe.SendResultsStart(record); conn.Open( ); SqlInt32 actid = reader.GetSqlInt32(0) ; SqlMoney val = reader.GetSqlMoney(2); if (actid == prvactid) (saldo += val;) else (saldo = val;) prvactid = actid; (0, lezer.GetSqlInt32(0));

record.SetSqlInt32(1, lezer.GetSqlInt32(1));

Als u de assembly AccountBalances een naam hebt gegeven en het pad naar het assembly-bestand "C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll" is, kunt u de assembly in de database laden en de opgeslagen procedure registreren met de volgende code:

MONTAGE AccountBalances MAKEN VANUIT "C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll"; GA PROCEDURE MAKEN dbo.AccountBalances ALS EXTERNE NAAM AccountBalances.StoredProcedures.AccountBalances;

Nadat u de assembly heeft geïmplementeerd en de procedure heeft geregistreerd, kunt u deze uitvoeren met de volgende code:

EXEC dbo.AccountBalances;

Zoals ik al zei, is SQLDataReader gewoon een andere vorm van cursor, maar deze versie kost aanzienlijk minder kosten voor het lezen van rijen dan het gebruik van een traditionele cursor in T-SQL. Iteraties zijn ook veel sneller in .NET dan in T-SQL. Op CLR gebaseerde oplossingen schalen dus ook lineair. Uit tests is gebleken dat de prestaties van deze oplossing hoger worden dan de prestaties van oplossingen die gebruik maken van subquery's en joins wanneer het aantal rijen in een sectie groter is dan 15.

Wanneer u klaar bent, moet u de volgende opruimcode uitvoeren:

DROP-PROCEDURE dbo.AccountBalances; DROP ASSEMBLY AccountBalances;

Geneste iteraties

Tot nu toe heb ik iteratieve en set-gebaseerde oplossingen getoond. De volgende oplossing is gebaseerd op geneste iteraties, wat een hybride is van iteratieve en set-gebaseerde benaderingen. Het idee is om eerst de rijen uit de brontabel (in ons geval bankrekeningen) naar een tijdelijke tabel te kopiëren, samen met een nieuw attribuut genaamd rownum, dat wordt berekend met behulp van de functie ROW_NUMBER. Regelnummers worden gepartitioneerd op actid en geordend op tranid, dus de eerste transactie op elke bankrekening krijgt nummer 1, de tweede transactie krijgt nummer 2, enzovoort. Vervolgens wordt op de tijdelijke tabel een geclusterde index gemaakt met een lijst met sleutels (rownum, actid). Een recursieve CTE-expressie of een speciaal vervaardigde lus wordt vervolgens gebruikt om één rij per iteratie voor alle accounts te verwerken. Het lopende totaal wordt vervolgens berekend door de waarde die bij de huidige rij hoort, op te tellen bij de waarde die bij de vorige rij hoort. Hier is een implementatie van deze logica met behulp van een recursieve CTE:

SELECT actid, tranid, val, ROW_NUMBER() OVER(PARTITION BY actid ORDER BY tranid) AS rownum INTO #Transactions FROM dbo.Transactions; MAAK EEN UNIEKE GECLUSTERDE INDEX idx_rownum_actid OP #Transactions(rownum, actid); MET C AS (SELECTEER 1 AS rownum, actid, tranid, val, val AS sumqty FROM #Transactions WAAR rownum = 1 UNION ALLES SELECTEER PRV.rownum + 1, PRV.actid, CUR.tranid, CUR.val, PRV.sumqty + CUR.val VAN C AS PRV JOIN #Transacties ALS CUR OP CUR.rownum = PRV.rownum + 1 EN CUR.actid = PRV.actid) SELECTEER actid, tranid, val, sumqty FROM C OPTIE (MAXRECURSION 0); DROP TABLE #Transacties;

En dit is een implementatie die een expliciete lus gebruikt:

SELECT ROW_NUMBER() OVER(PARTITION BY actid ORDER BY tranid) AS rownum, actid, tranid, val, CAST(val AS BIGINT) AS sumqty INTO #Transactions FROM dbo.Transactions; MAAK EEN UNIEKE GECLUSTERDE INDEX idx_rownum_actid OP #Transactions(rownum, actid); VERKLAREN @rownum ALS INT; SET @rownummer = 1; TERWIJL 1 = 1 BEGINNEN MET INSTELLEN @rownum = @rownum + 1;

UPDATE CUR SET sumqty = PRV.sumqty + CUR.val VAN #Transacties ALS CUR JOIN #Transacties ALS PRV OP CUR.rownum = @rownum EN PRV.rownum = @rownum - 1 EN CUR.actid = PRV.actid;

ALS @@rijtelling = 0 BREAK; END SELECT actid, tranid, val, sumqty FROM #Transactions; DROP TABLE #Transacties;

Deze oplossing levert goede prestaties als er een groot aantal partities is met een klein aantal rijen per partitie. Dan is het aantal iteraties klein en wordt het grootste deel van het werk gedaan door het setgebaseerde deel van de oplossing, dat de rijen die bij het ene rijnummer horen, verbindt met de rijen die bij het vorige rijnummer horen.

Deze methode gebruikt een UPDATE-instructie met variabelen. De UPDATE-instructie kan expressies toewijzen aan variabelen op basis van de waarde van een kolom, en kan ook waarden in kolommen toewijzen aan een expressie met een variabele. De oplossing begint met het maken van een tijdelijke tabel met de naam Transactions met de attributen actid, tranid, val en balance en een geclusterde index met een lijst met sleutels (actid, tranid). Vervolgens wordt de tijdelijke tabel gevuld met alle rijen uit de brontransactiesdatabase en wordt de waarde 0,00 ingevoerd in de saldokolom van alle rijen. Vervolgens wordt een UPDATE-instructie aangeroepen met de variabelen die aan de tijdelijke tabel zijn gekoppeld om de lopende totalen te berekenen en de berekende waarde in de saldokolom in te voegen.

De variabelen @prevaccount en @prevbalance worden gebruikt en de waarde in de saldokolom wordt berekend met behulp van de volgende expressie:

SET @prevbalance = saldo = CASE WHEN actid = @prevaccount THEN @prevbalance + waarde ELSE waarde END

De CASE-expressie controleert of de huidige en vorige account-ID's hetzelfde zijn en retourneert, als dat het geval is, de som van de vorige en huidige waarden in de saldokolom. Als de account-ID's verschillend zijn, wordt het huidige transactiebedrag geretourneerd. Vervolgens wordt het resultaat van de CASE-expressie ingevoegd in de balance-kolom en toegewezen aan de @prevbalance-variabele. In een aparte expressie wordt aan de variabele ©prevaccount de ID van het huidige account toegewezen.

Na de UPDATE-instructie presenteert de oplossing de rijen uit de tijdelijke tabel en verwijdert de laatste. Hier is de code voor de voltooide oplossing:

MAAK TABEL #Transacties (actid INT, tranid INT, val MONEY, saldo MONEY); MAAK EEN GECLUSTERDE INDEX idx_actid_tranid ON #Transactions(actid, tranid); INSERT IN #Transactions WITH (TABLOCK) (actid, tranid, val, balance) SELECT actid, tranid, val, 0.00 VAN dbo. Transacties ORDER OP actid, tranid; VERKLAREN @prevaccount ALS INT, @prevbalance ALS GELD; UPDATE #Transacties SET @prevbalance = saldo = CASE WHEN actid = @prevaccount THEN @prevbalance + val ANDERS val END, @prevaccount = actid FROM #Transactions WITH(INDEX(1), TABLOCKX) OPTIE (MAXDOP 1); SELECTEER * VAN #Transacties; DROP TABLE #Transacties;

De schets van deze oplossing wordt weergegeven in de volgende afbeelding. Het eerste deel wordt vertegenwoordigd door de INSERT-instructie, het tweede door de UPDATE en het derde door de SELECT-instructie:

Deze oplossing gaat ervan uit dat de UPDATE-uitvoeringsoptimalisatie altijd een geordende scan van de geclusterde index uitvoert, en de oplossing biedt een aantal tips om omstandigheden te voorkomen die dit zouden kunnen voorkomen, zoals gelijktijdigheid. Het probleem is dat er geen officiële garantie bestaat dat de optimalisatie altijd in de volgorde van de geclusterde index zal kijken. Je kunt niet vertrouwen op fysieke berekeningen om ervoor te zorgen dat de code logisch correct is, tenzij er logische elementen in de code zitten die, per definitie, dat gedrag kunnen garanderen. Er is geen logische functie in deze code die dit gedrag kan garanderen. Uiteraard ligt de keuze om deze methode wel of niet te gebruiken volledig op uw geweten. Ik denk dat het onverantwoord is om het te gebruiken, ook al heb je het duizenden keren gecontroleerd en "alles lijkt te werken zoals het zou moeten."

Gelukkig maakt SQL Server 2012 deze keuze vrijwel overbodig. Als u een uiterst efficiënte oplossing heeft die gebruikmaakt van aggregatiefuncties in vensters, hoeft u niet aan andere oplossingen te denken.

Prestatiemeting

Ik heb de prestaties van verschillende technieken gemeten en vergeleken. De resultaten worden weergegeven in de onderstaande figuren:

Ik heb de resultaten in twee grafieken gesplitst omdat de subquery/join-methode zoveel langzamer is dan de andere, dat ik er een andere schaal voor moest gebruiken. Houd er in ieder geval rekening mee dat de meeste oplossingen een lineair verband laten zien tussen de werklast en de partitiegrootte, en dat alleen de subquery- of join-oplossing een kwadratisch verband vertoont. Het is ook duidelijk om te zien hoeveel efficiënter de nieuwe oplossing op basis van de aggregatiefunctie in vensters is. De UPDATE-oplossing met variabelen is ook erg snel, maar om de reeds beschreven redenen raad ik het gebruik ervan niet aan. De CLR-oplossing is ook behoorlijk snel, maar je moet al die .NET-code schrijven en de assembly in de database implementeren. Hoe je het ook bekijkt, een kitgebaseerde oplossing met raamelementen blijft de meeste voorkeur.