Dlaczego optymalizacja nie wybiera wyszukiwania
TL: DR Rozszerzona definicja kolumny obliczeniowej zakłóca zdolność optymalizatora do zmiany kolejności połączeń na początku. Przy innym punkcie początkowym optymalizacja oparta na kosztach przebiega przez optymalizator inną ścieżką i kończy się na innym ostatecznym wyborze planu.
Detale
W przypadku wszystkich najprostszych zapytań optymalizator nie próbuje zbadać niczego takiego jak cała przestrzeń możliwych planów. Zamiast tego wybiera rozsądnie wyglądający punkt początkowy , a następnie poświęca zaplanowaną ilość wysiłku na badanie logicznych i fizycznych wariantów, w jednej lub kilku fazach wyszukiwania, aż do znalezienia rozsądnego planu.
Głównym powodem, dla którego otrzymujesz różne plany (z różnymi szacunkowymi kosztami końcowymi) dla tych dwóch przypadków, są różne punkty początkowe. Zaczynając od innego miejsca, optymalizacja kończy się w innym miejscu (po ograniczonej liczbie iteracji eksploracji i implementacji). Mam nadzieję, że jest to dość intuicyjne.
Punkt początkowy, o którym wspomniałem, opiera się w pewnym stopniu na tekstowej reprezentacji zapytania, ale wprowadza się zmiany w wewnętrznej reprezentacji drzewa, gdy przechodzi ona przez etapy analizy, wiązania, normalizacji i uproszczenia kompilacji zapytania.
Co ważne, dokładny punkt początkowy zależy w dużej mierze od początkowej kolejności łączenia wybranej przez optymalizator. Wyboru tego dokonuje się przed załadowaniem statystyk i przed uzyskaniem jakichkolwiek oszacowań liczności. Łączna liczność (liczba wierszy) w każdej tabeli jest jednak znana, uzyskana z metadanych systemowych.
Wstępne porządkowanie złączeń jest zatem oparte na heurystyce . Na przykład optymalizator próbuje przepisać drzewo tak, aby mniejsze tabele były łączone przed większymi, a połączenia wewnętrzne występowały przed złączeniami zewnętrznymi (i połączeniami krzyżowymi).
Obecność kolumny obliczeniowej zakłóca ten proces, szczególnie zdolność optymalizatora do wypychania złączeń zewnętrznych w dół drzewa zapytań. Wynika to z faktu, że kolumna obliczeniowa jest rozszerzana do wyrażenia leżącego u jej podstaw, zanim nastąpi zmiana kolejności łączenia, a przeniesienie połączenia poza złożone wyrażenie jest znacznie trudniejsze niż przeniesienie go poza proste odwołanie do kolumny.
Drzewa, których to dotyczy, są dość duże, ale dla zilustrowania, niepoliczone drzewo początkowych zapytań kolumnowych zaczyna się od: (zwróć uwagę na dwa zewnętrzne sprzężenia u góry)
LogOp_Select
LogOp_Apply (x_jtLeftOuter)
LogOp_LeftOuterJoin
LogOp_NAryJoin
LogOp_LeftAntiSemiJoin
LogOp_NAryJoin
LogOp_Get TBL: dbo.table1 (alias TBL: a4)
LogOp_Select
LogOp_Get TBL: dbo.table6 (alias TBL: a3)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a3] .col18
ScaOp_Const TI (zestawienie varchar 53256, Var, Trim, ML = 16)
LogOp_Select
LogOp_Get TBL: dbo.table1 (alias TBL: a1)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a1] .col2
ScaOp_Const TI (zestawienie varchar 53256, Var, Trim, ML = 16)
LogOp_Select
LogOp_Get TBL: dbo.table5 (alias TBL: a2)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a2] .col2
ScaOp_Const TI (zestawienie varchar 53256, Var, Trim, ML = 16)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a4] .col2
ScaOp_Identifier QCOL: [a3] .col19
LogOp_Select
LogOp_Get TBL: dbo.table7 (alias TBL: a7)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a7] .col22
ScaOp_Const TI (zestawienie varchar 53256, Var, Trim, ML = 16)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a4] .col2
ScaOp_Identifier QCOL: [a7] .col23
LogOp_Select
LogOp_Get TBL: table1 (alias TBL: cdc)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [cdc] .col6
ScaOp_Const TI (smallint, ML = 2) XVAR (smallint, Not Owned, Value = 4)
LogOp_Get TBL: dbo.table5 (alias TBL: a5)
LogOp_Get TBL: table2 (alias TBL: cdt)
ScaOp_Logical x_lopAnd
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a5] .col2
ScaOp_Identifier QCOL: [cdc] .col2
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a4] .col2
ScaOp_Identifier QCOL: [cdc] .col2
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [cdt] .col1
ScaOp_Identifier QCOL: [cdc] .col1
LogOp_Get TBL: table3 (alias TBL: ahcr)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [ahcr] .col9
ScaOp_Identifier QCOL: [cdt] .col1
Ten sam fragment kwerendy obliczanej kolumny : (zwróć uwagę na połączenie zewnętrzne znacznie niżej, rozszerzoną definicję kolumny obliczeniowej i kilka innych subtelnych różnic w (wewnętrznej) kolejności łączenia)
LogOp_Select
LogOp_Apply (x_jtLeftOuter)
LogOp_NAryJoin
LogOp_LeftAntiSemiJoin
LogOp_NAryJoin
LogOp_Get TBL: dbo.table1 (alias TBL: a4)
LogOp_Select
LogOp_Get TBL: dbo.table6 (alias TBL: a3)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a3] .col18
ScaOp_Const TI (zestawienie varchar 53256, Var, Trim, ML = 16)
LogOp_Select
LogOp_Get TBL: dbo.table1 (alias TBL: a1
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a1] .col2
ScaOp_Const TI (zestawienie varchar 53256, Var, Trim, ML = 16)
LogOp_Select
LogOp_Get TBL: dbo.table5 (alias TBL: a2)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a2] .col2
ScaOp_Const TI (zestawienie varchar 53256, Var, Trim, ML = 16)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a4] .col2
ScaOp_Identifier QCOL: [a3] .col19
LogOp_Select
LogOp_Get TBL: dbo.table7 (alias TBL: a7)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a7] .col22
ScaOp_Const TI (zestawienie varchar 53256, Var, Trim, ML = 16)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a4] .col2
ScaOp_Identifier QCOL: [a7] .col23
LogOp_Project
LogOp_LeftOuterJoin
LogOp_Join
LogOp_Select
LogOp_Get TBL: table1 (alias TBL: cdc)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [cdc] .col6
ScaOp_Const TI (smallint, ML = 2) XVAR (smallint, Not Owned, Value = 4)
LogOp_Get TBL: table2 (alias TBL: cdt)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [cdc] .col1
ScaOp_Identifier QCOL: [cdt] .col1
LogOp_Get TBL: table3 (alias TBL: ahcr)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [ahcr] .col9
ScaOp_Identifier QCOL: [cdt] .col1
AncOp_PrjList
AncOp_PrjEl QCOL: [cdc] .col7
ScaOp_Convert char collate 53256, Null, Trim, ML = 6
ScaOp_IIF varchar zestawić 53256, Null, Var, Trim, ML = 6
ScaOp_Comp x_cmpEq
ScaOp_Intrinsic isnumeric
ScaOp_Intrinsic right
ScaOp_Identifier QCOL: [cdc] .col4
ScaOp_Const TI (int, ML = 4) XVAR (int, Not Owned, Value = 4)
ScaOp_Const TI (int, ML = 4) XVAR (int, Not Owned, Value = 0)
ScaOp_Const TI (zestawienie varchar 53256, Var, Trim, ML = 1) XVAR (varchar, Owned, Value = Len, Data = (0,))
Podciąg ScaOp_Intrinsic
ScaOp_Const TI (int, ML = 4) XVAR (int, Not Owned, Value = 6)
ScaOp_Const TI (int, ML = 4) XVAR (int, Not Owned, Value = 1)
ScaOp_Identifier QCOL: [cdc] .col4
LogOp_Get TBL: dbo.table5 (alias TBL: a5)
ScaOp_Logical x_lopAnd
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a5] .col2
ScaOp_Identifier QCOL: [cdc] .col2
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [a4] .col2
ScaOp_Identifier QCOL: [cdc] .col2
Statystyki są ładowane i wstępne oszacowanie liczności jest wykonywane na drzewie tuż po ustawieniu początkowej kolejności łączenia. Łączenie w różnych zamówieniach również wpływa na te szacunki, a zatem ma efekt domina podczas późniejszej optymalizacji opartej na kosztach.
Wreszcie w przypadku tej sekcji zablokowanie połączenia zewnętrznego pośrodku drzewa może uniemożliwić dalsze dopasowanie reguł zmiany kolejności łączenia podczas optymalizacji opartej na kosztach.
Korzystanie z przewodnika po planach (lub równoważnie USE PLANpodpowiedź - przykład dla Twojego zapytania ) zmienia strategię wyszukiwania na podejście bardziej zorientowane na cel, kierując się ogólnym kształtem i funkcjami dostarczonego szablonu. To wyjaśnia, dlaczego optymalizator może znaleźć ten sam table1plan wyszukiwania zarówno dla obliczonych, jak i nieobliczonych schematów kolumn, gdy używany jest przewodnik po planach lub podpowiedź.
Czy możemy zrobić coś inaczej, aby wyszukiwanie się stało
Jest to coś, o co musisz się martwić tylko wtedy, gdy optymalizator nie znajdzie planu o akceptowalnej charakterystyce wydajności.
Wszystkie normalne narzędzia do strojenia są potencjalnie przydatne. Możesz na przykład podzielić zapytanie na prostsze części, przejrzeć i ulepszyć dostępne indeksowanie, zaktualizować lub utworzyć nowe statystyki ... i tak dalej.
Wszystkie te rzeczy mogą wpływać na oszacowania liczności, ścieżkę kodu przeprowadzaną przez optymalizator i subtelnie wpływać na decyzje oparte na kosztach.
Możesz ostatecznie skorzystać z podpowiedzi (lub przewodnika po planie), ale zwykle nie jest to idealne rozwiązanie.
Dodatkowe pytania z komentarzy
Zgadzam się, że najlepiej jest uprościć zapytanie itp., Ale czy istnieje sposób (flaga śledzenia), aby optymalizator kontynuował optymalizację i osiągnął ten sam wynik?
Nie, nie ma flagi śledzenia do przeprowadzenia wyczerpującego wyszukiwania, a ty jej nie chcesz. Możliwa przestrzeń poszukiwań jest ogromna, a czasy kompilacji przekraczające wiek wszechświata nie byłyby dobrze odbierane. Ponadto optymalizator nie zna każdej możliwej transformacji logicznej (nikt tego nie robi).
Ponadto, dlaczego potrzebne jest złożone rozszerzenie, ponieważ kolumna jest utrwalana? Dlaczego optymalizator nie może uniknąć rozwinięcia go, traktować go jak zwykłą kolumnę i osiągnąć ten sam punkt początkowy?
Kolumny obliczeniowe są rozwijane (podobnie jak widoki), aby umożliwić dodatkowe możliwości optymalizacji. Rozwinięcie można dopasować z powrotem do np. Utrwalonej kolumny lub indeksu na późniejszym etapie procesu, ale dzieje się tak po ustaleniu początkowej kolejności łączenia .
