(一)问题提出
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)
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的值,所以不能产生一个正确的执行计划。
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()
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
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'
,
@
date
=
'2012-04-29'
|
以上四种解决方案的执行计划都如下,实际环境推荐封装成存储过程: