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:
- Sprawdzamy dane źródłowe z pustą tabelą docelową
- Sprawdzamy dane źródłowe z tabelą docelową zawierającą dane
- Tabela docelowa ma kolumnę typu CHAR(6)
- Tabela docelowa ma kolumnę typu VARCHAR(6)
W obu sytuacjach sprawdzę pliki z danymi źródłowymi w układzie:
- Dane z narastającymi wartościami w kolumnie, po której szukamy danych za pomocą Lookup
- Dane z kolejnymi takimi samymi wartościami w kolumnie, po której szukamy danych za pomocą Lookup
- 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.
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):
- Sprawdzanie poprawności pakietu przez Visual Studio – weryfikacja metadanych bazy
- Sprawdzanie poprawności pakietu przez silnik SSIS (przed uruchomieniem)
- Walidacja pakietu po uruchomieniu przez silnik SSIS
- Konfiguracja komponentu Lookup
- 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:
- Plik 1, kolumna login
CHAR(6)
, tabela docelowa pusta: 20 wpisów - Plik 1, kolumna login
CHAR(6)
, tabela docelowa zawiera dane: 20 wpisów - Plik 1, kolumna login
VARCHAR(6)
, tabela docelowa pusta: 20 wpisów - Plik 1, kolumna login
VARCHAR(6)
, tabela docelowa zawiera dane: 20 wpisów - Plik 2, kolumna login
CHAR(6)
, tabela docelowa pusta: 20 wpisów - Plik 2, kolumna login
CHAR(6)
, tabela docelowa zawiera dane: 18 wpisów - Plik 2, kolumna login
VARCHAR(6)
, tabela docelowa pusta: 20 wpisów - Plik 2, kolumna login
VARCHAR(6)
, tabela docelowa zawiera dane: 10 wpisów - Plik 3, kolumna login
CHAR(6)
, tabela docelowa pusta: 20 wpisów - Plik 3, kolumna login
CHAR(6)
, tabela docelowa zawiera dane: 18 wpisów - Plik 3, kolumna login
VARCHAR(6)
, tabela docelowa pusta: 20 wpisów - 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.
Jedna uwaga do wpisu “SSIS pod lupą – Lookup NoCache”