OPTION(RECOMPILE)提高带参数执行SQL语句的索引效率

本例为MSSQL2008环境下测试。

1. 无参数查询
先看一个简单的查询语句在大数据表中的查询情况：

SELECT fmr.FID,fmr.BasicTime FROM feeMeterRead fmr
 WHERE fmr.BasicTime>='2015-12-02 00:00:00'

在查询分析器中执行后显示耗时5s，查询到的记录数为6223条

2. 带参数查询
现在引用参数再查询一次

DECLARE
    @BasicTime DATETIME
 SET @BasicTime= LEFT(CONVERT(VARCHAR(20),DATEADD(DAY,-2,GETDATE()),120),10)+' 00:00:00'
 --SELECT @BasicTime
 SELECT fmr.FID,fmr.BasicTime FROM feeMeterRead fmr
 WHERE fmr.BasicTime>=@BasicTime

为保证查询条件一样，参数@BasicTime的值也是’2015-12-02 00:00:00’
在查询分析器中执行后显示耗时15s，查询到的记录数为6223条。比不带参数的查询时间多了10s。

3. 分析对比

将例一查询语句选中，点击工具栏中的“显示估计的执行计划”按钮，可以看到该语句的执行计划。

可以看到，执行时使用了索引查找和键查找这两种最有效率的查询方式，所以耗时短。

同理查看例2中的执行计划

查询1是赋值参数的过程，我们主要看查询2、

执行计划明显比例1的无参数查询的复杂，例1的“键查找”在例2中变成了“索引扫描“。

由此可判断此处是耗时的主要关键。

4. 语句优化
在语句末尾添加OPTION(RECOMPILE)，可以使执行计划重新编译，提高性能。

DECLARE
    @BasicTime DATETIME
 SET @BasicTime= LEFT(CONVERT(VARCHAR(20),DATEADD(DAY,-2,GETDATE()),120),10)+' 00:00:00'
 --SELECT @BasicTime
 SELECT fmr.FID,fmr.BasicTime FROM feeMeterRead fmr
 WHERE fmr.BasicTime>=@BasicTime
 OPTION(RECOMPILE)

上面查询语句重新执行后，耗时为6s。优化成功。
我们现在来查看下执行计划，估计的执行计划还是没变，但是点击”包括实际的执行计划“，然后再点击”执行“查询，可以看到执行时的计划如下：

可以看到，实际执行时是重新编译了，执行计划已经与例1的无参数执行一致。