Beim Durchsehen von Katmai – dem Nachfolger von SQL Server 2005 – bin ich auf eine coole Erweiterung in der DB-Engine gestossen; dem MERGE Statement.
Das MERGE Statement vereint die drei Statements INSERT, UPDATE und DELETE. Neu kann mit nur einem einzigen Statement ein ganzes Datenset in einem Rutsch in die DB geschrieben werden, unabhängig davon, ob einzelne Records bereits erfasst (und demnach ein UPDATE benötigen) , neu sind (und somit ein INSERT benötigen) oder aber aus der DB gelöscht werden müssen (und somit ein DELETE benötigen). Natürlich erwarte ich von so einem neuen MERGE Statement auch eine erhebliche Performance Verbesserung gegenüber der alt hergebrachten Methode mit zwei oder drei separaten Statements (INSERT, UPDATE und DELETE). Ob dem so ist, werde ich dir gleich nachfolgend versuchen zu beweisen. Zuerst aber mal ein Beispiel zum MERGE Statement (dies habe ich mir aus dem BOL von Katmai ausgelehnt).
Szenario: In einem täglichen Prozess soll die ProductInventory Tabelle mit den Einträgen aus SalesOrderDetail aktualisiert werden. Die Spalte Quantity aus ProductInventory soll pro Produkt um die Anzahl der verkauften Produkte reduziert werden. Fällt ein Produkt auf eine Menge Null zurück, muss dieses Produkt aus ProductInventory gelöscht werden.
Lösung mit SQL Server 2005:
WITH Selling(ProductID, OrderQty) AS
(
SELECT ProductID, SUM(OrderQty)
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = {d ‘2001-07-03′}
GROUP BY ProductID
)
DELETE Production.ProductInventory
FROM Production.ProductInventory pi
JOIN Selling src ON (pi.ProductID = src.ProductID)
WHERE pi.Quantity = src.OrderQty;
WITH Selling(ProductID, OrderQty) AS
(
SELECT ProductID, SUM(OrderQty)
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = {d ‘2001-07-03′}
GROUP BY ProductID
)
UPDATE Production.ProductInventory
SET Quantity = pi.Quantity - src.OrderQty
FROM Production.ProductInventory pi
JOIN Selling src ON (pi.ProductID = src.ProductID)
WHERE pi.Quantity > src.OrderQty;
Lösung mit Katmai:
MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty)
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = {d ‘2001-07-03′}
GROUP BY ProductID) AS src (ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED AND pi.Quantity - src.OrderQty = 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty;
Also, eleganter ist die Lösung von Katmai allemal; wie sieht es aber mit der Performance aus? OK, ich weiss – Performance Tests mit CTP’s sind nicht gerade besonders fair. Ich bin aber auch nicht an absoluten Zahlen interessiert, sondern vielmehr an Trends. Daher habe ich die IO’s (SET STATISTICS IO ON) der beiden Lösungen miteinander verglichen:
mit SQL Server 2005:
Table ‘ProductInventory’. Scan count 4, logical reads 8, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderDetail’. Scan count 5, logical reads 15, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderHeader’. Scan count 1, logical reads 703, physical reads 2, read-ahead reads 699, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(0 row(s) affected)
Table ‘ProductInventory’. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderDetail’. Scan count 5, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderHeader’. Scan count 1, logical reads 703, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘ProductInventory’. Scan count 0, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(8 row(s) affected)
mit Katmai:
Table ‘ProductInventory’. Scan count 4, logical reads 8, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderDetail’. Scan count 5, logical reads 15, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderHeader’. Scan count 1, logical reads 703, physical reads 2, read-ahead reads 699, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘ProductInventory’. Scan count 0, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(8 row(s) affected)
Der Effekt des neuen Merge Statements scheint mir offensichtlich zu sein; rund die Hälfte der IO’s. Und das ist schlicht und einfach Super!
