SQLInsertWhereNotExists_00

INSERT WHERE NOT EXISTS i minimalne logowanie

Written by on in MSSQL, SQL Server database, TSQL

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:

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”.

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:

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ę:

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:

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:

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:

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:

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:

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:

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

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

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ł:

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!

Adrian Chodkowski
Follow me
Latest posts by Adrian Chodkowski (see all)

1 Comment

Leave a Reply