INSERT WHERE NOT EXISTS i minimalne logowanie

W ostatnim okresie w projekcie który aktualnie realizuję natrafiłem na dosyć ciekawy i specyficzny przypadek. Wiązał się on z procedurą napisaną w TSQL, która ładowała nowe rekordy do tabeli docelowej. Co w tym takiego ciekawego? A no to, że zachowywała się ona w dosyć nieprzewidywalny sposób i razem z kolegą (pozdrawiam Krzysiek!) próbowaliśmy rozszyfrować to dziwne zachowanie. O co w tym wszystkim chodzi? Zapraszam serdecznie do lektury.

Nie wchodząc w szczegółowy opis przypadku biznesowego przejdźmy bez owijania w bawełnę do praktyki. W pierwszej kolejności stworzymy sobie bazę danych o niezwykle kreatywnej nazwie tj. “TEST”. Nie będziemy szczegółowo jej konfigurować ponieważ do naszych celów wystarczą domyślne ustawienia – jedyne co ustawilimy jawnie to RECOVERY MODEL, który w tym przypadku przyjmie wartość SIMPLE:

CREATE DATABASE TEST
GO

ALTER DATABASE [TEST] SET RECOVERY SIMPLE WITH NO_WAIT
GO

Po co nam Simple? Ponieważ nasza baza danych jest bazą analityczną dla której kluczowe jest dostarczenie w jak najszybszy sposób danych, a nie wysoka dostępność i możliwość odtworzenia bazy do konkretnego punktu w czasie. W dalszej kolejności stworzymy sobie dwie tabele, które są kopią tabeli Fact.Sale z WideWorldImporters:

use TEST
GO

CREATE TABLE [dbo].[Source](
	[Sale Key] [bigint] NULL,
	[City Key] [int] NULL,
	[Customer Key] [int] NULL,
	[Bill To Customer Key] [int] NULL,
	[Stock Item Key] [int] NULL,
	[Invoice Date Key] [date] NULL,
	[Delivery Date Key] [date] NULL,
	[Salesperson Key] [int] NULL,
	[WWI Invoice ID] [int] NULL,
	[Description] [nvarchar](100) NULL,
	[Package] [nvarchar](50) NULL,
	[Quantity] [int] NULL,
	[Unit Price] [decimal](18, 2) NULL,
	[Tax Rate] [decimal](18, 3) NULL,
	[Total Excluding Tax] [decimal](18, 2) NULL,
	[Tax Amount] [decimal](18, 2) NULL,
	[Profit] [decimal](18, 2) NULL,
	[Total Including Tax] [decimal](18, 2) NULL,
	[Total Dry Items] [int] NULL,
	[Total Chiller Items] [int] NULL,
	[Lineage Key] [int] NULL
)
GO

CREATE TABLE [dbo].[Target](
	[Sale Key] [bigint] NULL,
	[City Key] [int] NULL,
	[Customer Key] [int] NULL,
	[Bill To Customer Key] [int] NULL,
	[Stock Item Key] [int] NULL,
	[Invoice Date Key] [date] NULL,
	[Delivery Date Key] [date] NULL,
	[Salesperson Key] [int] NULL,
	[WWI Invoice ID] [int] NULL,
	[Description] [nvarchar](100) NULL,
	[Package] [nvarchar](50) NULL,
	[Quantity] [int] NULL,
	[Unit Price] [decimal](18, 2) NULL,
	[Tax Rate] [decimal](18, 3) NULL,
	[Total Excluding Tax] [decimal](18, 2) NULL,
	[Tax Amount] [decimal](18, 2) NULL,
	[Profit] [decimal](18, 2) NULL,
	[Total Including Tax] [decimal](18, 2) NULL,
	[Total Dry Items] [int] NULL,
	[Total Chiller Items] [int] NULL,
	[Lineage Key] [int] NULL
)
GO

W dalszej kolejności uzupełniamy tabelę Source danymi z WideWorldImporters – robimy to dwudziestokrotnie abyśmy mieli trochę wierszy do testowania (pętla powinna wygenerować nieco ponad 4.5mln wierszy):

INSERT INTO [dbo].[Source] WITH (TABLOCK)
SELECT 
	*
FROM
[WideWorldImportersDW].[Fact].[Sale]
GO 20

To co będzie nas interesowało w opisywanym teście to dziennik transakcyjny – chcemy aby nasze operacje były minimalnie logowane co powinno zwiększyć wydajność i zmniejszyć zapotrzebowanie na miejsce na dysku dla dziennika ( o minimalnym logowaniu pisałem już trochę tutaj).

