主页

SQL 2008 CTE 使用小结。原来CTE 没有Index。顺带问个问题。

shoppo0505 2014-01-21 06:23:51

标题有点夸张了。不过做了那么久，确实也是刚知道，所以上来通报一声。

本人因为平时写代码比较规范，一般SQL语句都会尽可能简化，而且如果使用CTE,基本上不太会返回大量数据。昨天同事写了存储过程，执行时间竟然超过了10分钟，存储过程本省很简单，前面就是一些变量定义，然后就是使用了CTE语句，CTE返回结果在和5，6个表inner join连接, 表的数据量也就是1，2百万，不算很多。
按照以前的经验，这种语句应该是瞬秒的，但是这次需要这么长时间，肯定不正常。

给几个新表新添了几个Index,但是问题完全没有改善。然后根据自己精良简化语句的原则，改了一下Code，结合刚添加的index,存储过程一下变成瞬秒，执行时间为0秒。后来在网上查了一下，原来CTE没有index的。所以如果CTE返回百万级数据量，然后再和其他表格连接的话，那么超过10分钟的执行时间也是意料之中了。

旧代码大致如下：
with tb as --返回百万级数据
(
select ...
)
select ...
from 表格1
inner join 表格2 --百万级数据
inner join 表格3 --百万级数据
inner join 表格4 --百万级数据
inner join tb
... --链接几个小数据表格
group by ...
order by ...
执行时间超过10分钟

新代码大致如下：
with tb as --返回几时行数据
(
select ...
inner join 表格2 --百万级数据
inner join 表格3 --百万级数据
inner join 表格4 --百万级数据
)
select ...
from tb
inner join tb
... --链接几个小数据表格
group by ...
order by ...
执行时间0秒。

再提个问题
我将存储过程的旧代码直接在窗口中执行的话，执行时间大概45秒左右，但是如果作为存储过程，执行时间就超过10分钟，这个上面也浪费我不少时间。
有哪位高人知道是什么原因么？

...全文

46 条回复

KeepSayingNo 2014年01月24日

發糞塗牆解释得非常到位啊，CTE经常有的仅仅用其作为一个临时对象，在cte结构块里面根本没用到递归，楼主直接把with去掉，在外层继续套一层select呢

回复点赞

阳泉酒家小当家 2014年01月24日

引用 44 楼 x_wy46 的回复:

[quote=引用 38 楼 yupeigu 的回复:] [quote=引用 37 楼 shoppo0505 的回复:] [quote=引用 36 楼 yupeigu 的回复:] 不过，我觉得这个应该不太可能是参数嗅探的问题，因为这个参数嗅探，一般不会因为你的sql写法不同，就会有什么不同。

完全相同的语句（就是我之前还没有优化的语句），我一个在管理器界面中直接执行，另外一个作为存储过程执行。前者运行需要1分钟不到，后者超过10分钟。我从来没有遇到过这种情况。不知道所以然。[/quote] 你的意思是，语句完全一样，采用的是新的语句，一个是在ssms中执行，一个是在存储过程中执行，效率完全不同是吧。那就是参数嗅探了的问题了。 [/quote] 参数嗅探理论上是这样没错，关键是已经缓存的执行计划是如何影响到当前执行的sql的你举得例子很经典，因为我没有遇到类似的问题，所有我有这样一个疑问存储过程执行慢单独的sql放在查询分析器中执行快那就是说，查询分析器中的sql的执行利用的是一个相对合理的执行计划，存储过程中的sql的执行利用的是“不合理”的执行计划，现在是同一个查询，存在2个可以利用的执行计划存储过程生成的那个，直接执行sql生成的那个（这个说法很不恰当，为了说明问题）那么再次执行存储过程时，存储过程为什么“刚刚好又选择中”了那个“不合理”的执行计划? 这个我在本机继续测试测试求证一下。 [/quote] 我的理解是这样的：之所以那么再次执行存储过程时，存储过程为什么“刚刚好又选择中”了那个“不合理”的执行计划? 是因为存储过程是有名称的，比如create proc dbo.abc 那么这个存储过程的名称就是abc，所以每次exec abc 参数，的时候不管参数是多少，由于这个存储过程的执行计划之前已经生成，所以通过存储过程的名称，就能准确的定位这个执行计划。所以，不是刚刚好又选择中了那个执行计划，而是肯定会选择那个执行计划。而一般的adhoc既席查询，每次执行，大部分都是重新编译，所以针对具体的参数值，往往能生成比较合适的执行计划。

