22 października 2011

Wymienię IF/EXISTS/SELECT/UPDATE/INSERT na MERGE

Ignorancja to słabość


Ostatnio przeglądałem T-SQL kod w którym została użyta pewna siermiężna acz użyteczna konstrukcja (użyteczna w poprzednich wersjach SQL Server'a, tak od 2005 w dół). Zapytałem się autora kodu, czy przypadkiem nie używają MS SQL Server 2008, otrzymałem pozytywną odpowiedź "nawet w wersji R2". Kod który prezentuje ogólny zarys problemu znajduje się poniżej. Przykłądowa implementacja wstawia rekord do tabeli jeśli jeszcze on nie istnieje, w innym wypadku aktualizuje wartości wybranych kolumn przy pomocy UPDATE'u.

IF EXISTS(SELECT * FROM Table1 WHERE Id = @Id)
BEGIN
  INSERT INTO Table1(Id, Name) VALUES(@Id, @Name)
END
ELSE
BEGIN
  UPDATE Table1
     SET Name = @Name
   WHERE Id = @Id
END

Niestety i na szczęście każde triki, schematy ulegają w końcu przedawnieniu. I tak się stało z powyższym IF/EXISTS/SELECT/UPDATE/INSERT.


MERGE na ratunek


W MS SQL Server 2008 pojawiło słowo kluczowe MERGE. Nigdy nie miałem okazji używać MERGE produkcyjnie. Aczkolwiek warto śledzić jak się rozwija Transact-SQL, ponieważ nie znasz dnia ani godziny kiedy taka zdawkowa wiedza może zostać wykorzystana (np.warto wiedzieć, że w wersji MS SQL Server 2012 wprowadzono wsparcie dla "paginacji" ).

Wracając do tematu. Powyższe zapytanie (EXISTS .... UPDATE) można w prosty sposób przepisać przy użyciu MERGE.

IF OBJECT_ID('dbo.Table1','U') IS NOT NULL DROP TABLE dbo.Table1

CREATE TABLE dbo.Table1(
  Id Int NOT NULL PRIMARY KEY,
  MyName Varchar(10) NOT NULL
)

IF OBJECT_ID('dbo.SmartUpsert','P') IS NOT NULL DROP PROC dbo.SmartUpsert
GO
CREATE PROCEDURE dbo.SmartUpsert(
  @Id Int
, @MyName VARCHAR(10)
)
AS
BEGIN
    MERGE dbo.Table1 AS target
    USING (SELECT @Id, @MyName) AS source (Id, MyName)
    ON (target.Id = source.Id)
    WHEN MATCHED THEN 
        UPDATE SET MyName = source.MyName
 WHEN NOT MATCHED THEN 
     INSERT (Id, MyName)
     VALUES (source.Id, source.MyName)
OUTPUT deleted.MyName OldMyName, inserted.MyName as NewMyName ;   
END
GO
DECLARE @Id1 Int = 1
, @MyName1 VARCHAR(10) = 'Max'
, @Id2 Int = 2
, @MyName2 VARCHAR(10) = 'Joanna'

EXEC dbo.SmartUpsert @Id1, @MyName1
EXEC dbo.SmartUpsert @Id2, @MyName2
EXEC dbo.SmartUpsert @Id1, @MyName2

Wyniki zgodne z oczekiwaniami, tabele deleted i inserted zachowują się jak w triggerach DML.




Dla MERGE otrzymujemy następujący plan wykonania.


Panta rei, trzeba ciągle poszerzać swoje horyzonty.

Hope this helps.

2 komentarze:

  1. Słaby ten artykuł. Długi wstęp a o MERGE dwa zdania. Zero komentarza do kodu, czy zalet MERGE. Zamiast pisać coś takiego, może lepiej nie śmiecić? Wyszukiwarki znajdują potem takie nieprzydatne strony.

    OdpowiedzUsuń

Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.