JOIN Elimination czyli kilka słów o upraszczaniu zapytań przez SQL Server

W ostatnim czasie dostałem dosyć ciekawe pytanie w którym jego autor zapytał mnie o przykład zapytania w którym SQL Server nie łączył ze sobą tabel mimo, że takie złączenie zostało wyspecyfikowane w treści zapytania. Oczywiście coś takiego może mieć miejsce i nosi nazwę JOIN Elimination i może wystąpić w specyficznych warunkach. O tym kiedy i jak możemy zaobserwować takie zdarzenie opowiem w ramach niniejszego artykułu – zapraszam.

Do przedstawienia konkretnych funkcjonalności wykorzystamy znaną wszystkim bazę AdventureWorks. Zanim przejdziemy do samego zjawiska eliminacji złączeń pokażmy sobie typowy plan zapytania występujący przy łączeniu ze sobą tabel. Poniższe zapytanie łączy ze sobą tabele Person.Address oraz Person.StateProvince aby wyświetlić adres, miasto oraz prowincję:

USE AdventureWorks2017
GO

SELECT 
	 A.AddressLine1
	,A.City
	,SP.Name AS ProvinceName
FROM [Person].[Address] AS A
JOIN [Person].[StateProvince] AS SP
	ON SP.[StateProvinceID]=A.[StateProvinceID];
GO

Rezultat tego zapytania nie jest w tym momencie dla nas interesujący dlatego spójrzmy na plan zapytania, który wygląda następująco:

Na powyższym planie możemy zauważyć, że złączenie zostało faktycznie wykonane i do tego celu wykorzystany został operator Hash Match (link). Wiemy oczywiście, że to nie jedyny operator jaki możemy w tym miejscu zobaczyć bo w zależności od estymacji liczby wierszy, istniejących indeksów itp może to być również Nested Loops (link), Merge Join (link) lub w nowszych wersjach SQL możemy zobaczyć operator Adaptive Join (link). Nie jest jednak istotne to jaki operator tutaj zobaczymy, a to że w ogóle będzie on widoczny. Spróbujmy nieco zmienić nasze zapytanie i wyświetlmy jedynie dane z tabeli Person.Address:

SELECT 
	 A.AddressLine1
	,A.City
FROM [Person].[Address] AS A
JOIN [Person].[StateProvince] AS SP
	ON SP.[StateProvinceID]=A.[StateProvinceID];
GO

W tym miejscu możemy zauważyć, że plan zapytania nieco się zmienił:

Gdzie podział się operator złączenia i dlaczego występuje on w pierwszym przykładzie a tutaj nie? Odpowiedź na to pytanie jest dosyć prosta – w pierwszym zapytaniu w SELECT wyświetlamy kolumny z obu tabel co samo w sobie determinuje to, że tabele te muszą ze sobą być złączone. W drugim zapytaniu wyświetliliśmy jedynie tabele z tabeli Person.Address tak więc operacja JOIN miała tutaj jedynie charakter filtrujący tzn. zadaliśmy pytanie do silnika bazy danych “wyświetl adres oraz miasto z tabeli Person.Address które mają odpowiadające zdefiniowaną prowincję w tabeli Person.StateProvince”. Skoro JOIN ma w tym przypadku charakter filtrujący, a SQL go nie wykonał to znaczy, że musi posiadać informację, iż wszystkie rekordy z tabeli Person.Address mają złączenie do tabeli Person.StateProvince. Taka informacja oczywiście istnieje i została zdefiniowana pomiędzy tymi tabelami w postaci relacji klucza obcego:

SELECT
	 FK.name AS ConstraintName
	,OBJECT_NAME(fkc.parent_object_id) AS TableName
	,C.name AS TableNameColumn
	,OBJECT_NAME (FK.referenced_object_id) AS FKTableName
	,rc.name AS FKColumnName
FROM sys.foreign_keys AS FK
JOIN sys.foreign_key_columns AS FKC
	ON fK.object_id = fKC.constraint_object_id
JOIN sys.columns AS C
	ON fkc.parent_object_id = C.object_ID
	AND fkc.parent_column_id = C.column_id
JOIN sys.columns AS RC
	ON FKC.referenced_object_id = RC.object_id
	AND FKC.referenced_column_id = RC.column_id
WHERE 
	fK.parent_object_id = OBJECT_ID('Person.Address');

Ze względu na fakt istnienia relacji klucza obcego optymalizator wie, że nie ma możliwości, iż w tabeli Address znajdzie się rekord, który nie ma złączenia do tabeli StateProvince więc całkowicie opuszcza takie sprawdzenie. Możemy dokonać jeszcze jednego sprawdzenia, które potwierdzi naszą tezę – wyłączmy sprawdzanie przy relację klucza obcego ( co w praktyce skutkowałoby możliwością wstawienia rekordu do Address bez złączenia do tabeli StateProvince):

ALTER TABLE [Person].[Address] NOCHECK CONSTRAINT FK_Address_StateProvince_StateProvinceID;
GO

Po tym działaniu możemy zauważyć, że wykonanie zapytania:

SELECT 
	 A.AddressLine1
	,A.City
FROM [Person].[Address] AS A
JOIN [Person].[StateProvince] AS SP
	ON SP.[StateProvinceID]=A.[StateProvinceID];
GO

skutkuje tym, że złączenie musi być faktycznie wykonane:

Wszystko opiera się oczywiście na odpowiednim zdefiniowaniu FOREIGN KEY Constraint. Przywróćmy poprzednie ustawienie i przejdźmy do kolejnych przykładów:

ALTER TABLE [Person].[Address] CHECK CONSTRAINT FK_Address_StateProvince_StateProvinceID;
GO

Sama składnia JOIN to nie jedyna możliwość na uzyskanie operatora złączenia na planie, analogiczną sytuację możemy uzyskać używając odpowiedniej konstrukcji WHERE i tam również sposób wykonania jest zależny od występowania relacji klucza obcego:

SELECT 
	 A.AddressLine1
	,A.City
FROM [Person].[Address] AS A
WHERE A.[StateProvinceID] IN
(SELECT [StateProvinceID] FROM [Person].[StateProvince]);
GO

Te i podobne konstrukcje zachowują się w zbliżony sposób i pozwolalają na eliminację operatora złączenia. Nie jest to jednak uniwersalne zachowanie i np. podczas używania operatorów takich jak INTERSECT czy EXCEPT optymalizator bez wykonania poniższego zapytania/złączenia nie jest w stanie dowiedzieć się, że poniższe zapytanie musi zwrócić pusty zbiór (ponieważ nie ma możliwości aby istniały wiersze w górnej tabeli, które nie występują w dolnej tabel):

SELECT 
	 [StateProvinceID]
FROM [Person].[Address]
EXCEPT
SELECT [StateProvinceID] 
FROM [Person].[StateProvince]
GO

Jak możecie zauważyć nie ma złotych rad i zasad i są przypadki kiedy NOT IN będzie działał “lepiej” niż np. EXCEPT i to jest w tym wszystkim najciekawsze.

Oczywiście to nie jedyne przypadki i jest cała gama przeróżnych scenariuszy gdzie SQL nie wykona jakiejś operacji ze względu na występujące constrainty lub specyficzny zapis w języku TSQL poniżej kilka z nich.

Wyobraźmy sobie zapis z warunkiem, który zawsze wykluczy wszystkie wiersze:

SELECT 
	 *
FROM [Person].[Address]
WHERE 1=2

ze względu na fakt, iż 1 nigdy nie będzie równe 2 SQL nawet nie próbował przeszukiwać tabeli co zobaczymy na planie zapytania:

Innym przypadkiem jest słowo kluczowe DISTINCT gdzie w SELECT wybieramy tylko kolumny, które są częścią unikalnego indeksu (np. poniżej kolumna AddressID jest częścią klucza głównego co gwarantuje jej unikalność):

SELECT DISTINCT
	 AddressID
FROM [Person].[Address]

na planie zapytania nie widzimy żadnego dodatkowego operatora, który usuwał by duplikaty ponieważ optymalizator “wie”, że wybrany zbiór danych jest unikalny:

Kolejny przykład również jest bardzo ciekawy i pokazuje, że w przypadku złączenia zewnętrznego np. LEFT JOIN jeśli używamy kolumn z tabeli “po lewej stronie” to operacja złączenia w ogóle nie jest wykonywana:

SELECT
	 A.*
FROM [Person].[Address] AS A
LEFT JOIN [Production].[ProductModel] AS PM
	ON A.AddressID=PM.ProductModelID

Powyższe złączenie nie ma żadnego sensu z biznesowego punktu widzenia ale chciałem pokazać, że eliminacja złączenia może nastąpić nawet wtedy gdy pomiędzy tabelami, które łączymy nie ma żadnych relacji czy też constraintów. Ważne jest to aby uzmysłowić sobie to, że powyższa zależność wystąpi tylko wtedy jeśli kolumny po których dokonamy złączenia są unikalne (czyli mają założony unikalny indeks zgrupowany lub niezgrupowany). Jeżeli optymalizator nie ma pewności co do unikalności to oczywiście musi dokonać odpowiednich operacji np. poniższe zapytanie opiera się na kolumnie AddressID z tabeli Person.Address co do której nie ma wątpliwości, że jest unikalna bo został na niej założony klucz główny oraz na polu TerritoryID z tabeli Sales.Customer co do której nie ma żadnych gwarancji o jej unikalności:

SELECT
	 A.*
FROM [Person].[Address] AS A
LEFT JOIN [Sales].[Customer] AS C
	ON A.AddressID=C.TerritoryID

W rezultacie otrzymaliśmy plan ze złączeniem z wykorzystaniem operatora Merge Join:

Przechodząc dalej chciałbym pokazać ostatni wybrany przeze mnie przypadek gdzie filtracja jest omijana ze względu na występujący CHECK_CONSTRAINT. W AdventureWorks mamy tabelę Person.Person na której mamy constrainta, który dopuszcza na kolumnie EmailPromotion jedynie wartości 0,1 lub 2:

ALTER TABLE [Person].[Person]  WITH CHECK ADD  CONSTRAINT [CK_Person_EmailPromotion] CHECK  (([EmailPromotion]>=(0) AND [EmailPromotion]<=(2)))
GO

ALTER TABLE [Person].[Person] CHECK CONSTRAINT [CK_Person_EmailPromotion]
GO

Co za tym idzie zapytanie, które będzie poszukiwać rekordów, które znajdują się poza zdefiniowanym zakresem bez wykonywania zwróci zero wierszy:

SELECT
	*
FROM [Person].[Person] 
WHERE EmailPromotion=3

Jak więc widać niektóre konstrukcje mogą znacząco wpłynąć na wydajność naszych zapytań. Tego typu przypadków jest naprawdę dużo, a ja przedstawiając kilka z nich chciałem zaznaczyć, iż coś takiego ma miejsce ponieważ uważam, że ważne jest aby mieć świadomość, że coś takiego istnieje i może mieć wpływ na wydajność naszych zapytań. Pozdrawiam serdecznie.