SQL Server优化技巧——如何避免查询条件OR引起的性能问题
之前写过一篇博客“SQL SERVER中关于OR会导致索引扫描或全表扫描的浅析”，里面介绍了OR可能会引起全表扫描或索引扫描的各种案例，以及如何优化查询条件中含有OR的SQL语句的几种方法，其实还有一些方法可以用来优化这种问题，这里简单介绍一下。
如下所示，下面的SQL语句之所有出现这种写法，是因为程序的查询界面，可能有多个输入性的查询条件，往往用户只填了一个或部分查询条件（业务情况，应该不用详细介绍，大家都能明白），但是程序里面没有通过判断查询条件生成不同的SQL语句，而是用一个SQL搞定，不管用户没有填写JobNo这个查询条件，下面这种写法：WHERE ISNULL(@JobNo, '') = '' OR JobNo = @JobNo都能满足条件，实现逻辑功能。
DECLARE @GenerateDateStart DATETIME ,
@GenerateDateEnd DATETIME ,
@JobNo NVARCHAR(200) ,
@GkNo NVARCHAR(200);
SET @JobNo = 'PT19B030';
SET @GkNo = 'PV19-1-8050';
SELECT *
FROM [dbo].[GEW_UnitConsumption] AS A
LEFT JOIN dbo.UnitConsumption_Relation AS B ON B.UsableFlag = 'Y'
AND A.GewUnitConsumptionId = B.RootUnitConsumptionID
WHERE ( ISNULL(@JobNo, '') = ''
OR A.JobNo = @JobNo
)
AND ( ISNULL(@GkNo, '') = ''
OR A.GkNo = @GkNo
);
其实，如果根据查询条件动态生成SQL语句，的确能避免查询条件中出现OR的情形，但是动态SQL语句没有上面语句简单和通熟易懂，尤其是查询条件较多的情况下。只能说各有利弊。这里暂且不讨论那种策略的优劣。
下面介绍一种技巧，如何避免OR引起的索引扫描或全表扫描问题。我们可以使用CASE WHEN改写一下这个SQL语句，就能避免OR引起的执行计划不走索引查找(Index Seek)的情况，如下所示：
DECLARE @GenerateDateStart DATETIME ,
@GenerateDateEnd DATETIME ,
@JobNo NVARCHAR(200) ,
@GkNo NVARCHAR(200);
SET @JobNo = 'PT19B030';
SET @GkNo = 'PV19-1-8050';
SELECT *
FROM [dbo].[GEW_UnitConsumption] AS A
LEFT JOIN dbo.UnitConsumption_Relation AS B ON B.UsableFlag = 'Y'
AND A.GewUnitConsumptionId = B.RootUnitConsumptionID
WHERE CASE WHEN ISNULL(@JobNo, '') = '' THEN A.JobNo
ELSE @JobNo
END = JobNo
AND CASE WHEN ISNULL(@GkNo, '') = '' THEN A.GkNo
ELSE GkNo
END = @GkNo;
测试对比发现性能改善非常明显，当然这种优化技巧也是有局限性的，并不能解决所有OR引起的性能问题（没有银弹！）。如下所示，对于下面这种情况，这种技巧也是无能为力！
SELECT * FROM TEST1 WHERE A=12 OR B=500
------------------------------------------分割线-------------------------------------------------
网友MSSQL123反馈：他测试的一个案例发现这种技巧无效，个人测试验证发现确实如此，后面发现个人遇到的仅仅是一个特殊个例（当时生产环境那个场景下确实生效了），后面经过大量测试发现，很多情况下CASE WHEN这种技巧无效，也就是说单个案例不具有通用性，后面进一步测试分析，发现我得出的结论是错误的。
当然在错误的基础上，进一步测试验证，发现还是有技巧优化OR引起的性能问题的，这也是我后续补充的原因,请见下文分析：
我们首先简单构造一个测试环境案例,测试环境为SQL Server 2014
CREATE TABLE TEST_OPTION_COMPILE (OBJECT_ID INT, NAME VARCHAR(16));
CREATE CLUSTERED INDEX PK_TEST_OPTION_COMPILE ON TEST_OPTION_COMPILE(OBJECT_ID);
DECLARE @Index INT =0;
WHILE @Index < 100000
BEGIN
INSERT INTO TEST_OPTION_COMPILE
SELECT @Index, 'kerry'+CAST(@Index AS VARCHAR(7));
SET @Index = @Index +1;
END
CREATE INDEX IX_TEST_OPTION_COMPILE_N1 ON TEST_OPTION_COMPILE(NAME);
UPDATE STATISTICS TEST_OPTION_COMPILE WITH FULLSCAN;
如下测试所示，发现这个例子中，CASE WHEN完全无效，使用这种SQL写法，依然走Index Scan
DECLARE @name VARCHAR(8);
SET @name = 'kerry8'
SELECT NAME
FROM dbo.TEST_OPTION_COMPILE
WHERE CASE WHEN ISNULL(@name, '') = '' THEN NAME
ELSE @name
END = NAME;
SELECT NAME
FROM dbo.TEST_OPTION_COMPILE
WHERE ( ISNULL(@name, '') = ''
OR NAME = @name
)
![clip_image001[1]](https://img2018.cnblogs.com/blog/73542/201911/73542-20191126214831619-1101208717.png "clip_image001[1]")
如果我们在SQL后面加上OPTION(RECOMPILE)的话，那么SQL就会走索引查找（Index Seek),其实下面两个SQL语句，如果都加上OPTION(RECOMPILE)的话，它们都会走索引。这是什么情况呢？
![clip_image002[1]](https://img2018.cnblogs.com/blog/73542/201911/73542-20191126214832598-1656222583.png "clip_image002[1]")
接下来我们对比分析一下，看看SQL语句有无OPTION(RECOMPILE)的区别，如下所示：
DECLARE @name VARCHAR(8);
SET @name = 'kerry8'
SELECT NAME
FROM dbo.TEST_OPTION_COMPILE
WHERE CASE WHEN ISNULL(@name, '') = '' THEN NAME
ELSE @name
END = NAME ;
SELECT NAME
FROM dbo.TEST_OPTION_COMPILE
WHERE CASE WHEN ISNULL(@name, '') = '' THEN NAME
ELSE @name
END = NAME OPTION(RECOMPILE)
如下所示，如果没有OPTION(RECOMPILE)的话，执行计划走Index Scan，预估行数（Estimated Number of Rows）是100000， 而实际行数（Actual Number of Rows）是1，
如果SQL中有OPTION(RECOMPILE)的话，执行计划走Index Seek，预估行数（Estimated Number of Rows）是1， 而实际行数（Actual Number of Rows）是1，从对比我们可以看出，加上OPTION(RECOMPILE)的话，SQL的执行计划要准确很多，那么为什么呢？这里是因为OPTION(RECOMPILE)开启了Parameter Embedding Optimization
关于Parameter Embedding Optimization，这里简单介绍一下，详情参考Parameter Sniffing, Embedding, and the RECOMPILE Options 和参考资料的相关文档。
参数嗅探值使优化器可以使用参数值来得出基数估计。 WITH RECOMPILE和OPTION（RECOMPILE）均会生成查询计划，并根据每次执行时的实际参数值计算出估算值。
相比WITH RECOMPILE这种强制重编译的方式，OPTION(RECOMPILE)中的参数嵌入优化（Parameter Embedding Optimization）的机制更进一步：查询解析期间，查询参数被文字常量值替代。 解析器能够神奇的将复杂问题简单化，并且在随后的查询优化可能会进一步完善这些内容。
Microsoft在SQL Server 2008（后RTM）中引入了参数嵌入优化（Parameter Embedding Optimization）。 这个特性扩展了参数嗅探优化。 它能使用基数估计值来嗅探参数以影响计划。具体参考官方文档“Changed behaviour of OPTION RECOMPILE syntax in SQL Server 2008 SP1 cumulative update #5”
总结： 我们可以使用OPTION(RECOMPILE)（确切的说，是Parameter Embedding Optimization）这种技巧来避免查询条件中OR引起的性能问题，这确实是一个SQL Server优化技巧，至于我前面的结论，这是一个错误结论（使用CASE WHEN改写一下这个SQL语句，就能避免OR引起的执行计划不走索引查找(Index Seek)）。在缺乏严谨的论证、充分的测试就草率的得出了一个结论，以后要引以为戒！。
参考资料：
https://www.cnblogs.com/wy123/p/6262800.html
https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options
