为什么这两个查询的性能如此不同? [英] Why are the performances of these 2 queries so different?

问题描述

我有一个使用全文索引搜索产品(250,000 行)的存储过程.

I have a stored proc that searches for products (250,000 rows) using a full text index.

存储过程采用一个参数，即全文搜索条件.这个参数可以为空，所以我添加了一个空检查，查询突然开始慢了几个数量级.

The stored proc takes a parameter that is the full text search condition. This parameter can be null, so I added a null check and the query suddenly started running orders of magnitude slower.

-- 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

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

I must admit that I am not very familiar with execution plans. The only obvious difference to me is that the joins are different. I would try adding a hint but having no join in my query I am not sure how to do that.

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

I also do not quite understand why the index called IX_SectionID is used, since it is an index that only contains the column SectionID and that column is not used anywhere.

推荐答案

OR 会影响性能，所以这样做:

OR can crush performance, so do it this way:

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 - 条件查询.

Look at this article: Dynamic Search Conditions in T-SQL by Erland Sommarskog and this question: SQL Server 2008 - Conditional Query.

这篇关于为什么这两个查询的性能如此不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！