回复点赞

专注or全面 2014年01月23日

引用 38 楼 yupeigu 的回复:

[quote=引用 37 楼 shoppo0505 的回复:] [quote=引用 36 楼 yupeigu 的回复:] 不过，我觉得这个应该不太可能是参数嗅探的问题，因为这个参数嗅探，一般不会因为你的sql写法不同，就会有什么不同。

回复点赞

lg314 2014年01月23日

没有用递归的话cte和子查询没有区别,这个问题感觉像是统计信息的不准确，可以看一下运行时的执行计划对比下每个表实际数据量和预估数据量的差异

回复点赞

發糞塗牆 2014年01月23日

引用 37 楼 shoppo0505 的回复:

[quote=引用 36 楼 yupeigu 的回复:] 不过，我觉得这个应该不太可能是参数嗅探的问题，因为这个参数嗅探，一般不会因为你的sql写法不同，就会有什么不同。

完全相同的语句（就是我之前还没有优化的语句），我一个在管理器界面中直接执行，另外一个作为存储过程执行。前者运行需要1分钟不到，后者超过10分钟。我从来没有遇到过这种情况。不知道所以然。[/quote]很多年前遇到过，现在没遇到了，但是根据现在的知识，很大程度上是因为编译问题，除了执行计划的对比，还要开启set 选项、性能计数器等来监控一下

回复点赞

阳泉酒家小当家 2014年01月22日

引用 18 楼 shoppo0505 的回复:

因为工作的保密性问题，不能把执行计划发给你看，见谅。新旧计划我和同事一起看的，我们都认为是一样的。这个和我之前的结论基本是吻合的。百万级数据库实表之间的连接执行时间都可以忽略不计的。

哦，理解。另外，你提到的，尽量减少结果集，这个才是，新的语句本质上快的原因。我同意减少结果集，这样能让查询更快，我以前写报表的时候也有类似的问题，有2种方法，一种是先group by ，然后再把多个group by结果集关联，这种方法就很快，而另一种是先关联，然后直接group by，这种往往就会非常慢。这个减少结果集是让查询更快的一个原因，但这并一定就是第一个查询慢的原因，我觉得应该还有其他原因。

回复点赞

發糞塗牆 2014年01月22日

只看电子书，只有徐海蔚和宋沄剑

送我的的实体书

引用 24 楼 yupeigu 的回复:

[quote=引用 19 楼 DBA_Huangzj 的回复:] 手机看，看不了这么多，我还有30多本书没看....压力大

哦，都是数据库的书吗？下载的电子书？[/quote]

引用 24 楼 yupeigu 的回复:

[quote=引用 19 楼 DBA_Huangzj 的回复:] 手机看，看不了这么多，我还有30多本书没看....压力大

哦，都是数据库的书吗？下载的电子书？[/quote]工作需要，研究一下Extended events

回复点赞

阳泉酒家小当家 2014年01月22日

引用 19 楼 DBA_Huangzj 的回复:

手机看，看不了这么多，我还有30多本书没看....压力大

哦，都是数据库的书吗？下载的电子书？

回复点赞

發糞塗牆 2014年01月22日

另外再唠叨一下，针对你第一个问题：抽象一下，实际上是4步，第一步原数据，第二步过滤数据，第三步和其他表关联，第四部返回结果。对于第一个CTE，到了第三步，数据量才减少，这样优化器会使用处理大数据量的算法，而且资源负担很重。你的数据集长期保留，甚至会使用worktable。产生非必要的IO。第二个CTE，到了第二步，数据量已经大大减少，后续的操作实际上是小表操作，几十、上百行数据，即使没有索引，也不会存在什么性能问题，cross join不就是过万数据而已。爱怎么搞怎么搞。这个说白了就是尽快减少数据集的规模，提高速度的核心思想表现

回复点赞

發糞塗牆 2014年01月22日

引用 20 楼 tntzbzc 的回复:

[quote=引用 19 楼 DBA_Huangzj 的回复:] 手机看，看不了这么多，我还有30多本书没看....压力大

