In een vorig artikel heb ik laten zien dat bij sommige problemen het gebruik van een cursor veel betere resultaten geeft dan een zogenaamde ‘set-based’ oplossing. Bijvoorbeeld: Om het lopend totaal te berekenen over een ordertabel gebruikte ik een heel simpele voorbeeldcursor. De gegevens waren afkomstig uit de SalesOrderHeader tabel uit de AdventureWorks2008R2 database. In die situatie was de performance van de cursor-oplossing uitstekend. Maar ook wanneer je een iets minder simpel voorbeeld gebruikt, dan blijft in deze database de performance prima. Je kunt zelf het volgende voorbeeldje testen:
USE [AdventureWorks2008R2]
GO
/****** Object: StoredProcedure [dbo].[RunningSumCustomer] Script Date: 03/20/2011 12:42:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RunningSumCustomer]
@year int = 2008
AS
BEGIN
DECLARE @HlpTable TABLE (CustomerID int, OrderID int, TotalDue money, RunningTotal money)
DECLARE
@CustomerID int,
@PrevCustomerID int,
@OrderID int,
@TotalDue money,
@RunningTotal money
SET @RunningTotal = 0
DECLARE RunningTot CURSOR STATIC READ_ONLY -- schijnt hier sneller te zijn dan FAST_FORWARD
FOR
SELECT CustomerID, SalesOrderID, TotalDue
FROM Sales.SalesOrderHeader
where datepart(yy, OrderDate) = @year
ORDER BY CustomerID, SalesOrderID
OPEN RunningTot
FETCH NEXT FROM RunningTot INTO @CustomerID, @OrderID, @TotalDue
SELECT @PrevCustomerID = @CustomerID, @RunningTotal = 0
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PrevCustomerID <> @CustomerID
SELECT @PrevCustomerID = @CustomerID, @RunningTotal = 0
SET @RunningTotal = @RunningTotal + @TotalDue
INSERT @HlpTable VALUES (@CustomerID, @OrderID, @TotalDue, @RunningTotal)
FETCH NEXT FROM RunningTot INTO @CustomerID, @OrderID, @TotalDue
END
CLOSE RunningTot
DEALLOCATE RunningTot
SELECT * from @HlpTable
END
GO
Je kunt met recht zeggen dat de AdventureWorks2008R2 database in een bepaald opzicht geen realistisch beeld geeft van de praktijk: ze is namelijk nogal klein van omvang. Zo bevat de SalesOrderHeader tabel slechts 31.465 regels en dat zorgt ervoor dat het beeld wordt vertekend als je gaat testen op performance. Deze cursors illustreren dat goed. Laat ik ze op mijn systeem lopen over bestanden vanaf een grootte van circa 100.000 regels, dan worden de wachttijden merkbaar. Kom je met 500.000 regels, dan zijn ze eigenlijk niet meer acceptabel. In bovenstaand voorbeeld heb ik de cursor ook nog STATIC READ_ONLY gemaakt, omdat ik had gelezen dat dit een verbetering van de prestaties zou opleveren ten opzichte van een FAST_FORWARD cursor. In de praktijk merkte ik eigenlijk niets van een prestatieverbetering. Daarom heb ik dit maar niet verder onderzocht. Ik trek hieruit de conclusie dat ik, op mijn systeem, voor tabellen die meer dan 100.000 regels bevatten op zoek moet gaan naar een andere oplossing. Maar welke zou dat moeten zijn?
In een eerder artikel heb ik voor de berekening van een lopende teller een zogenaamde windowing functie gebruikt: ROW_NUMBER(). Het zou mooi zijn als je in SQL Server een dergelijke functie zou kunnen gebruiken om de lopende som te berekenen. Je zou dan iets moeten krijgen in de geest van:
SUM(Total_Due) OVER(PARTITION BY CUSTOMERID ORDER BY OrderDate) as LopendTotaal
Helaas staat SQL Server 2008 het gebruik van de ORDER BY clause in bovenstaande constructie niet toe, en als ik het goed begrijp zal dat er voorlopig ook niet van komen. Er lijkt dan ook geen eenvoudig antwoord op dit probleem te zijn. Maar ingewikkelde antwoorden zijn vaak interessant en om een lopende som in T-SQL te berekenen zijn er een paar bedacht. Eén van de meest besproken oplossingen is de zogenaamde quirky update. Het is een controversiele techniek die misschien niet is uitgevonden door Jeff Moden, maar wel voortdurend door hem onder de aandacht wordt gebracht. Deze “quirky” update werkt alleen maar als er een clustered index op de tabel staat en de update zal die clustered index ook moeten volgen. Met andere woorden: de updates zullen moeten plaatsvinden in de volgorde die wordt voorgeschreven door de clustered index. Dit is ook meteen de Achilleshiel van deze methode, want hoewel het erop lijkt dat dit altijd het geval zal zijn, kan niemand de garantie geven dat dit inderdaad zo is. Als academisch onderzoek is dit erg interessant, maar als je een klant wilt helpen dan geef je graag de garantie dat je oplossing zal werken. Moden zelf is zich overigens bewust van de zwakheden van zijn oplossing, want hij geeft aan het eind van zijn artikel een tiental restricties op bij zijn methode. Het artikel is hier te lezen: http://www.sqlservercentral.com/articles/T-SQL/68467/.
Alles bijeengenomen zal ik deze techniek in de praktijk niet gebruiken, maar om te laten zien tot welke spectaculaire resultaten ze leidt, geef ik hier een voorbeeldje.
Eerst even een tabel met een clustered index aanmaken en die vullen vanuit de SalesOrderHeader tabel in AdventureWorks
USE [AdventureWorks2008R2] GO /****** Object: Table [dbo].[testquirky] Script Date: 03/20/2011 13:33:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[testquirky]( [CustomerID] [int] NOT NULL, [OrderID] [int] NOT NULL, [Totaldue] [money] NULL, [RunningSum] [money] NULL, [Runningcount] [int] NULL, CONSTRAINT [PK_testquirky] PRIMARY KEY CLUSTERED ( [CustomerID] ASC, [OrderID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO insert into dbo.testquirky select CustomerID, SalesOrderID, Totaldue, 0, 0 from Sales.SalesOrderHeader
Vervolgens de update uitvoeren:
SET NOCOUNT ON
--===== Declare the working variables
DECLARE @PrevCustomerID INT
DECLARE @OrderRunningTotal MONEY
DECLARE @OrderRunningCount INT
--===== Update the running total and running count for this row using the "Quirky
-- Update" and a "Pseudo-cursor". The order of the UPDATE is controlled by the
-- order of the clustered index.
UPDATE dbo.testquirky
SET @OrderRunningTotal = RunningSum = CASE
WHEN CustomerID = @PrevCustomerID
THEN @OrderRunningTotal + Totaldue
ELSE Totaldue
END,
@OrderRunningCount = RunningCount = CASE
WHEN CustomerID = @PrevCustomerID
THEN @OrderRunningCount + 1
ELSE 1
END,
@PrevCustomerID = CustomerID
FROM dbo.testquirky WITH (TABLOCKX)
OPTION (MAXDOP 1)
GO
Heel leuk om kennis te maken met deze techniek, maar in een volgend artikel zal ik aandacht besteden aan andere oplossingen waarin ik meer vertrouwen heb.