Żeby mieć pewność co do logowania w naszym teście zrobimy shrinka dziennika bazy testowej za pomocą odpowiedniej komendy DBCC:

DBCC SHRINKFILE (TEST_log,1)

Następnie sprawdzimy sobie rozmiar dziennika komendą DBCC SQLPERF:

DBCC SQLPERF('logspace')

Otrzymany rezultat wygląda następująco:

SQLServer_InsertWhereExistsMinimalLogging_01

Tak więc 3.875MB zajmuje plik na dysku z czego wewnętrznie około 8.5% jest zajęte. Pierwszym testem będzie wstawienie danych z tabeli źródłowej do tabeli docelowej z przełącznikiem TABLOCK. Ze względu na fakt, iż:

tabela docelowa jest bezindeksową stertą,
wyspecyfikowaliśmy TABLOCK,
nasza baza testowa ma Recovery Mode ustawiony na SIMPLE.

to nasze polecenie INSERT SELECT powinno być minimalnie logowanie – czy tak się stało? Już sprawdzamy.

INSERT INTO [dbo].[Target] WITH (TABLOCK)
(
	 [Sale Key]
	,[City Key]
	,[Customer Key]
	,[Bill To Customer Key]
	,[Stock Item Key]
	,[Invoice Date Key]
	,[Delivery Date Key]
	,[Salesperson Key]
	,[WWI Invoice ID]
	,[Description]
	,[Package]
	,[Quantity]
	,[Unit Price]
	,[Tax Rate]
	,[Total Excluding Tax]
	,[Tax Amount]
	,[Profit]
	,[Total Including Tax]
	,[Total Dry Items]
	,[Total Chiller Items]
	,[Lineage Key]
)
SELECT 
	 [Sale Key]
	,[City Key]
	,[Customer Key]
	,[Bill To Customer Key]
	,[Stock Item Key]
	,[Invoice Date Key]
	,[Delivery Date Key]
	,[Salesperson Key]
	,[WWI Invoice ID]
	,[Description]
	,[Package]
	,[Quantity]
	,[Unit Price]
	,[Tax Rate]
	,[Total Excluding Tax]
	,[Tax Amount]
	,[Profit]
	,[Total Including Tax]
	,[Total Dry Items]
	,[Total Chiller Items]
	,[Lineage Key] [int]
FROM [dbo].[Source] AS S

Dziennik troszeczkę urósł do 323MB i ze względu na specyfikę działania trybu SIMPLE zajęte miejsce po zatwierdzeniu transakcji może być natychmiast użyte przez kolejne transakcje dlatego taka niska wartość procentowego użycia. W naszym teście będziemy zatem sprawdzać przede wszystkim rozmiar loga, a nie procentową wartość “użytej przestrzeni”.

SQLServer_InsertWhereExistsMinimalLogging_02

Powiedzmy, że to jest nasz “baseline” czyli wartość do której będziemy dążyć w naszych testach. Aby uzmysłowić czytelnikom jak dużo miejsca będzie zawierało pełne logowanie wykonam analogiczną operację wstawiania tylko bez wskazówki TABLOCK (oczywiście żeby nie było wątpliwości stworzyłem bazę od nowa i wykonałem DBCC SHRINKFILE, a dla czytelności pominąłem ten kod – będę to robił za każdym razem):

INSERT INTO [dbo].[Target]
(
	 [Sale Key]
	,[City Key]
	,[Customer Key]
	,[Bill To Customer Key]
	,[Stock Item Key]
	,[Invoice Date Key]
	,[Delivery Date Key]
	,[Salesperson Key]
	,[WWI Invoice ID]
	,[Description]
	,[Package]
	,[Quantity]
	,[Unit Price]
	,[Tax Rate]
	,[Total Excluding Tax]
	,[Tax Amount]
	,[Profit]
	,[Total Including Tax]
	,[Total Dry Items]
	,[Total Chiller Items]
	,[Lineage Key]
)
SELECT 
	 [Sale Key]
	,[City Key]
	,[Customer Key]
	,[Bill To Customer Key]
	,[Stock Item Key]
	,[Invoice Date Key]
	,[Delivery Date Key]
	,[Salesperson Key]
	,[WWI Invoice ID]
	,[Description]
	,[Package]
	,[Quantity]
	,[Unit Price]
	,[Tax Rate]
	,[Total Excluding Tax]
	,[Tax Amount]
	,[Profit]
	,[Total Including Tax]
	,[Total Dry Items]
	,[Total Chiller Items]
	,[Lineage Key] [int]
