Datenbanken und mehr

17. Juni 2007

MERGE Statement – ein cooles Feature von Katmai

Filed under: Katmai, Performance, SQL Server, T-SQL — Urs Gehrig @ 22:35

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!

Advertisements

Schreibe einen Kommentar »

Es gibt noch keine Kommentare.

RSS feed for comments on this post. TrackBack URI

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

Erstelle eine kostenlose Website oder Blog – auf WordPress.com.

%d Bloggern gefällt das: