T-SQL之变量导致索引无效的问题
来源：未知 责任编辑：责任编辑 发表时间:2015-09-16 20:04 点击:次
T-SQL之变量导致索引无效的问题
(一)问题提出
1，在开发中是否遇到一个情况，就是在where后写明具体值时可以用到索引，使用变量时却不行了呢？
2，是否开始怀疑MS SQL 出现了编译问题。
（二）测试过程
1，建立测试数据
CREATE TABLE t_order (
orderid INT IDENTITY ( 1 , 1 ) PRIMARY KEY,
ordertime DATETIME,
productname VARCHAR(50))
GO
--创建索引
CREATE INDEX idx_ordertime ON t_order (
ordertime) www.2cto.com
GO
--插入1000000条记录
WITH cte
AS (SELECT NUMBER + 1 AS NUMBER
FROM master..spt_values a
WHERE a.TYPE = 'P'
AND NUMBER < 1000)
INSERT INTO t_order
(ordertime,
productname)
SELECT Getdate() - a.NUMBER,
LEFT(Newid(),10)
FROM cte a
CROSS JOIN cte b
GO
2，分别查询
SET STATISTICS io ON
－－查询一采用变量
DECLARE @date DATETIME
SET @date = Getdate()
SELECT *
FROM t_order
WHERE ordertime > @date
GO
－－查询二采用变量给出具体值
SELECT *
FROM t_order
WHERE ordertime > Getdate()
3，对比执行计划发现相差太太太太大了。
查询1扫描了整个表，查询2确实很好的一个seek加Look up
（三） 原因分析以及验证
1，原因分析
因为当你使用变量时，查询语句在编译时，并不做SET操作。换句话说，即是SET操作是编译完成后，执行的时候才执行。所以编译的时候MS SQL 并不知道◎date的值，所以不能产生一个正确的执行计划。 www.2cto.com
2,验证
MS SQL在这种情况总按照一个固定的估计值在产生执行计划（即30%），所以做一个全表扫描更划算。让我们来论证一下，我们对该表插入了1000000条记录，按照30% ，所以预估行数就该是300000,查看执行计划，果然如此（注意红色方框）：
（四）解决方案
解决方案1：（使用option(RECOMPILE)，在执行时重新编译）：
declare @date datetime
set @date=GETDATE()
select * from T_order where ordertime>@date
option(RECOMPILE)
解决方案2：给定一个参数提示给该查询
declare @date datetime
set @date=GETDATE()
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>
