Wskaźniki wydajności ON i GDZIE

Mam dwa stoliki

@T1 TABLE
(
    Id INT,
    Date DATETIME
)

@T2 TABLE
(
    Id INT,
    Date DATETIME
)

Te tabele mają indeks nieklastrowany (Id, Data)

I dołączam do tych tabel

SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON 
    t1.Id = t2.Id
WHERE 
    t1.Date <= GETDATE()
    AND
    t2.Date <= GETDATE()

Można to również zapisać jako

SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON 
    t1.Id = t2.Id
    AND
    t1.Date <= GETDATE()
    AND
    t2.Date <= GETDATE()

Moje pytanie brzmi, które z tych dwóch zapytań daje lepszą wydajność i dlaczego? A może są równi?

Wydajność będzie taka sama. Optymalizator rozpozna to i utworzy ten sam plan.

Z drugiej strony nie powiedziałbym, że są równi. Pierwsza forma pytania jest znacznie bardziej czytelna i ogólnie oczekiwana.

Na przykład przy użyciu niektórych tabel, które mam pod ręką, możesz zobaczyć, że plan wykonania jest dokładnie taki sam, bez względu na to, jak piszę zapytanie.

Powinieneś być w stanie określić plany zapytań dla własnych tabel i zestawu danych, abyś mógł zobaczyć, co dzieje się w twojej sytuacji.

SELECT * FROM salestable , custtable 
WHERE salestable.custaccount = custtable.accountnum 
AND salestable.dataareaid = custtable.dataareaid

SELECT * FROM salestable 
JOIN  custtable 
ON salestable.custaccount = custtable.accountnum 
AND salestable.dataareaid = custtable.dataareaid

SELECT * FROM salestable JOIN custtable 
ON salestable.custaccount = custtable.accountnum 
WHERE salestable.dataareaid = custtable.dataareaid

Daje te plany wykonania

Są semantycznie identyczne, a optymalizator nie powinien mieć problemów z rozpoznaniem tego faktu i wygenerowaniem identycznych planów.

Zazwyczaj umieszczam warunki odnoszące się do obu tabel w ONi warunki odnoszące się tylko do jednej tabeli w WHERE.

Jednak OUTER JOINSprzenoszenie warunków może mieć wpływ na semantykę.

W prostych przypadkach będzie tak samo. Widziałem jednak, że bardzo złożone zapytania z kilkoma połączeniami mają znacząco różne plany. Ostatni, nad którym pracowałem, rozpoczął się od stołu, który ma prawie 6 milionów wierszy połączonych z około 20 różnymi stołami. Tylko pierwsze połączenie w tym stole było złączeniem wewnętrznym , wszystkie pozostałe pozostały złączeniami zewnętrznymi. Filtr w klauzuli where został sparametryzowany mniej więcej tak:

WHERE table1.begindate >= @startdate AND table1.enddate < @enddate 

Ten filtr został użyty później w planie zamiast wcześniej. Kiedy przeniosłem te warunki do pierwszego połączenia wewnętrznego, plan zmienił się diametralnie, ponieważ filtr został zastosowany na początku planu, aby ograniczyć zestaw wyników, a mój procesor i upływający czas spadły o około 310%. Tak jak w przypadku wielu pytań dotyczących programu SQL Server, zależy to.

Ogólnie rzecz biorąc, umieszczenie filtrów robi różnicę.
Podczas gdy Tom V twierdzi, że Optymalizator rozpozna, że ​​zapytania są takie same i opracują ten sam plan, nie zawsze jest to prawda. Zależy to od używanej wersji SQL, stopnia złożoności zapytania i tego, jak ważne dla całej partii jest to, które optymalizator określa.

Optymalizator może zdecydować, że ta część partii nie jest warta poświęcenia wystarczającej ilości czasu, aby opracować najlepszy plan. Zasadniczo osiągniesz lepszą wydajność, jeśli umieścisz warunki zmniejszające ilość danych, na których kwerenda będzie musiała pracować w klauzuli ON zamiast klauzuli WHERE (jeśli to możliwe, ponieważ wykonanie tego z zewnętrznym złączeniem spowoduje produkt kartezjański .)

Od czasu do czasu programistom SQL jest trochę łatwiej dostrzec filtry w klauzuli WHERE, ale pracowałem nad niektórymi dużymi tabelami, w których filtry w klauzuli ON skracają godziny pracy.

Więc jeśli klauzula może drastycznie zmniejszyć liczbę wierszy, które będzie czytać zapytanie, zawsze umieszczam ją w klauzuli ON, aby pomóc Optymalizatorowi wybrać lepszy plan.

W zwykłych okolicznościach warunki filtrowania można określić w klauzulach WHERE lub JOIN. Zazwyczaj umieszczam filtry pod GDZIE, chyba że można wpłynąć na pierwszeństwo ZEWNĘTRZNEGO ŁĄCZENIA (patrz poniżej) lub jeśli filtr jest bardzo specyficzny dla tej tabeli (np. TYP = 12, aby określić konkretny podzbiór wierszy w tabeli).

Z drugiej strony zarówno klauzule ON, jak i WHERE mogą być użyte do określenia warunków łączenia (w przeciwieństwie do warunków filtrowania). Tak długo, jak korzystasz tylko z połączeń INNER, nadal nie będzie miało znaczenia, którego używasz w zwykłych okolicznościach.

Jeśli jednak korzystasz z połączeń OUTER, może to mieć duże znaczenie. Jeśli na przykład podasz ZŁĄCZE ZEWNĘTRZNE między dwiema tabelami (t1 i t2), ale następnie w klauzuli WHERE przejdź do określenia relacji eqijoin między tabelami (np. T1.col = t2.col), masz właśnie zamieniłem złączenie OUTER na złączenie INNER! Wynika to z tego, że GDZIE można użyć do określenia ekwiwalentu (a może nawet połączenia OUTER, w zależności od wersji, używając przestarzałej składni * =) bez użycia klauzuli ON, a gdy GDZIE wskazuje na wewnętrzną równowagę między tabelami, zastępuje OUTER DOŁĄCZ (jeśli jest obecny).

Pierwotne pytanie dotyczyło filtrów, w których typ sprzężenia często nie powinien stanowić problemu, ale sprzężenie może również działać jak filtr, a w takich sytuacjach położenie warunku złączenia z pewnością może mieć znaczenie.

W przypadku INNER JOIN jest to kwestia stylu.

Jednak staje się o wiele bardziej interesujący dzięki ZEWNĘTRZNYM DOŁĄCZENIU. Powinieneś zbadać różnice między zapytaniami z OUTER JOIN a warunkami zarówno w klauzuli ON, jak i WHERE. Zestaw wyników nie zawsze jest taki sam. Jest na przykład

OUTER JOIN dbo.x ON a.ID = x.ID ... WHERE x.SomeField IS NOT NULL

taki sam jak

INNER JOIN dbo.x ON a.ID = x.ID AND x.SomeField IS NOT NULL