《如意君传》、《痴婆子传》、《素娥篇》、《弁而钗》、《ROU蒲团》、《姑妄言》、《金瓶梅》等等。。。。。。。 [/quote]求下载地址

回复点赞

發糞塗牆 2014年01月22日

本人看法： 1、虽然CTE用不到索引，但是，由于第二个查询中的inner join多表关联是能用到索引，所以在理想情况下第二个查询的CTE内部已经处理得七七八八，你也说了，最终第二个CTE返回几十行数据，这个数据集对CTE来说几乎不存在性能问题，再和几个小表关联也不会有问题。CTE内部已经多为index seek，而第一个CTE，实际上就是一个类似一个没有索引的临时表，几百万的临时表，没有索引，后续操作几乎只有table scan这种Physical operator。而且还不能保证使用的join 算法是否合适。不过我以前的确没注意过这点，赞一个。 2、针对你这个问题：再提个问题我将存储过程的旧代码直接在窗口中执行的话，执行时间大概45秒左右，但是如果作为存储过程，执行时间就超过10分钟，这个上面也浪费我不少时间。有哪位高人知道是什么原因么？这个应该是编译和优化存储过程的开销，ad hoc你可以看看执行计划，有没有【优化级别】为full还是tra啥啥的那个，估计优化器几乎就没给你优化这个ad hoc，而存储过程，如果没有plan cache，就需要去编译、优化产生执行计划，你可以用set statistcs io/time这两个语句来看看存储过程和ad hoc的区别，可能在CPU时间上差异会比较大。前面说的参数嗅探可能不是很适合你这个问题，不过不保证不是那个问题。

回复点赞

阳泉酒家小当家 2014年01月22日

其实，总结简化一下就是：存储过程中的变量，有2种，一种是参数，一种是存储过程中内部定义的参数，第一种存储过程的参数，是exec xxx 时，传入存储过程中的，在第一次传入一个值后，编译了存储过程，生成了执行计划，以后当你传入不同的参数值时，还是采用刚才的执行计划，这也就是存储过程高效的原因，编译一次，多次执行。所以，当你传入的参数值有较大的不同时，会导致参数嗅探。那么第二种是存储过程内部的参数，由于不知道这个参数值是多少，所以sql server采用了一种折中的办法，假设这个变量的值是平均的数据分布，就是数据不多也不少，这种执行计划，一般是不快，也不慢，也是参数嗅探问题。上面两种和你直接把代码放到ssms中执行就不一样了，你在ssms种的是adhoc即席查询，每次都是重新编译，能产生更优化的执行计划。

回复点赞

shoppo0505 2014年01月22日

引用 38 楼 yupeigu 的回复:

[quote=引用 37 楼 shoppo0505 的回复:] [quote=引用 36 楼 yupeigu 的回复:] 不过，我觉得这个应该不太可能是参数嗅探的问题，因为这个参数嗅探，一般不会因为你的sql写法不同，就会有什么不同。

--13.4 Parameter Sniffing
--在使用存储过程的时候，总是要使用到一些变量。变量有两种，一种是在存储过程的外面定义的。
--在调用存储过程的时候，必须要给它带入值。这种变量，SQL Server在编译的时候知道它的值是多少。
drop proc Sniff
go

create proc Sniff (@i int) 
as
	select count(b.SalesOrderID),sum(p.Weight)
	from dbo.SalesOrderHeader_test a 
	inner join dbo.SalesOrderDetail_test b
	on a.SalesOrderID = b.SalesOrderID
	inner join Production.Product p
	on b.ProductID = p.ProductID
	where a.SalesOrderID =@i 
go

--这里的变量@i，就是要在调用的时候带入值的。
exec Sniff 50000
go

--还有一种变量是在存储过程里面定义的。它的值是在存储过程的语句执行的过程中得到的。所以对这种本地变量，SQL Server在编译的时候不知道它的值是多少。例如：
create proc Sniff2 (@i int) 
as
	declare @j int    --这里的变量@j，是SQL在运行的过程中算出来的。
	
	set @j = @i 
	
	select count(b.SalesOrderID),sum(p.Weight)
	from dbo.SalesOrderHeader_test a 
	inner join dbo.SalesOrderDetail_test b
		    on a.SalesOrderID = b.SalesOrderID
	inner join Production.Product p
	        on b.ProductID = p.ProductID
	where a.SalesOrderID =@j 
