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

4 komentarze

  1. “Warto też założyć indeks na kolumnie [id] zawierający kolumnę [skrot]” – nie rozumiem tej kwestii.
    Sam artykuł – miodzio. Mam tylko pytanie bo jeszcze w praktyce nie testowałem – ten “skrót” jakiej jest długości? Bo przy takim przyroście wydajności to…

    1. 1. Indeks na kolumnie [ID], zawierający kolumnę [skrot] to tzw. “covering index” (nie znam polskiego odpowiednika tej nazwy – może “indeks pokrywający”?), czyli taki indeks, który oprócz kolumny [id] (zorganizowanej w b-tree w celu optymalizacji wyszukiwań) zawiera dodatkowo wartości z kolumny [skrot], niejako wewnętrznie “przyklejone” do [id] – w ten sposób unikamy kosztownych czasowo operacji typu “bookmark lookup” (lub “key lookup”), które potrafią zająć ponad 50% czasu wykonania zapytania. “Covering index” został dodany do SQL Servera w okolicach wersji bodajże 2005 (nie dam głowy). Nie jestem pewien, jak to wygląda w innych silnikach baz danych.

      2. Skrót kryptograficzny MD5 daje 128 bitów na wyjściu – zwykle zapisuje się go za pomocą 32 cyfr szesnastkowych, ale de facto da się go “skompresować” do dwóch wartości typu BIGINT. Inne funkcje skrótu (najczęściej używane są różne warianty SHA) dają od 160 do 512 bitów na wyjściu, z wyjątkiem odmian SHA3-SHAKE, które mogą generować skrót o zadanej długości. Przy okazji, MD5 oraz SHA-1 nie są już uznawane za bezpieczne (znaleziono kolizje), na chwilę obecną standardem jest SHA-2; standard SHA-3 jeszcze nie zdążył się spopularyzować (chociaż jest pod każdym względem lepszy od poprzednika). Ja do dziś używam MD5 do generowania skrótów w bazie danych – jeszcze nie udało mi się natrafić na kolizję 😉

      1. chyba coś namieszaliście w #1. Covering index to taki, który “pokrywa” całość zapotrzebowania na dane dla danego zapytania. CI to nie jest jakiś specyficzny typ indexu. Ten sam index dla jednego zapytania będzie covering a dla 2go – już nie.
        Natomiast rozszerzanie indexu o ddoatkowe kolumny wymaga użycia klauzuli include.

        1. No cóż. Masz rację Schroedingera, a więc albo ją masz, albo nie, w zależności od przyjętej nomenklatury. Jedni mówią, że “covering index” istnieje tylko w powiązaniu z konkretnym zapytaniem / grupą zapytań: takich, które nie wymagają żadnych kolumn spoza indeksu. I w takiej sytuacji mówimy, że dany indeks “pokrywa” dane zapytanie.

          Jednak istnieje również szkoła, która określa niektóre zapytania mianem “covered queries”, czyli zapytania “pokryte” przez index. Tutaj patrzymy z drugiej strony: staramy się napisać takie zapytanie, które nie będzie wychodzić poza ramy istniejących indeksów – i jeżeli nam się uda, nasze zapytanie jest pokryte przez indeks. Przedstawiciele tej szkoły twierdzą, że “covering index” to nic innego, jak indeks zawierający klauzulę “INCLUDE”, która robi (pi x oko) to, co opisuję w powyższym komentarzu.

          Tak czy siak, żeby rozwiać wątpliwości, w tym konkretnym przypadku chodziło mi o utworzenie indeksu na kolumnie [id] “pokrywającego” też kolumnę [skrot] (oraz zapytanie MERGE), ponieważ do z-MERGE-owania rekordów będziemy potrzebowali obydwu kolumn, więc dodanie [skrot] do indeksu znacznie przyspieszy tę operację.

Leave a Comment

Komentarze mile widziane.

Jeżeli chcesz do komentarza wstawić kod, użyj składni:
[code]
tutaj wstaw swój kod
[/code]

Jeżeli zrobisz literówkę lub zmienisz zdanie, możesz edytować komentarz po jego zatwierdzeniu.