Pchełki SQL: MERGE, optymalizacja haszem

https://xpil.eu/JmM 📋

Dziś pchełka dotycząca optymalizacji operatora MERGE.

Uwaga: jeżeli nie wiesz, jak działa MERGE, lub po prostu nie interesujesz się bazami danych, ten wpis raczej nie jest dla Ciebie. Istnieje szansa, że uśniesz w okolicach czwartego akapitu...

Mamy następujący scenariusz: ładujemy dane do hurtowni. W pierwszej kolejności ładowana jest warstwa STAGE a następnie warstwa EDW.

Dla niezorientowanych: w STAGE mamy kopię 1:1 danych z systemu źródłowego, natomiast w EDW mamy "porządnie" poukładane dane, 3NF itd. Dla potrzeb niniejszego wpisu uprościłem nieco tę zasadę i do EDW ładuję dane ze STAGE "jak leci"

Załóżmy, że chodzi o dane do Działu Analizy Stolca, a konkretnie tabelę z klapami do sedesów:

W warstwie STAGE robimy:

IF OBJECT_ID('stage.s_klapy') IS NULL DROP TABLE stage.s_klapy;

SELECT id, model, cena, data, kolor
INTO stage.s_klapy
FROM jakis_system_zrodlowy.baza_w_tamtym_systemie.dbo.klapy

Natomiast w warstwie EDW robimy:

MERGE edw.klapy tgt
USING stage.s_klapy src ON tgt.id = src.id
WHEN MATCHED THEN
  UPDATE SET
  tgt.model = src.model
  , tgt.cena = src.cena
  , tgt.data = src.data
  , tgt.kolor = src.kolor
WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (
  src.id
  , src.model
  , src.cena
  , src.data
  , src.kolor
);

Jak widać, logika jest prosta: jeżeli rekord źródłowy już istnieje u celu, aktualizujemy jego dane, w przeciwnym razie dodajemy nowy rekord. Proste?

W zasadzie tak, ale jest tu jedno "ale": przy dopasowaniu będziemy nadpisywać WSZYSTKIE rekordy docelowe danymi źródłowymi niezależnie od tego, czy nastąpiły jakieś zmiany, czy nie. Straszne marnotrawstwo zasobów serwera.

Stąd też warto nasz operator MERGE nieco usprytnić:

MERGE edw.klapy tgt
USING stage.s_klapy src ON tgt.id = src.id
WHEN MATCHED AND
(tgt.model <> src.model
  OR tgt.cena <> src.cena
  OR tgt.data <> src.data
  OR tgt.kolor <> src.kolor)
THEN UPDATE SET
tgt.model = src.model
, tgt.cena = src.cena
, tgt.data = src.data
, tgt.kolor = src.kolor
WHEN NOT MATCHED BY TARGET
THEN INSERT VALUES (
  src.id
  , src.model
  , src.cena
  , src.data
  , src.kolor
);

W tym drugim przykładzie zaktualizowane zostaną jedynie te rekordy, które uległy zmienie od ostatniego razu. Kod taki wykona się o wiele szybciej, jednak jest on dość nieelegancki - jeżeli bowiem mamy wiele kolumn, dodatkowe porównanie wartości będzie zajmować mnóstwo miejsca i popsuje czytelność zapytania. Dlatego warto zamiast tego zatosować funkcję skrótu (haszującą), która uprości wykrywanie zmian przez operator MERGE kosztem niewielkiego skomplikowania w warstwie STAGE.

A więc najpierw:

IF OBJECT_ID('stage.s_klapy') IS NULL DROP TABLE stage.s_klapy;

SELECT id, model, cena, data, kolor, HASHBYTES('sha1',
CONVERT(VARCHAR(MAX), model)
+ CONVERT(VARCHAR(MAX), cena)
+ CONVERT(VARCHAR(MAX), data)
+ CONVERT(VARCHAR(MAX), kolor)) as skrot
INTO stage.s_klapy
FROM jakis_system_zrodlowy.baza_w_tamtym_systemie.dbo.klapy;

CREATE NONCLUSTERED INDEX ix_s_klapy on stage.s_klapy(id, skrot)

... a potem:

MERGE edw.klapy tgt
USING stage.s_klapy src ON tgt.id = src.id
WHEN MATCHED AND src.skrot <> tgt.skrot
THEN UPDATE SET
tgt.model = src.model
, tgt.cena = src.cena
, tgt.data = src.data
, tgt.kolor = src.kolor
, tgt.skrot = src.skrot
WHEN NOT MATCHED BY TARGET
THEN INSERT VALUES (
src.id
, src.model
, src.cena
, src.data
, src.kolor
, src.skrot
);

Oczywiście należy pamiętać o tym, żeby dodać kolumnę [skrot] do tabeli docelowej (w warstwie EDW). Warto też założyć indeks na kolumnie [id] zawierający kolumnę [skrot] (covering index) żeby zapytanie wykonało się w krótszym czasie.

W moim przypadku przyrost wydajności w warstwie EDW, na pięciu milionach rekordów, był ponaddwudziestokrotny: zamiast 24 minut zapytanie MERGE wykonało się w 56 sekund. Natomiast czas zapisania danych do warstwy STAGE wzrósł z 20 sekund do około minuty (15 kolumn) - oprócz zapisania samych danych doszło jeszcze wyliczenie skrótu oraz założenie indeksu. Summa summarum zaoszczędziłem na użyciu funkcji haszującej kupę czasu.

https://xpil.eu/JmM 📋