go
	


--13.4.1 什么是”Parameter Sniffing”
--带着第一个问题，请做下面这两个测试。
--测试 1：第二个效率很差
dbcc freeproccache
go
exec Sniff 50000    -- 发生编译，插入一个使用Nested Loops联结的执行计划
go
exec Sniff 75124    -- 发生执行计划重用，重用上面的Nested Loops的执行计划
go

--测试2：执行计划完全一样,但是2个效率都很差
dbcc freeproccache
go
exec Sniff 75124    -- 发生编译，插入一个使用Hash Match联结的执行计划
go
exec Sniff 50000    -- 发生执行计划重用，重用上面的Hash Match的执行计划
go


--13.4.2 本地变量的影响
--那对于有Parameter Sniffing问题的存储过程，如果使用本地变量，会怎么样呢？
--下面请看测试3。这次用不同的变量值时，都清空执行计划缓存，迫使其重编译。
dbcc freeproccache
go
exec Sniff 50000
go
dbcc freeproccache    
go
exec Sniff 75124         --删除了缓存的执行计划,所以不会重用执行计划,会重新编译产生更优的执行计划.
go

dbcc freeproccache
go
exec Sniff2 50000
go

/*========================================================================
由于删除了缓存的执行计划,所以需要重新编译,会根据某些规则来猜测一个值,这样产生的执行计划可能不是合适.
另外,此执行计划于上面那句是一样的,之所以这样,是因为SQL Server在编译时没法知道这个本地变量的值,
所以只能根据表中数据的分布做一个猜测,而这个猜测的值是一样的,就会导致产生相同的执行计划.
==========================================================================*/
dbcc freeproccache      
go
exec Sniff2 75124
go

[/quote] 有点意思。仔细看看。其实我不是SQL科班出生，这东西真还没接触过，哈哈。

回复点赞

阳泉酒家小当家 2014年01月22日

--13.4.3 Parametre Sniffing的解决方案

--1. 用EXEC()的方式运行动态SQL语句
--如果在存储过程里不是直接运行语句，而是把语句带上变量，生成一个字符串，再让Exec()这样的命令做动态语句运行，那SQL Server就会在运行到这句话的时候，
--对动态语句进行编译,不会重用执行计划。这时SQL Server已经知道了变量的值，会根据值生成优化的执行计划，从而绕过了parameter sniffing的问题。
--例如前面那个Sniff存储过程，就可以改成下面的样子。

create proc NoSniff (@i int) 
as
	declare @cmd varchar(1000)
	set @cmd = 'select count(b.SalesOrderID),sum(p.Weight)
	from dbo.SalesOrderHeader_test a 
	inner join dbo.SalesOrderDetail_test b
	on a.SalesOrderID = b.SalesOrderID
	inner join Production.Product p
	on b.ProductID = p.ProductID
	where a.SalesOrderID =' 
	exec (@cmd + @i)
go

--会得到比它更好的性能。
Dbcc freeproccache
go
exec NoSniff 50000
go
exec NoSniff 75124
go


/*===================================================
2.使用本地变量 local variable

前面提到如果把变量赋给一个本地变量(就是在存储过程内部定义的变量),SQL Server在编译时是没办法知道这个本地变量的值的.
所以会根据表格里数据的一般分布情况,猜测一个返回值,不管用户在调用存储过程时代入的变量值是多少,产生的执行计划都是一样的.

而这样的执行计划一般比较中庸,不会是最优的执行计划,但是对大多数变量值来说,也不会是一个很差的执行计划.
上面13.4.2 本地变量的影响中的Sniff2就是这样的一个例子.
=====================================================*/


--3.在语句中使用query hint,指定执行计划

/*==========================================================
3.1 "Recompile”

"Recompile”这个查询提示告诉SQL Server，语句在每一次存储过程运行的时候，都要重新编译一下。
与上面的使用exec执行动态语句一样,每次运行都会重新编译一次,不会重用执行计划
这样就能够使SQL Server根据当前变量的值，选一个最好的执行计划。
对前面的那个例子，我们可以这么改写。
============================================================*/
drop proc NoSniff_QueryHint_Recompile
go
create proc NoSniff_QueryHint_Recompile (@i int) 
as
	select count(b.SalesOrderID),
	       sum(p.Weight)
	from dbo.SalesOrderHeader_test a 
	inner join dbo.SalesOrderDetail_test b
	        on a.SalesOrderID = b.SalesOrderID
	inner join Production.Product p
	        on b.ProductID = p.ProductID
	where a.SalesOrderID = @i
	option (recompile)
