SQLGEEK

Msg 106, Level 15, State 1, Line 2
Too many table names in the query. The maximum allowable is 256.

A więc sprawa jasna - SQL Server w jednym zapytaniu więcej niż 256 tabel nie przyjmie. Ciekawość zaspokojona. W BOL piszą prawdę :-)

Postanowiłem wykorzystać stworzone już tabele do dalszych testów. Skróciłem zapytanie ze skryptu 3. o jeden JOIN (czyli teraz mam złączenie 256 tabel). Uruchomiłem zmodyfikowany skrypt i po ponad 30 sekundach dostałem oczekiwaną odpowiedź - jeden wiersz. Plan wykonania zawierał oczywiście same skany, ale nie to było najciekawsze. Optymalizator przy tak dużej liczbie łączonych tabel pogubił się chyba w statystykach, bo dla maleńkich tabel użył złączeń typu HASH JOIN (ten typ złączenia jest wykorzstywany do łączenia dużych zbiorów danych). Poniżej fragment graficznej reprezentacji planu wykonania.

Warto zwrócić uwagę, że tabela table1 została złączona z tabelą table256, jako że optymalizator nie był zmuszony do zachowania kolejności złączeń.

Uruchomiłem to samo zapytanie drugi raz, by sprawdzić, czy coś się zmieni i... natychmiast otrzymałem wynik. Plan wykonania został skompilowany i umieszczony w cache'u, dzięki czemu drugi raz optymalizator zapytań nie musiał się męczyć z jego układaniem. Słowo "męczyć" jest tu zdecydowanie na miejscu, bo samo zapytanie wykonuje się w mgnieniu oka, ale układanie planu trwa ponad pół minuty! Optymalizator musiał przecież zajrzeć do tabel, do statystyk, poszukać indeksów... Natłok pracy! I niech mi ktoś powie, że kompilacja planu wykonania nie może być kosztowna. Niestety finalnie, mówiąc potocznie, optymalizator "strzelił samobója", bo - choć myślał długo - plan wykonania z HASH JOINami nie jest tym, co dla tak małych tabel jest najlepsze. Postanowiłem więc pójść dalej z moimi testami.

Do zapytania łączącego 256 tabel dodałem (poprzez użycie okna Replace w Management Studio) w każdym INNER JOIN hint LOOP. Dzięki temu zmuszam optymalizator do wykonania innego rodzaju złączenia (właśnie LOOP), a jednocześnie wymuszam kolejność tabel w złączeniach (będzie taka, w jakiej tabele występują w zapytaniu).

Skrypt 4. Złączenie 256 tabel z użyciem hinta LOOP

USE tempdb
GO

SELECT table1.id
FROM table1
INNER LOOP JOIN table2
ON table1.id = table2.id
INNER LOOP JOIN table3
ON table2.id = table3.id
...
INNER LOOP JOIN table256
ON table255.id = table256.id

Uruchomiłem powyższy skrypt i mimo, że planu takiego zapytania nie było w cache'u, rezultat otrzymałem już po 1 sekundzie. W porównaniu do 34 sekund w przypadku użycia przez optymalizator złączeń typu HASH JOIN jest to wynik znakomity. Oczywiście stało się tak dlatego, że dopisując hint, oszczędziłem optymalizatorowi "myślenia" - właściwie dostał on ode mnie plan wykonania w samym zapytaniu. Oczywiście, plan wykonania zapytania zmienił się - nadal występowały skanowania tabel, ale złączenia typy HASH JOIN zostały zastąpione przez właściwsze w tym przypadku LOOP JOIN, zaś kolejność tabel w złączeniach była taka, jak w kodzie zapytania. Poniżej fragment tego planu wykonania.

I niech ktoś powie, że za hinty obcinamy rączki :-)

Czy jednak nie można pomóc optymalizatorowi w inny sposób? Spróbowałem inaczej. Usunąłem tabele i stworzyłem je na nowo - tym razem z indeksami zgrupowanymi (ang. clustered index). Patrz kod poniżej.

Skrypt 5. Tworzenie tabel z indeksami do testu

USE tempdb
GO

SET NOCOUNT ON

DECLARE @i INT, @max INT
DECLARE @stmt NVARCHAR(4000)
SELECT @i = 1
WHILE @i <= 256
BEGIN
  SET @stmt = ''
  SET @stmt = N'IF (OBJECT_ID(''table' +
              CAST(@i AS NVARCHAR(4)) +
              N''')) IS NOT NULL DROP TABLE table' +
              CAST(@i AS NVARCHAR(4)) + N'; '
  SET @stmt = @stmt + N'CREATE TABLE table' +
              CAST(@i AS NVARCHAR(4)) +
              N'(id int identity(1,1) PRIMARY KEY); '
  SET @stmt = @stmt + N'INSERT INTO table' +
              CAST(@i AS NVARCHAR(4)) +
              N' DEFAULT VALUES'
  EXEC(@stmt)
  SET @i = @i + 1
END

Na tak stworzonych tabelach ponownie uruchomiłem złączenie 256 tabel (bez hinta LOOP). Tym razem kompilacja planu zajęła około 20 sekund. Ale plan wykonania był zgoła inny niż w przypadku zapytania do tabel bez indeksów.

To, co w powyższym planie może zastanawiać, to fakt, że pierwsza tabela z zapytania została przeskanowana, a w każdej następnej było już dokonywane wyszukiwanie danych w indeksie. Wygląda na to, że optymalizator uznał skanowanie indeksu w pierwszej tabeli za najtańszą metodę dostępu (i miał rację, dla jednego rekordu nie warto wyszukiwać; dla większej liczby rekordów w tej tabeli i przy podanych warunkach wyszukiwania w klauzuli WHERE zapewne użyłby wyszukiwania w indeksie). Dla następnych tabel przeszukiwanie indeksu jest już zrozumiałe. Jeżeli z pierwszej tabeli zapytanie wybiera jeden rekord (jedną wartość w kolumnie, po której następuje złączenie), to dalsza część zapytania polega na wyszukiwaniu jednej wartości w każdej tabeli. Natomiast warto zwrócić uwagę na to, że tym razem optymalizator wybrał dobry rodzaj złączeń. Dobry rodzaj złączeń plus operacje przeszukiwania tabel skłaniają mnie do stwierdzenia, że samo stworzenie odpowiedniego indeksu w każdej z tabel jest krokiem naprzód w procesie optymalizacji zapytań o takim stopniu złożoności.

Podsumowując - 256 to rzeczywiście maksymalna liczba tabel obsługiwanych w SQL Server 2005 przez pojedyncze zapytanie, kompilacja planu wykonania bardzo często trwa dłużej od samej realizacji gotowego planu, optymalizacja za pomocą indeksów i hintów może przynieść bardzo dobre rezultaty.

Na koniec coś, z czym możesz się spotkać w środowisku Management Studio, jeżeli spróbujesz podobnego testu. Management Studio często nie potrafi poradzić sobie z wizualizacją planów wykonania tak złożonych zapytań, jak te, które pojawiły się w moim teście. Jeżeli plan będzie zbyt złożony, to próba przemieszczania się po arkuszu planu za pomocą mini-mapy w prawym dolnym rogu ekranu może zakończyć się czymś takim:

Nie wspominam już o tym, że o wyświetleniu tooltipa (przy najechaniu na górną część graficznego planu wykonania w Management Studio) z treścią zapytania łączącego 256 tabel nawet nie myśl :-)