SQL 查询性能的降低取决于搜索值的顺序

Question

我有一个双重自连接查询，当交换搜索值时，性能会严重下降。

-- 500,000 i/o & 500ms execution
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 -- self join
  INNER JOIN bar AS barB ON fooB.barID = barB.barID
where
  barA.value = 'xyz'
  AND barB.value = '60'

-- 5,000 i/o & 5ms execution
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 -- self join
  INNER JOIN bar AS barB ON fooB.barID = barB.barID
where
  barA.value = '60'
  AND barB.value = 'xyz'

• 值“xyz”在“bar”表中列出了 150,000 次。
• 值“60”在“bar”表中列出了 500 次。
• 查询计划是相同的，只是最内层循环返回 150,000 行或 500 行，具体取决于首先列出的搜索值。
• 搜索对非聚集索引执行查找。
• 两个表的统计数据均已使用 FULLSCAN 更新。

为什么 SQL 查询优化器不能正确识别在这两种情况下查询计划的最内层联接应该是行数最少的联接？

Answer 1

SQL Server 不会在每次执行查询时重新编译查询。它执行一次，并针对第一次看到的确切值进行优化。我猜你缓存了一个不幸的计划。

尝试在末尾添加OPTION (RECOMPILE)。