go

Dbcc freeproccache
go
exec NoSniff_QueryHint_Recompile 50000
go
exec NoSniff_QueryHint_Recompile 75124
go



--3.2 和这种方法类似的，是在存储过程的定义里直接指定"recompile"，也能达到避免parameter sniffing的效果。
drop proc NoSniff_SPCreate_Recompile
go
create proc NoSniff_SPCreate_Recompile (@i int) 
with recompile
as
	select count(b.SalesOrderID),sum(p.Weight)
	from dbo.SalesOrderHeader_test a 
	inner join dbo.SalesOrderDetail_test b
	        on a.SalesOrderID = b.SalesOrderID
	inner join Production.Product p
	        on b.ProductID = p.ProductID
	where a.SalesOrderID = @i
go
Dbcc freeproccache
go
exec NoSniff_SPCreate_Recompile 50000
go
exec NoSniff_SPCreate_Recompile 75124
go


/*==========================================================================
3.3 指定Join运算（{ LOOP | MERGE | HASH } JOIN）

但这种方法一般很不推荐,因为不是所有的join,SQL Server都能根据你给的提示做出执行计划的.
如果对上面的存储过程的查询上加上: option(hash join),也就是对这个查询内所有的join都用hash算法，
那么运行时会报错:
	Query processor could not produce a query plan because of the hints defined in this query.
	Resubmit the query without specifying any hints and without using set forceplan.
也就是SQL Server不接受这个查询提示。

所以更常见的，是在特定的那个Join上使用Join Hint。这种方法成功的几率要高得多。

这种方法的好处是保持了存储过程一次编译，后面多次使用的特性，节省编译时间。
但缺点是这种方法生成的执行计划，不一定就是一个最好的执行计划，
而且表里的数据量发生变化后，现在合适的join方式将来可能不合适，到时候会造成性能问题。
===========================================================================*/
drop proc NoSniff_QueryHint_JoinHint
go
create proc NoSniff_QueryHint_JoinHint (@i int) 
as
	select count(b.SalesOrderID),sum(p.Weight)
	from dbo.SalesOrderHeader_test a 
	inner join dbo.SalesOrderDetail_test b
	        on a.SalesOrderID = b.SalesOrderID
	inner hash join Production.Product p
	             on b.ProductID = p.ProductID
	where a.SalesOrderID = @i
go
Dbcc freeproccache
go
exec NoSniff_QueryHint_JoinHint 50000
go
exec NoSniff_QueryHint_JoinHint 75124
go



/*==========================================================================
3.4 OPTIMIZE FOR ( @variable_name = literal_constant [ , ...n ] )

当确认了Parameter Sniffing问题后，会发现，根据某些变量值生成的执行计划，快慢相差很大，
而根据另外一些变量生成的执行计划，性能在好和坏时，相差并不大。
使用” OPTIMIZE FOR”这个查询指导，就能够让SQL Server做到这一点，而且比上面。

比如：当变量值 = 5000，用最好的Nested Loops执行计划，用时10多毫秒，用hash join执行计划，也不过300毫秒
      当变量值 = 75142，用hash join执行计划，需要500毫秒，而用Nested Loops时要用4000多毫秒。
所以折中一下，还是用hash join比较稳定。
      
使用OPTIMIZE FOR这个查询指导，就能够让SQL Server用变量值75142来生产hash join的执行计划，
这样的执行计划就一个比较稳定的查询计划。

它的局限性就是要去修改存储过程的定义，有些时候没有应用开发组的许可，是不可以修改的。
而对sp_executesql的方式调用指令，问题更大，因为指令是写在应用程序中的，而不是在SQL Server中，管理员没法去修改应用程序。
好在SQL Server 2005和2008,引入和完善了一个Plan Guide的功能，DBA可以告诉SQL Server，当运行某个语句时，请用我们指定的执行计划，
这样就不需要修改存储过程或应用了。
===========================================================================*/

