SSIS pod lupą – Lookup NoCache

Transformacja Lookup w SQL Server Integration Services ma trzy tryby przechowywania danych podręcznych (cache):

• pełny (Full cache),
• częściowy (Partial cache)
• wcale (No cache).

Jaki wpływ na bazę danych ma każdy z tych trzech trybów? Na początek: No Cache.

No cache oznacza, że nie przechowujemy w pamięci podręcznej żadnych danych pomocniczych. Czyli każdy rekord źródłowy będzie powodował zapytanie do bazy danych o dane do zmapowania. Czy tak jest na pewno okaże się za chwilę podczas rozpatrywania kilku przypadków testowych:

1. Sprawdzamy dane źródłowe z pustą tabelą docelową
2. Sprawdzamy dane źródłowe z tabelą docelową zawierającą dane
3. Tabela docelowa ma kolumnę typu CHAR(6)
4. Tabela docelowa ma kolumnę typu VARCHAR(6)

W obu sytuacjach sprawdzę pliki z danymi źródłowymi w układzie:

1. Dane z narastającymi wartościami w kolumnie, po której szukamy danych za pomocą Lookup
2. Dane z kolejnymi takimi samymi wartościami w kolumnie, po której szukamy danych za pomocą Lookup
3. Dane zawierają wartości puste

Przykład bazuje na wyszukiwaniu identyfikatorów pracowników, do których chcę przypisać sprzedaż. Za pomocą transformacji Lookup będę wyszukiwał numeryczny identyfikator użytkownika do loginu. Rekord źródłowy ma postać:

Login, ProductName, Price, Tax, Quantity, Total, Date

Pierwszy plik zawiera informacje o sprzedaży posortowane wg daty sprzedaży (Date) a następnie loginu użytkownika (Login). Dla uproszczenia każdego dnia jeden pracownik sprzedawał jeden produkt – chodzi o sam układ danych w kolumnie Login. 20 rekordów, dwie serie User1 – User10.

Drugi plik zawiera dane z jednego dnia, pracownicy sprzedawali po kilka produktów, dane są posortowane wg kolumny Login. 20 rekordów, różne ilości rekordów UserX, ale posortowane.

Trzeci plik zawiera dane z jednego dnia i okazjonalne puste wartości w kolumnie Login. 20 rekordów, plik bazuje na pliku 2, przy czym usunięto dwie wartości loginów i zastąpiono pustym ciągiem znaków.

Kolumna Login w danych źródłowych jest typu DT_STR 6, czyli przechowuje sześć znaków. Do porównania z danymi w bazie sprawdzę przypadki, kiedy dane są typu CHAR(6) i VARCHAR(6).

USE Test;
GO

IF OBJECT_ID(N'dbo.Users', N'U') IS NOT NULL
    EXEC sys.sp_executesql N'DROP TABLE dbo.Users;';
GO

CREATE TABLE dbo.Users (
    Id      TINYINT        NOT NULL,
    Login   CHAR(6)        NOT NULL,
    Name    VARCHAR(10)    NOT NULL
);
GO

IF OBJECT_ID(N'dbo.UsersV', N'U') IS NOT NULL
    EXEC sys.sp_executesql N'DROP TABLE dbo.UsersV;';
GO

CREATE TABLE dbo.UsersV (
    Id      TINYINT        NOT NULL,
    Login   VARCHAR(6)     NOT NULL,
    Name    VARCHAR(10)    NOT NULL
);
GO

Pakiet testujący Lookup będzie się składał z jednego Data Flow Task zawierającego Flat File Source i Lookup.

Konfiguracja Lookup:

General: No cache, Redirect rows to no match output

Connection: SQL query: SELECT Id, Login FROM dbo.Users / SELECT Id, Login FROM dbo.UsersV

Columns: Łączenie po kolumnie Login

Advanced: niezaznaczone pole Modify the SQL statement

Error output: zostawione bez zmian

Wpływ na bazę danych będę obserwował przez SQL Server Profiler z minimalną konfiguracją zdarzeń:

• Stored Procedures / RPC:Completed
• TSQL / SQL:Batch Completed

Żeby nie pokazywało za dużo dodatkowych wpisów na poziomie serwera filtruję kolumnę Database Name o nazwę bazy, w której przeprowadzam testy (chcę sprawdzić tylko wpływ Lookup a nie ogólnie silnika SSIS). Do wyboru zdarzeń zmodyfikowałem szablon Standard (default), w którym kolumna Database Name nie jest domyślnie widoczna w definicji i trzeba ją dodać wybierając checkbox Show all columns. Filtr na nazwę bazy ustawiam naciskając przycisk Column filters…

