Dlaczego plan wykonania zapytania SELECT COUNT () obejmuje dołączoną do lewej tabelę?

W SQL Server 2012 mam funkcję wycenianą tabelą z dołączeniem do innej tabeli muszę policzyć liczbę wierszy dla tej „funkcji wycenianej tabelą”. Kiedy sprawdzam plan wykonania, widzę lewą tabelę łączenia. Dlaczego? Jak lewy połączony stół może wpływać na liczbę zwracanych wierszy? Spodziewałbym się, że silnik db nie musi oceniać lewej tabeli wspólnej w zapytaniu SELECT count (..).

Select count(realtyId) FROM [dbo].[GetFilteredRealtyFulltext]('"praha"')

Plan wykonania:

Funkcja ceniona w tabeli:

CREATE FUNCTION [dbo].[GetFilteredRealtyFulltext]
(@criteria nvarchar(4000))
RETURNS TABLE
AS
RETURN (SELECT 
realty.Id AS realtyId,
realty.OwnerId,
realty.Caption AS realtyCaption,
realty.BusinessCategory,
realty.Created,
realty.LastChanged,
realty.LastChangedType,
realty.Price,
realty.Pricing,
realty.PriceCurrency,
realty.PriceNote,
realty.PricePlus,
realty.OfferState,
realty.OrderCode,
realty.PublishAddress,
realty.PublishMap,
realty.AreaLand,
realty.AreaCover,
realty.AreaFloor,
realty.Views,
realty.TopPoints,
realty.Radius,
COALESCE(realty.Wgs84X, ruian_cobce.Wgs84X, ruian_obec.Wgs84X) as Wgs84X,
COALESCE(realty.Wgs84Y, ruian_cobce.Wgs84Y, ruian_obec.Wgs84Y) as Wgs84Y,
realty.krajId,
realty.okresId,
realty.obecId,
realty.cobceId,
IsNull(CONVERT(int,realty.Ranking),0) as Ranking,

realty.energy_efficiency_rating,
realty.energy_performance_attachment,
realty.energy_performance_certificate,
realty.energy_performance_summary,

Category.Id AS CategoryId,
Category.ParentCategoryId,
Category.WholeName,
okres.nazev AS okres,
ruian_obec.nazev AS obec,
ruian_cobce.nazev AS cobce,
ExternFile.ServerPath,
Person.ParentPersonId,
( COALESCE(ftR.Rank,0) + COALESCE(ftObec.Rank,0) + COALESCE(ftOkres.Rank,0) + COALESCE(ftpobvod.Rank,0)) AS FtRank

FROM realty
JOIN Category ON realty.CategoryId = Category.Id
LEFT JOIN ruian_cobce ON realty.cobceId = ruian_cobce.cobce_kod
LEFT JOIN ruian_obec ON realty.obecId = ruian_obec.obec_kod
LEFT JOIN okres ON realty.okresId = okres.okres_kod
LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1 AND ExternFile.ForeignTable = 5
INNER JOIN Person ON realty.OwnerId = Person.Id
Left JOIN CONTAINSTABLE(Realty, *, @criteria) ftR ON realty.Id = ftR.[Key] 
Left JOIN CONTAINSTABLE(ruian_obec, *, @criteria) ftObec ON realty.obecId = ftObec.[Key] 
Left JOIN CONTAINSTABLE(Okres, *, @criteria) ftOkres ON realty.okresId = ftOkres.[Key]
Left JOIN CONTAINSTABLE(pobvod, *, @criteria) ftpobvod ON realty.pobvodId = ftpobvod.[Key]
WHERE Person.ConfirmStatus = 1
AND ( COALESCE(ftR.Rank,0) + COALESCE(ftObec.Rank,0) + COALESCE(ftOkres.Rank,0) + COALESCE(ftpobvod.Rank,0))  > 0
)

AKTUALIZACJA:

Dodaję unikalny indeks, aby podążać za ideą Roba Farleya:

 Create unique nonclustered index ExternFileIsMainUnique ON ExternFile(ForeignId) WHERE IsMain = 1 AND ForeignTable = 5

I indeksowane przez DB Engine:

CREATE NONCLUSTERED INDEX [RealtyOwnerLocation] ON [dbo].[Realty]

([OwnerId] ASC) INCLUDE ([Id], [okresId], [obecId], [pobvodId]) GO

Dla uproszczenia usuwam warunek

WHERE Person.ConfirmStatus = 1

z funkcji o wartościach przedstawionych powyżej.

Teraz plan wykonania jest znacznie prostszy, ale nadal dotyka tabeli ExternFile:

Może serwer SQL nie jest wystarczająco sprytny?

sql-server execution-plan count

— Tomas Kubes
źródło

Jeśli ForeignId, ForeignTable, IsMainnie wiadomo *, że jest unikalny ExternFile, wówczas QO będzie musiała uwzględnić tę tabelę, aby obliczyć liczbę. Za każdym razem, gdy pasuje wiele wierszy, liczba będzie miała wpływ.

Dołącz do uproszczenia w
projektowaniu SQL Server dla uproszczenia (nagrywanie SQLBits)

_{* Optymalizator nie rozpoznaje obecnie filtrowanych unikalnych indeksów jako unikalnych}

AKTUALIZACJA (wg OP) : Rozwiązaniem jest zmiana wiersza w zapytaniu z LEWEGO DOŁĄCZENIA (które może produkować wiele wierszy):

LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1 AND ExternFile.ForeignTable = 5

ZEWNĘTRZNIE STOSOWAĆ za pomocą TOP (które dają jeden rząd i nie wpływają na COUNT)

OUTER APPLY (SELECT TOP (1) ServerPath FROM ExternFile WHERE ForeignId = realty.Id AND IsMain = 1 AND ForeignTable = 5) AS ExternFile

Zapytanie jest teraz bardziej skuteczne. Nie można było dodać unikalnego indeksu, ponieważ wartości nie były unikalne, były one unikalne tylko dla kombinacji w stanie i nie jest to uważane za unikalne, jak wspomniano powyżej.

— Rob Farley
źródło