drop proc NoSniff_QueryHint_OptimizeFor
go
create proc NoSniff_QueryHint_OptimizeFor (@i int) 
as
	select count(b.SalesOrderID),sum(p.Weight)
	from dbo.SalesOrderHeader_test a 
	inner join dbo.SalesOrderDetail_test b
			on a.SalesOrderID = b.SalesOrderID
	inner join Production.Product p
			on b.ProductID = p.ProductID
	where a.SalesOrderID = @i
	option (optimize for (@i = 75124))
go

Dbcc freeproccache
go
exec NoSniff_QueryHint_OptimizeFor 50000
go
exec NoSniff_QueryHint_OptimizeFor 75124
go


--3.5 Plan Guild
--例如可以用下面的方法，在原来那个有parameter sniffing问题的存储过程”Sniff”上，解决sniffing问题。

EXEC sp_create_plan_guide 
    @name =  N'Guide1',
    @stmt = N'select count(b.SalesOrderID),sum(p.Weight)
from dbo.SalesOrderHeader_test a 
inner join dbo.SalesOrderDetail_test b
on a.SalesOrderID = b.SalesOrderID
inner join Production.Product p
on b.ProductID = p.ProductID
where a.SalesOrderID =@i',
    @type = N'OBJECT',
    @module_or_batch = N'Sniff',
    @params = NULL,
    @hints = N'OPTION (optimize for (@i = 75124))';
go

--使用了75124的Hash Match的Join方式
exec Sniff 50000

go

/*==========================================================================
上面各种方法的比较：

方法							是否修改存储过程/语句	是否每次运行都要重编译	执行计划准确度

用exec()的方式运行动态sql			需要					会						很准确
使用本地变量(local variable)		需要					不会					一般
Query Hint + Reconmpile				需要					会						很准确
Query Hint + 指定Join运算			需要					不会					很一般
Query HInt + Optimize For			需要					不会					比较准确
Plan Guide							不需要					不会				    比较准确
===========================================================================*/

回复点赞

阳泉酒家小当家 2014年01月22日

引用 37 楼 shoppo0505 的回复:

[quote=引用 36 楼 yupeigu 的回复:] 不过，我觉得这个应该不太可能是参数嗅探的问题，因为这个参数嗅探，一般不会因为你的sql写法不同，就会有什么不同。

--13.4 Parameter Sniffing
--在使用存储过程的时候，总是要使用到一些变量。变量有两种，一种是在存储过程的外面定义的。
--在调用存储过程的时候，必须要给它带入值。这种变量，SQL Server在编译的时候知道它的值是多少。
drop proc Sniff
go

create proc Sniff (@i int) 
as
	select count(b.SalesOrderID),sum(p.Weight)
	from dbo.SalesOrderHeader_test a 
	inner join dbo.SalesOrderDetail_test b
	on a.SalesOrderID = b.SalesOrderID
	inner join Production.Product p
	on b.ProductID = p.ProductID
	where a.SalesOrderID =@i 
go

--这里的变量@i，就是要在调用的时候带入值的。
exec Sniff 50000
go

--还有一种变量是在存储过程里面定义的。它的值是在存储过程的语句执行的过程中得到的。所以对这种本地变量，SQL Server在编译的时候不知道它的值是多少。例如：
create proc Sniff2 (@i int) 
as
	declare @j int    --这里的变量@j，是SQL在运行的过程中算出来的。
	
	set @j = @i 
	
	select count(b.SalesOrderID),sum(p.Weight)
	from dbo.SalesOrderHeader_test a 
	inner join dbo.SalesOrderDetail_test b
		    on a.SalesOrderID = b.SalesOrderID
	inner join Production.Product p
	        on b.ProductID = p.ProductID
	where a.SalesOrderID =@j 
go
	


--13.4.1 什么是”Parameter Sniffing”
--带着第一个问题，请做下面这两个测试。
--测试 1：第二个效率很差
dbcc freeproccache
go
exec Sniff 50000    -- 发生编译，插入一个使用Nested Loops联结的执行计划
go
exec Sniff 75124    -- 发生执行计划重用，重用上面的Nested Loops的执行计划
go

--测试2：执行计划完全一样,但是2个效率都很差
dbcc freeproccache
go
exec Sniff 75124    -- 发生编译，插入一个使用Hash Match联结的执行计划
go
exec Sniff 50000    -- 发生执行计划重用，重用上面的Hash Match的执行计划
go