No to start i analiza wyników. W każdym przypadku podaję ilość linii związanych z odpytywaniem bazy danych przez transformację Lookup. Analizując wyniki pokazywane przez profiler można podzielić je na kilka części (a przynajmniej tak mi się wydaje, na razie nie znalazłem oficjalnego wytłumaczenia):

1. Sprawdzanie poprawności pakietu przez Visual Studio – weryfikacja metadanych bazy
2. Sprawdzanie poprawności pakietu przez silnik SSIS (przed uruchomieniem)
3. Walidacja pakietu po uruchomieniu przez silnik SSIS
4. Konfiguracja komponentu Lookup
5. Wyszukiwanie przez Lookup danych w bazie

Podział na części wynika też z analizy pliku logu przetwarzania pakietu (opcja Logging na pakiecie), gdzie występują dwie fazy Validate, po starcie pakietu następuje ponowna walidacja pakietu w fazie PreExecute, a dopiero potem następuje faza Execute. Wygląda jakby przed dotarciem do interesującej mnie części wykonywane były trzy weryfikacje poprawności (jedna na poziomie Visual Studio, czyli SSDT), a dopiero przed serią zapytań do bazy (5) konfiguracja samej kontrolki (4). Takich wpisów w części od (1) do (4) jest 20 (4 razy po pięć wpisów). Okazjonalnie zdarza się wpis sp_reset_connection.

Co do samych wyników: zliczam ilości wpisów

exec sp_executesql N'select * from (SELECT Id, Login FROM dbo.Users) [refTable] 
where [refTable].[Login] = @P1',N'@P1 char(6)','<UserX>'

gdzie <UserX> oznacza przekazywaną wartość loginu, np. User 1 . Spacja na końcu, bo ciąg znaków jest dopełniany do 6.

Ponieważ w każdym pliku jest po 20 rekordów zakładam, że 20 wpisów exec sp_executesql oznacza odpytanie bazy danych o każdą wartość z pliku. Testuję łącznie 12 przypadków i spisuję liczbę rekordów wywołanych przez Lookup:

1. Plik 1, kolumna login CHAR(6), tabela docelowa pusta: 20 wpisów
2. Plik 1, kolumna login CHAR(6), tabela docelowa zawiera dane: 20 wpisów
3. Plik 1, kolumna login VARCHAR(6), tabela docelowa pusta: 20 wpisów
4. Plik 1, kolumna login VARCHAR(6), tabela docelowa zawiera dane: 20 wpisów
5. Plik 2, kolumna login CHAR(6), tabela docelowa pusta: 20 wpisów
6. Plik 2, kolumna login CHAR(6), tabela docelowa zawiera dane: 18 wpisów
7. Plik 2, kolumna login VARCHAR(6), tabela docelowa pusta: 20 wpisów
8. Plik 2, kolumna login VARCHAR(6), tabela docelowa zawiera dane: 10 wpisów
9. Plik 3, kolumna login CHAR(6), tabela docelowa pusta: 20 wpisów
10. Plik 3, kolumna login CHAR(6), tabela docelowa zawiera dane: 18 wpisów
11. Plik 3, kolumna login VARCHAR(6), tabela docelowa pusta: 20 wpisów
12. Plik 3, kolumna login VARCHAR(6), tabela docelowa zawiera dane: 12 wpisów

Wnioski:

• widać, że nie zawsze baza jest odpytywana dla każdego rekordu źródłowego
• w przypadku, jeśli pytamy ponownie o tą samą wartość nie idzie zapytanie do bazy – o ile pytamy o kolumnę VARCHAR
• jeśli pytamy o kolumnę typu CHAR Lookup nie zawsze rozpoznaje, że przed chwilą pytał o tą wartość i ponawia zapytanie do bazy
• 18 rekordów dla kolumny typu CHAR wynika z rekordu User10, który występuje trzykrotnie na końcu plików 2 i 3 – długość loginu to 6 znaków bez spacji na końcu i Lookup wie, że przed chwilą o nią pytał
• 10 rekordów dla kolumny VARCHAR w pliku 2 – Lookup pamięta wynika dla przed chwilą odpytywanej wartości i nie ponawia zapytania do bazy
• 12 rekordów  dla pliku 3 wynika z dwóch rekordów z pustą wartością loginu (traktowana jako 6 spacji)
• W przypadku odpytywania pustych tabel Lookup nie przechowuje informacji, że nie znalazł dopasowania

Najważniejszy wniosek: jeśli mamy posortowane dane w strumieniu wejściowym, to lookup ustawiony w trybie No cache będzie odpytywał bazę tylko raz dla każdej nowej wartości.

Co dokładnie oznaczają w profilerze polecenia SQL  dotyczące walidacji – w następnym wpisie.

• lookup
• profiler
• ssis