FROM [dbo].[Source] AS S

Wynik może niektórych zaskoczyć ponieważ dziennik urósł aż do 2499MB:

SQLServer_InsertWhereExistsMinimalLogging_03

Mamy więc do czynienia z pełnym logowaniem. W dalszej kolejności przechodzimy do finalnego testu gdzie będziemy ładować tylko te wiersze, których nie ma w tabeli docelowej, a są w tabeli źródłowej. Aby to sprawdzić użyjemy konstrukcji INSERT SELECT WHERE EXISTS. Kluczem po którym będziemy sprawdzać istnienie wiersza jest Sales Key:

INSERT INTO [dbo].[Target] WITH (TABLOCK)
(
	 [Sale Key]
	,[City Key]
	,[Customer Key]
	,[Bill To Customer Key]
	,[Stock Item Key]
	,[Invoice Date Key]
	,[Delivery Date Key]
	,[Salesperson Key]
	,[WWI Invoice ID]
	,[Description]
	,[Package]
	,[Quantity]
	,[Unit Price]
	,[Tax Rate]
	,[Total Excluding Tax]
	,[Tax Amount]
	,[Profit]
	,[Total Including Tax]
	,[Total Dry Items]
	,[Total Chiller Items]
	,[Lineage Key]
)
SELECT 
	 [Sale Key]
	,[City Key]
	,[Customer Key]
	,[Bill To Customer Key]
	,[Stock Item Key]
	,[Invoice Date Key]
	,[Delivery Date Key]
	,[Salesperson Key]
	,[WWI Invoice ID]
	,[Description]
	,[Package]
	,[Quantity]
	,[Unit Price]
	,[Tax Rate]
	,[Total Excluding Tax]
	,[Tax Amount]
	,[Profit]
	,[Total Including Tax]
	,[Total Dry Items]
	,[Total Chiller Items]
	,[Lineage Key] [int]
FROM [dbo].[Source] AS S
WHERE NOT EXISTS
(
SELECT NULL
FROM [dbo].[Target] AS T
WHERE T.[Sale Key]=S.[Sale Key]
)

Oczywiście w momencie wykonywania powyższego skryptu tabela docelowa jest pusta ale przy każdym kolejnym uruchomieniu sprawdzenie będzie miało jak najbardziej sens. Po sprawdzeniu dziennika dostaliśmy dosyć zaskakującą informację:

SQLServer_InsertWhereExistsMinimalLogging_04

Dostaliśmy pełne logowanie! Okazuje się, że jeżeli w użytej przez nas konstrukcji tabela docelowa istnieje w źródłowym zapytaniu SELECT to nie mamy optymalizacji w postaci minimalnego logowania. Ciekawe prawda? Gdy podejrzymy plan wykonania to wygląda on w następujący sposób:

SQLServer_InsertWhereExistsMinimalLogging_05

Zgodnie z oczekiwaniami wywołany został algorytm Right Anti Semi Join przy pomocy operatora Hash Match. W rzeczywistości dbo.Target nie posiada żadnych wierszy, ale mimo to tabela hashowa do porównania została stworzona (estymowana liczba wierszy z tabeli docelowej równa była 1 dlatego też operator złączenia nie został pominięty). W tym miejscu ktoś może powiedzieć, że inna konstrukcja mogłaby być lepsza np. odfiltrowanie poprzez JOIN – spróbujmy zatem:

FROM [dbo].[Source] AS S
LEFT JOIN [dbo].[Target] AS T
ON T.[Sale Key]=S.[Sale Key]
WHERE T.[Sale Key] IS NULL

Jednakże po planie zapytania możemy zauważyć, że jest to zdecydowanie gorsze podejście bo filtracja następuje bardzo późno tj. po wykonaniu złączenia HASH MATCH czyli po zbudowaniu tabeli hashy:

SQLServer_InsertWhereExistsMinimalLogging_06

Nie dość, że straciliśmy na wydajności pod kątem algorytmu użytego do wstawienia danych to jeszcze użycie dziennika wcale nie zostało zoptymalizowane:

SQLServer_InsertWhereExistsMinimalLogging_07

Podobne rezultaty otrzymamy w momencie gdy użyjemy takich konstrukcji jak NOT IN, którego opis w tym miejscu pominę. Ciekawą alternatywą może być również użycie EXCEPT do wyekstrahowania ze źródła tylko tych wierszy, których nie ma w tabeli docelowej:

INSERT INTO [dbo].[Target] WITH (TABLOCK)
(
	 [Sale Key]
	,[City Key]
	,[Customer Key]
	,[Bill To Customer Key]
	,[Stock Item Key]
	,[Invoice Date Key]
	,[Delivery Date Key]
	,[Salesperson Key]
	,[WWI Invoice ID]
	,[Description]
	,[Package]
	,[Quantity]
	,[Unit Price]
	,[Tax Rate]
	,[Total Excluding Tax]
	,[Tax Amount]
	,[Profit]
	,[Total Including Tax]
	,[Total Dry Items]
	,[Total Chiller Items]
	,[Lineage Key]
)
SELECT 
	 S.[Sale Key]
	,S.[City Key]
	,S.[Customer Key]
	,S.[Bill To Customer Key]
	,S.[Stock Item Key]
	,S.[Invoice Date Key]
	,S.[Delivery Date Key]
	,S.[Salesperson Key]
	,S.[WWI Invoice ID]
	,S.[Description]
	,S.[Package]
	,S.[Quantity]
	,S.[Unit Price]
	,S.[Tax Rate]
	,S.[Total Excluding Tax]
	,S.[Tax Amount]
	,S.[Profit]
	,S.[Total Including Tax]
	,S.[Total Dry Items]
	,S.[Total Chiller Items]
	,S.[Lineage Key]
FROM [dbo].[Source] AS S
EXCEPT 
SELECT 
	 T.[Sale Key]
	,T.[City Key]
	,T.[Customer Key]
	,T.[Bill To Customer Key]
	,T.[Stock Item Key]
	,T.[Invoice Date Key]
	,T.[Delivery Date Key]
	,T.[Salesperson Key]
	,T.[WWI Invoice ID]
	,T.[Description]
	,T.[Package]
	,T.[Quantity]
	,T.[Unit Price]
	,T.[Tax Rate]
	,T.[Total Excluding Tax]
	,T.[Tax Amount]
	,T.[Profit]
	,T.[Total Including Tax]
	,T.[Total Dry Items]
	,T.[Total Chiller Items]
	,T.[Lineage Key]
FROM [dbo].[Target] AS T

Problem z tym podejściem jest taki, że nie zawsze jest tak, że chcemy sprawdzać to czy całe wiersze różnią się między sobą, a jedynie kolumny kluczowe. Dla niektórych przypadków może to jednak być jakieś wyjście – plan też wygląda interesująco:

SQLServer_InsertWhereExistsMinimalLogging_08

Operacja już nieco “cięższa” bo mamy wykonywanie na wielu wątkach (widać to we właściwościom, a na załączonym obrazu można to dostrzec dzięki występowaniu operatora Parallelism i “strzałkach” przy poszczególnych operatorach). Dodatkowo widzimy, że tabelka hashy jest budowana dwukrotnie co może mieć wpływ na wydajność. Gdy spojrzymy na dziennik transakcyjny to ciekawostką jest zdecydowanie zoptymalizowane jego użycie:

SQLServer_InsertWhereExistsMinimalLogging_09

Ostatnią konstrukcją jaką chciałbym przetestować jest niesławny MERGE (który często powoduje dużo problemów jednakże na ten moment pozwolę sobie ten fakt pominąć). Jego zapis wygląda następująco:

MERGE dbo.Target WITH (TABLOCK) AS Target
USING dbo.Source AS Source
ON source.[Sale Key] = target.[Sale Key]
WHEN NOT MATCHED THEN
INSERT
(
	 [Sale Key]
	,[City Key]
	,[Customer Key]
	,[Bill To Customer Key]
	,[Stock Item Key]
	,[Invoice Date Key]
	,[Delivery Date Key]
	,[Salesperson Key]
	,[WWI Invoice ID]
	,[Description]
	,[Package]
	,[Quantity]
	,[Unit Price]
	,[Tax Rate]
	,[Total Excluding Tax]
	,[Tax Amount]
	,[Profit]
	,[Total Including Tax]
	,[Total Dry Items]
	,[Total Chiller Items]
	,[Lineage Key]
)
VALUES
(
	 [Sale Key]
	,[City Key]
	,[Customer Key]
	,[Bill To Customer Key]
	,[Stock Item Key]
	,[Invoice Date Key]
	,[Delivery Date Key]
	,[Salesperson Key]
	,[WWI Invoice ID]
	,[Description]
	,[Package]
	,[Quantity]
	,[Unit Price]
	,[Tax Rate]
	,[Total Excluding Tax]
	,[Tax Amount]
	,[Profit]
	,[Total Including Tax]
	,[Total Dry Items]
	,[Total Chiller Items]
	,[Lineage Key]
);