--13.4.2 本地变量的影响
--那对于有Parameter Sniffing问题的存储过程，如果使用本地变量，会怎么样呢？
--下面请看测试3。这次用不同的变量值时，都清空执行计划缓存，迫使其重编译。
dbcc freeproccache
go
exec Sniff 50000
go
dbcc freeproccache    
go
exec Sniff 75124         --删除了缓存的执行计划,所以不会重用执行计划,会重新编译产生更优的执行计划.
go

dbcc freeproccache
go
exec Sniff2 50000
go

/*========================================================================
由于删除了缓存的执行计划,所以需要重新编译,会根据某些规则来猜测一个值,这样产生的执行计划可能不是合适.
另外,此执行计划于上面那句是一样的,之所以这样,是因为SQL Server在编译时没法知道这个本地变量的值,
所以只能根据表中数据的分布做一个猜测,而这个猜测的值是一样的,就会导致产生相同的执行计划.
==========================================================================*/
dbcc freeproccache      
go
exec Sniff2 75124
go

回复点赞

shoppo0505 2014年01月22日

引用 36 楼 yupeigu 的回复:

不过，我觉得这个应该不太可能是参数嗅探的问题，因为这个参数嗅探，一般不会因为你的sql写法不同，就会有什么不同。

完全相同的语句（就是我之前还没有优化的语句），我一个在管理器界面中直接执行，另外一个作为存储过程执行。前者运行需要1分钟不到，后者超过10分钟。我从来没有遇到过这种情况。不知道所以然。

回复点赞

阳泉酒家小当家 2014年01月22日

引用 30 楼 shoppo0505 的回复:

[quote=引用 29 楼 DBA_Huangzj 的回复:] 简单来说，当一个存储过程（带有参数）第一次传入某个参数值，优化器会针对这个参数值生成一个执行计划，并缓存起来，但是当第二次传入一个参数值，而这个参数值可能由于数据分布的差异，不应该重用缓存的，却用了，导致性能变差

你的意思是当存储过程传入多个参数的时候，后面的可能覆盖前面的参数值？不会吧？而且我这次，参数都是在存储过程内部定义的，不是当参数给入的。一个是ID，一个是表格变量，表格中也只有两行数据。[/quote] 不过，我觉得这个应该不太可能是参数嗅探的问题，因为这个参数嗅探，一般不会因为你的sql写法不同，就会有什么不同。

回复点赞

阳泉酒家小当家 2014年01月22日

引用 30 楼 shoppo0505 的回复:

[quote=引用 29 楼 DBA_Huangzj 的回复:] 简单来说，当一个存储过程（带有参数）第一次传入某个参数值，优化器会针对这个参数值生成一个执行计划，并缓存起来，但是当第二次传入一个参数值，而这个参数值可能由于数据分布的差异，不应该重用缓存的，却用了，导致性能变差

你的意思是当存储过程传入多个参数的时候，后面的可能覆盖前面的参数值？不会吧？而且我这次，参数都是在存储过程内部定义的，不是当参数给入的。一个是ID，一个是表格变量，表格中也只有两行数据。[/quote] 尽量少用表格变量，这个没有统计信息，容易产生比较差的执行计划

回复点赞

發糞塗牆 2014年01月22日

CTE主要是用于递归迭代，不是用来替代临时对象的

回复点赞

shoppo0505 2014年01月22日

引用 32 楼 sz_haitao 的回复:

cte与表变量一样不能有索引？表变量还可以有一个主键。。。。看来，还得使用临时表？但递归就麻烦了

可以将CTE和临时表（表变量）参插使用，用cet解决递归问题，结果集存入临时表（表变量），再做后续操作。

引用 31 楼 DBA_Huangzj 的回复:

不是多个参数，比如一个参数1，整个表（10000行）里面有9000是1的，那么应该就是索引扫描等操作，但是只有10行是2，那么应该使用索引查找（假设有索引），但是因为第一次编译时是按1传进去的，缓存的执行计划就用了扫描，这样传入2的时候，它还是扫描，这样就会导致参数为2的时候效率很低

哦，原来如此。我再看看相关资料。

回复点赞

发动态

发帖子

应用实例

创建于2007-09-28

加入

1.1w+

社区成员

6.8w+

社区内容

MS-SQL Server 应用实例

社区公告

暂无公告