可选参数，“索引查找"，即“索引查找".计划 [英] Optional parameters, "index seek" plan

问题描述

在我的SELECT语句中，我以这种方式使用可选参数:

In my SELECT statement i use optional parameters in a way like this:

DECLARE @p1 INT = 1  
DECLARE @p2 INT = 1  
SELECT name FROM some_table WHERE (id = @p1 OR @p1 IS NULL) AND (name = @p2 OR @p2 IS NULL)

在这种情况下，优化器会为实体生成索引扫描"(非搜索)操作，当提供的参数具有非null值时，该操作不是最有效.
如果我在查询中添加RECOMPILE提示，则优化器将使用"seek"构建更有效的计划.它可以在我的MSSQL 2008 R2 SP1服务器上运行，并且还意味着优化程序可以构建仅考虑查询的一个逻辑分支的计划.
我如何才能在不需要重新编译的情况下在任何地方使用该计划?在这种情况下，USE PLAN提示似乎不起作用.

In this case the optimizer generates "index scan" (not seek) operations for the entity which is not most effective when parameters are supplied with not null values.
If i add the RECOMPILE hint to the query the optimizer builds more effective plan which uses "seek". It works on my MSSQL 2008 R2 SP1 server and it also means that the optimizer CAN build a plan which consider only one logic branch of my query.
How can i make it to use that plan everywhere i want with no recompiling? The USE PLAN hint seemes not to work in this case.

下面是测试代码:

-- see plans  
CREATE TABLE test_table(  
    id INT IDENTITY(1,1) NOT NULL,   
    name varchar(10),    
    CONSTRAINT [pk_test_table] PRIMARY KEY CLUSTERED (id ASC))  
GO  
INSERT INTO test_table(name) VALUES ('a'),('b'),('c')  
GO  
DECLARE @p INT = 1  
SELECT name FROM test_table WHERE id = @p OR @p IS NULL  
SELECT name FROM test_table WHERE id = @p OR @p IS NULL OPTION(RECOMPILE)  
GO  
DROP TABLE test_table  
GO  

请注意，并非所有版本的SQL Server都会按照我显示的方式更改计划.

Note that not all versions of SQL server will change the plan the way i shown.

推荐答案

您进行扫描的原因是，该谓词不会短路，并且两个语句都将始终被求值.正如您已经说过的那样，它不能与优化器一起很好地工作并强制进行扫描.即使with recompile有时似乎有帮助，但这也不是一致的.

The reason you get a scan is because the predicate will not short-circuit and both statements will always be evaluated. As you have already stated it will not work well with the optimizer and force a scan. Even though with recompile appears to help sometimes, it's not consistent.

如果您有一个很大的桌子，必须在其中寻找，那么您有两个选择:

If you have a large table where seeks are a must then you have two options:

1. 动态sql.
2. 如果语句将您的查询分开，从而创建了单独的执行计划(当@p is null时，您当然总是会得到扫描).

1. Dynamic sql.
2. If statements separating your queries and thus creating separate execution plans (when @p is null you will of course always get a scan).

这篇关于可选参数，“索引查找"，即“索引查找".计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！