为什么这两个查询的性能如此不同？

Question

我有一个使用全文索引搜索产品（250,000 行）的存储过程。

存储过程采用一个参数，即全文搜索条件。该参数可以为空，因此我添加了空检查，查询突然开始运行速度减慢了几个数量级。

-- This is normally a parameter of my stored proc
DECLARE @Filter VARCHAR(100)
SET @Filter = 'FORMSOF(INFLECTIONAL, robe)'

-- #1 - Runs < 1 sec
SELECT TOP 100 ID FROM dbo.Products
WHERE CONTAINS(Name, @Filter)

-- #2 - Runs in 18 secs
SELECT TOP 100 ID FROM dbo.Products
WHERE @Filter IS NULL OR CONTAINS(Name, @Filter)

以下是执行计划：

查询#1

查询 #2

我必须承认我对执行计划不是很熟悉。对我来说唯一明显的区别是连接不同。我会尝试添加提示，但在我的查询中没有加入，我不知道该怎么做。

我也不太明白为什么使用名为 IX_SectionID 的索引，因为它是一个仅包含列 SectionID 的索引，并且该列在任何地方都没有使用。

Answer 1

OR 会降低性能，所以这样做：

DECLARE @Filter VARCHAR(100)
SET @Filter = 'FORMSOF(INFLECTIONAL, robe)'

IF @Filter IS NOT NULL
BEGIN
    SELECT TOP 100 ID FROM dbo.Products
    WHERE CONTAINS(Name, @Filter)
END
ELSE
BEGIN
    SELECT TOP 100 ID FROM dbo.Products
END

看看这篇文章：Erland Sommarskog 的 T-SQL 中的动态搜索条件 和这个问题：SQL Server 2008 -条件查询。

Answer 2

第一个查询计划看起来很简单：

1. 全文搜索来解析 CONTAINS(Name, @Filter)
2. 索引扫描来查找匹配行的其他列
3. 使用散列连接将两者结合起来

连接运算符形成两个记录集的并集。所以看起来第二个查询正在执行：

1. 索引扫描（稍后用于查找其他列）
2. 和持续扫描。我假设它将您的查询视为未参数化，因此查询计划不必适用于 @Filter 的任何其他值。如果正确，持续扫描将解析@Filter is not null。
3. 解析 CONTAINS(Name, @Filter) 的全文搜索
4. 将 3 的结果与 2 中的空集联合起来 循环连接
5. 1 和 4 的结果以查找其他列

哈希连接会消耗内存为了速度；如果您的系统有足够的内存，它比循环连接快得多。这可以轻松解释 10-100 倍的减速。

一种修复方法是使用两个不同的查询：

if @Filter is null
    SELECT TOP 100 ID FROM dbo.Products
else
    SELECT TOP 100 ID FROM dbo.Products WHERE CONTAINS(Name, @Filter)

Answer 3

您引入了 OR 条件。
在大多数情况下，与您的方法相比，显式检查 NULL 并执行一个查询要快得多。

例如尝试这个：

IF @Filter IS NULL
 BEGIN
SELECT TOP 100 ID FROM dbo.Products
END
ELSE
BEGIN
SELECT TOP 100 ID FROM dbo.Products
WHERE @Filter CONTAINS(Name, @Filter)
END