Select带参数

dotnba 2009-03-08 08:49:18
有表ITWORKITEMS,WIREFID 字段上建有索引。如下两种方式却存在巨大性能差异,为何?

方式一:

SET STATISTICS TIME ON
DECLARE @Process_Instance_Id nVarChar(48)
SET @Process_Instance_Id = 'nvevrqt6e25wzpvhpa95htn4h2.%'

SELECT 1
FROM ITWORKITEMS I
WHERE I.WIREFID LIKE @Process_Instance_Id


ORDER BY I.WISTART DESC, I.PRINSTUID DESC, I.WIID DESC


SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

(100 行受影响)

SQL Server 执行时间:
CPU 时间 = 1984 毫秒,占用时间 = 267 毫秒。

方式二:
SET STATISTICS TIME ON
DECLARE @Process_Instance_Id nVarChar(48)
SET @Process_Instance_Id = 'nvevrqt6e25wzpvhpa95htn4h2.%'

SELECT 1
FROM ITWORKITEMS I
WHERE I.WIREFID LIKE 'nvevrqt6e25wzpvhpa95htn4h2.%'


ORDER BY I.WISTART DESC, I.PRINSTUID DESC, I.WIID DESC



SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

(100 行受影响)

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 2 毫秒。
...全文
351 25 打赏 收藏 举报
写回复
25 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
dotnba 2009-03-10
有时候确实有这个毛病(不光是 like, = 有时候都有这种问题)

一般采用 WITH(INDEX(index_name)) 强制使用索引
或者通过 OPTION(OPTIMIZE FOR(@变量='值')) , 以使用特定值来生成执行计划
  • 打赏
  • 举报
回复
rockywu 2009-03-09
不错的贴子...学习
  • 打赏
  • 举报
回复
wzy_love_sly 2009-03-09
因为变量的,会有个查询的开销评估,估计是这里占用了时间
  • 打赏
  • 举报
回复
wzy_love_sly 2009-03-09
楼主帖执行计划看看
  • 打赏
  • 举报
回复
LIHY70 2009-03-09
xuexi
  • 打赏
  • 举报
回复
dotnba 2009-03-09
测试步骤:

1.
SET STATISTICS TIME ON  

2.
DECLARE @Process_Instance_Id nVarChar(48) 
SET @Process_Instance_Id = 'nvevrqt6e25wzpvhpa95htn4h2.%'
SELECT 1
FROM ITWORKITEMS I
WHERE I.WIREFID LIKE @Process_Instance_Id
ORDER BY I.WISTART DESC, I.PRINSTUID DESC, I.WIID DESC

结果:
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 315 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

(102 行受影响)

SQL Server 执行时间:
CPU 时间 = 1485 毫秒,占用时间 = 22802 毫秒。

3.

DBCC FREEPROCCACHE 
DBCC DROPCLEANBUFFERS
CHECKPOINT


4.
SET STATISTICS TIME ON 
DECLARE @Process_Instance_Id nVarChar(48)
SET @Process_Instance_Id = 'nvevrqt6e25wzpvhpa95htn4h2.%'
SELECT 1
FROM ITWORKITEMS I
WHERE I.WIREFID LIKE 'nvevrqt6e25wzpvhpa95htn4h2.%'
ORDER BY I.WISTART DESC, I.PRINSTUID DESC, I.WIID DESC


SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 283 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

(102 行受影响)

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 436 毫秒。
  • 打赏
  • 举报
回复
interfacejava 2009-03-09
 1、Like语句是否属于SARG取决于所使用的通配符的类型

  如:name like ‘张%’ ,这就属于SARG

  而:name like ‘%张’ ,就不属于SARG。

  原因是通配符%在字符串的开通使得索引无法使用。
  • 打赏
  • 举报
回复
sumaoyi 2009-03-09
mark 关注
  • 打赏
  • 举报
回复
中国风 2009-03-09
DBCC FREEPROCCACHE 
DBCC DROPCLEANBUFFERS
CHECKPOINT

執行后再測第二個語句
  • 打赏
  • 举报
回复
dotnba 2009-03-09
[Quote=引用 9 楼 maco_wang 的回复:]
SQL code
set statistics time on
select * from mytemptable where gctxmc like'%06%'
setstatistics time on
declare @con varchar(20)
set @con='%06%'
select * from mytemptable where gctxmc like @con--在我这like后面接常量和变量的执行速度是一样的
[/Quote]

我这里测试也一样,但是你加上Order By再试一下,应该就不一样了。
此外,
1.你的Like后其实用不到索引,因为用不到索引,都是全盘扫描,结果区别不大也正常。
2.你的mytemptable 表数据量有可能太小,看不出差别。我的表是100万行
  • 打赏
  • 举报
回复
叶子 2009-03-09

set statistics time on
select * from mytemptable where gctxmc like '%06%'

set statistics time on
declare @con varchar(20)
set @con='%06%'
select * from mytemptable where gctxmc like @con
--在我这like后面接常量和变量的执行速度是一样的
  • 打赏
  • 举报
回复
1TH 2009-03-09
学习~
  • 打赏
  • 举报
回复
jamo 2009-03-09
关注
  • 打赏
  • 举报
回复
wzy_love_sly 2009-03-09
好象常量,会有并行的排序,而变量的不行
表扫有点奇怪了,不应该吧...

楼主投100分,叫zj老大看看吧
  • 打赏
  • 举报
回复
bhtfg538 2009-03-09
UP ,关注。
  • 打赏
  • 举报
回复
dotnba 2009-03-09
下面是截图,明显带参数就不能使用索引。谁能解释原因吗?

  • 打赏
  • 举报
回复
piaoxuerenjian 2009-03-09
关注
  • 打赏
  • 举报
回复
dotnba 2009-03-08
[Quote=引用 7 楼 jia_guijun 的回复:]
SQL code--请在执行第二个查询前加入以下语句后再试:dbccdropcleanbuffers--清除缓冲区的功能,因为第一个执行时已经将数据写到缓冲区中,第二次再执行就直接读缓冲区了,所以先执行这个语句清除缓冲区后再试结果
[/Quote]

问题不在这里,可以将两种方式的执行顺序调整一下,结果是一样。
  • 打赏
  • 举报
回复
jia_guijun 2009-03-08
--请在执行第二个查询前加入以下语句后再试:
dbcc dropcleanbuffers --清除缓冲区的功能,因为第一个执行时已经将数据写到缓冲区中,第二次再执行就直接读缓冲区了,所以先执行这个语句清除缓冲区后再试结果
  • 打赏
  • 举报
回复
jia_guijun 2009-03-08
--请在执行第二个查询前加入以下语句后再试:
dbcc dropcleanbuffers --清除缓冲区的功能,因为第一个执行时已经将数据写到缓冲区中,第二次再执行就直接读缓冲区了,所以先执行这个语句清除缓冲区后再试结果
  • 打赏
  • 举报
回复
加载更多回复(5)
发帖
MS-SQL Server

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
帖子事件
创建了帖子
2009-03-08 08:49
社区公告
暂无公告