I have a double request for self-join, when performance deteriorates greatly, when search values are reversed.
select
fooA.ID
, fooB.ID
from
foo AS fooA
INNER JOIN bar AS barA ON fooA.barID = barA.barID
INNER JOIN foo AS fooB ON fooA.fooID = fooB.fooID
INNER JOIN bar AS barB ON fooB.barID = barB.barID
where
barA.value = 'xyz'
AND barB.value = '60'
select
fooA.ID
, fooB.ID
from
foo AS fooA
INNER JOIN bar AS barA ON fooA.barID = barA.barID
INNER JOIN foo AS fooB ON fooA.fooID = fooB.fooID
INNER JOIN bar AS barB ON fooB.barID = barB.barID
where
barA.value = '60'
AND barB.value = 'xyz'
- The value "xyz" is indicated 150,000 times in the table "bar".
- The value "60" is indicated in the table "500" 500 times.
- Query plans are the same, except that the inner loop returns 150,000 rows or 500 rows, depending on what search value is listed.
- The search searches for non-clustered indexes.
- Statistics has been updated in both tables using FULLSCAN.
Why does the SQL query optimizer not correctly determine that in both cases the innermost join of the query plan should be with the least number of rows?