Mimo spełnienia warunków związanych z minimalnym logowaniem to z tej perspektywy nie wygląda to najlepiej:

SQLServer_InsertWhereExistsMinimalLogging_11

Plan raczej niczego nam tutaj nie rozwiąże ale gwoli ścisłości go umieszczam:

SQLServer_InsertWhereExistsMinimalLogging_12

Dla nieprzekonanych mam jeszcze zapytanie odpytujące funkcję sys.fn_dblog sprawdzające jakie operacje znalazły się w dzienniku:

SELECT 
	 Operation
	,COUNT(*) 
FROM sys.fn_dblog(NULL,NULL)
WHERE AllocUnitName='dbo.Target'
GROUP BY Operation

SQLServer_InsertWhereExistsMinimalLogging_13

Operacje minimalnie logowane z definicji nie powinny umieszczać w dzienniku informacji o wstawieniu wierszy, a jak widać powyżej tak właśnie się stało. Jak to możliwe, że nie mamy minimalnego logowania mimo, że scenariusz jest naprawdę prosty i spełniliśmy wszystkie wymogi? A no właśnie. W moim przekonaniu aby osiągnąć pożądany stan użycia dziennika zawsze powinniśmy testować bo nie zawsze złote zasady działają. W tym przypadku nie najgorsze rezultaty osiągnęliśmy używając EXCEPT jednak nie jest on rozwiązaniem w każdym przypadku. Oczywiście w ramach procesu ETL możemy w jakiś sposób upewnić się, że wstawiamy tylko nowe dane i całą logikę zawrzeć właśnie w narzędziu za to odpowiedzialnym. Jeśli dziennik nie jest dla nas kluczowy to możemy również wrzucać dane w batchach dzięki czemu poszczególne porcje zaczną nadpisywać go od początku i nie urośnie on do niebotycznych rozmiarów. W przypadku jednak INSERT SELECT nie mamy możliwości wyspecyfikowania rozmiaru batcha i wszystko ładowane jest niejako jako pojedynczy batch. Rozwiązań tego problemu z całą pewnością jest kilka jak np. partition switching albo użycie tabelki stagingowej – postaram się to pokazać używając tej drugiej techniki:

DROP TABLE IF EXISTS #tmpTargetKeys;

SELECT [Sale Key]
INTO #tmpTargetKeys
FROM dbo.Target;

Tabela może być standardowa lub tymczasowa – ja użyłem tej drugiej opcji. Następnie w NOT EXISTS użyłem tego właśnie obiektu tymczasowego:

INSERT INTO [dbo].[Target] WITH (TABLOCK)
(
	 [Sale Key]
	,[City Key]
	,[Customer Key]
	,[Bill To Customer Key]
	,[Stock Item Key]
	,[Invoice Date Key]
	,[Delivery Date Key]
	,[Salesperson Key]
	,[WWI Invoice ID]
	,[Description]
	,[Package]
	,[Quantity]
	,[Unit Price]
	,[Tax Rate]
	,[Total Excluding Tax]
	,[Tax Amount]
	,[Profit]
	,[Total Including Tax]
	,[Total Dry Items]
	,[Total Chiller Items]
	,[Lineage Key]
)
SELECT 
	 [Sale Key]
	,[City Key]
	,[Customer Key]
	,[Bill To Customer Key]
	,[Stock Item Key]
	,[Invoice Date Key]
	,[Delivery Date Key]
	,[Salesperson Key]
	,[WWI Invoice ID]
	,[Description]
	,[Package]
	,[Quantity]
	,[Unit Price]
	,[Tax Rate]
	,[Total Excluding Tax]
	,[Tax Amount]
	,[Profit]
	,[Total Including Tax]
	,[Total Dry Items]
	,[Total Chiller Items]
	,[Lineage Key] [int]
FROM [dbo].[Source] AS S
WHERE NOT EXISTS
(
SELECT NULL
FROM #tmpTargetKeys AS T
WHERE T.[Sale Key]=S.[Sale Key]
)

Wiecie co? Rezultat jest dokładnie taki jakbym tego oczekiwał:

SQLServer_InsertWhereExistsMinimalLogging_14

SQLServer_InsertWhereExistsMinimalLogging_15

Taką tabelkę tymczasową możemy odpowiednio podrasować dodając np. indeks i kompresję jednakże to już pozostawiam poszczególnym implementacjom. Tak jak wspomniałem rozwiązań jest wiele jednakże najważniejsze dla mnie było pokazanie dosyć ciekawego przypadku który nie jest do końca oczywisty. Pozdrawiam!