可选参数“索引查找”计划
在我的 SELECT 语句中，我以如下方式使用可选参数：
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)
在这种情况下，优化器为实体生成“索引扫描”（而不是查找）操作，当参数提供非空值时，这不是最有效的。
如果我将 RECOMPILE 提示添加到查询中，优化器会构建更有效的使用“seek”的计划。它适用于我的 MSSQL 2008 R2 SP1 服务器，这也意味着优化器可以构建一个仅考虑查询的一个逻辑分支的计划。
我怎样才能让它在我想要的任何地方使用该计划而不需要重新编译？ USE PLAN 提示在这种情况下似乎不起作用。
下面是测试代码：
-- 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 都会按照我所示的方式更改计划。
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)
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.
Below is test code:
-- 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
Note that not all versions of SQL server will change the plan the way i shown.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
进行扫描的原因是谓词不会短路，并且两个语句都将始终被评估。正如您已经说过的，它不能与优化器很好地配合并强制扫描。尽管
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 recompileappears to help sometimes, it's not consistent.If you have a large table where seeks are a must then you have two options:
@p is nullyou will of course always get a scan).对 Andreas 答案的评论的回应
问题是您需要两个不同的计划。
@p1 = 1那么您可以在索引上使用SEEK。@p1 IS NULL，则它不是查找，根据定义，它是扫描。这意味着当优化器在了解参数之前生成计划时，它需要创建一个可以满足所有可能性的计划。只有扫描才能满足两者
@p1 = 1和@p1 IS NULL的需求。这也意味着，如果在参数已知且
@p1 = 1时重新编译计划，则可以创建 SEEK 计划。这就是您在评论中提到的原因， IF 语句解决了您的问题；每个 IF 块代表问题空间的不同部分，并且每个块都可以被赋予不同的执行计划。
Response to Comment on Andreas' Answer
The problem is that you need two different plans.
@p1 = 1then you can use a SEEK on the index.@p1 IS NULL, however, it is not a seek, by definition it's a SCAN.This means that when the optimiser is generating a plan Prior to knowledge of the parameters, it needs to create a plan that can fullfil all possibilities. Only a Scan can cover the needs of Both
@p1 = 1And@p1 IS NULL.It also means that if the plan is recompiled at the time when the parameters are known, and
@p1 = 1, a SEEK plan can be created.This is the reason that, as you mention in your comment, IF statements resolve your problem; Each IF block represents a different portion of the problem space, and each can be given a different execution plan.
请参阅T-SQL 中的动态搜索条件。
这全面解释了
RECOMPILE选项起作用的版本以及不起作用的替代方案。See Dynamic Search Conditions in T-SQL.
This explains comprehensively the versions where the
RECOMPILEoption works and alternatives where it doesn't.看这篇文章 http://www.bigresource.com/Tracker/Track-ms_sql- fTP7dh01/
看来你可以尝试使用提案解决方案：
或者
Look at this article http://www.bigresource.com/Tracker/Track-ms_sql-fTP7dh01/
It seems that you can try to use proposal solution:
or