T-SQL之变量导致索引无效的问题

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

T-SQL之变量导致索引无效的问题

(三) 原因分析以及验证

1,原因分析

因为当你使用变量时,查询语句在编译时,并不做SET操作。换句话说,即是SET操作是编译完成后,执行的时候才执行。所以编译的时候MS SQL 并不知道◎date的值,所以不能产生一个正确的执行计划。 www.2cto.com

2,验证

MS SQL在这种情况总按照一个固定的估计值在产生执行计划(即30%),所以做一个全表扫描更划算。让我们来论证一下,我们对该表插入了1000000条记录,按照30% ,所以预估行数就该是300000,查看执行计划,果然如此(注意红色方框):

T-SQL之变量导致索引无效的问题

(四)解决方案

解决方案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()

select * from T_order where ordertime>@date

option(OPTIMIZE FOR (@date='2012-04-29'))

解决方案3:封装成存储过程,有人就会疑问了,为什么存储过程可以呢?在这里大家别把参数和变量混淆了,在SQL SERVER里面写法都一样,但意义不完全一样。存储过程的编译实在第一次执行的时候才产生执行计划。

--创建存储过程

CREATE PROC Sp_select_t_order

@date DATETIME

AS www.2cto.com

SELECT *

FROM t_order

WHERE ordertime > @date

GO

--执行存储过程

DECLARE @date DATETIME

SET @date = Getdate()

EXEC Sp_select_t_order @date

解决方案4:参数化查询

sp_executesql

N'select * from T_order where ordertime>@date',

N'@date datetime', www.2cto.com

@date='2012-04-29'

以上四种解决方案的执行计划都如下,实际环境推荐封装成存储过程:

T-SQL之变量导致索引无效的问题

作者 小材小用

分类:默认分类 时间:2015-03-03 人气:1
本文关键词:
分享到:

相关文章

Copyright (C) quwantang.com, All Rights Reserved.

趣玩堂 版权所有 京ICP备15002868号

processed in 0.039 (s). 10 q(s)