Select带参数

[dotnba]

有表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 毫秒。

[jia_guijun]

--请在执行第二个查询前加入以下语句后再试：
dbcc dropcleanbuffers    --清除缓冲区的功能，因为第一个执行时已经将数据写到缓冲区中，第二次再执行就直接读缓冲区了，所以先执行这个语句清除缓冲区后再试结果

[jia_guijun]

--请在执行第二个查询前加入以下语句后再试：
dbcc dropcleanbuffers    --清除缓冲区的功能，因为第一个执行时已经将数据写到缓冲区中，第二次再执行就直接读缓冲区了，所以先执行这个语句清除缓冲区后再试结果

[dotnba]

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

问题不在这里，可以将两种方式的执行顺序调整一下，结果是一样。

[piaoxuerenjian]

关注

[dotnba]

下面是截图，明显带参数就不能使用索引。谁能解释原因吗？

[bhtfg538]

UP ,关注。

[wzy_love_sly]

好象常量，会有并行的排序，而变量的不行
表扫有点奇怪了，不应该吧...

楼主投100分，叫zj老大看看吧

[jamo]

关注

[1TH]

学习~

[叶子]

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后面接常量和变量的执行速度是一样的

[dotnba]

[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万行

[中国风]

DBCC FREEPROCCACHE 
DBCC DROPCLEANBUFFERS
CHECKPOINT

執行后再測第二個語句

[sumaoyi]

mark 关注

[interfacejava]

　1、Like语句是否属于SARG取决于所使用的通配符的类型

　　如：name like ‘张%’ ，这就属于SARG

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

　　原因是通配符%在字符串的开通使得索引无法使用。

[dotnba]

测试步骤：

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 毫秒。

[LIHY70]

xuexi

[wzy_love_sly]

楼主帖执行计划看看

[wzy_love_sly]

因为变量的，会有个查询的开销评估，估计是这里占用了时间

[rockywu]

不错的贴子...学习

[dotnba]

有时候确实有这个毛病(不光是 like, = 有时候都有这种问题)

一般采用 WITH(INDEX(index_name)) 强制使用索引
或者通过 OPTION(OPTIMIZE FOR(@变量='值')) , 以使用特定值来生成执行计划