临时表VS表变量：因地制宜，合理使用(1)(2)

表变量谓词预估

由于表变量木有统计信息,在优化器知道整体行数的前提下将会根据谓词的情形

采用不同的规则"猜"来进行预估.

注:这里有些规则笔者未找到微软相应的算法文档,经过自己根据数据推算得出.

看到这里的朋友请为我点个赞J(很长时间推算得出.可能数学忘得差不多了)

注:由于检索对象本身及为变量,谓词为变量,或是常数无影响

常见谓词下预估算法:

a ">", "<" 运算符 按照表变量数据量的30%进行预估

b "like" 运算符 按照表变量数据量的10%进行预估

c "=" 运算符 按照表变量数据量的0.75次方预估

实例如图2-3

code

declare @i int 
set @i=13  
DECLARE @T TABLE(I INT);  
INSERT INTO @T VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)  
------表变量中存在个数字  
select * from @T where I < 1  option(recompile) ------20*30% 预估数为6  
select * from @T where I > @i option(recompile) --------20*30%预估数为6  
select * from @T where I like @i  option(recompile) --------20*10% 预估数为2  
select * from @T where I like 1  option(recompile)  --------20*10 预估数为2  
select * from @T where I = @i  option(recompile) --------POWER(20.00000,0.75) 预估数为9.45742  
select * from @T where I = 1  option(recompile)  --------POWER(20.00000,0.75) 预估数为9.45742  
 
insert into @T  
select DatabaseLogID from AdventureWorks2008R2.dbo.DatabaseLog------insert new records  
select * from @T option(recompile) ------------此时数据为行  
select * from @T where I = 1  option(recompile)--------------------POWER(1617.00000,0.75) 预估数为254.99550 

图2-3

可以看出根据不同的谓词优化器会采用不同的预估方式,虽然它不如统计信息下的密度,直方图等来的精确(尤其是等值预估,在数据量巨大的情形下,其效果可能接近统计信息),但在了解数据的前提下如果适合表变量我们还是可以大胆使用的.

Tempdb竞争

tempdb的竞争本身涵盖的知识面比较大,这里我们只讨论临时表与表变量的孰优孰劣.

通过前面的介绍我们知道临时表是支持事务的,而表变量时不支持的.正因如此很多人放弃了表变量的使用.但任何事情都有两方面,支持就一定好吗?由于临时表对事务的支持,在高并发的情形中可能正因为其事务的支持造成系统表锁,总而影响并发.

我们通过一个简单的实例来说明

日常管理中,我发现很多开发人员在使用临时表时采用select * into #t from …的语法,这样的写法如果数据量稍大,将会造成事务持有系统表锁的时间变长,从而影响并发,吞吐.我们通过一个简单的实例说明.如图3-1

Code 我们通过sqlquerystress模拟并发

----SSMS测试数据  
Use tempdb  
create table t  
( id int identity,str1 char(8000))----more pages for many records  
 
insert into t select 'a' 
go 100  
 
----sqlquerystress  
select * into #t  
from t----57s  
 
----sqlquerystress  
declare @t table 
( id int,str1 char(8000))  
insert into @t  
select * from t-----1s 

图3-1

通过图3-1可以看出上述情形中临时表简直不堪重负.临时表与表变量到底该如何应用不是看谁比谁的优点多,应视具体情形而定

结语:借用火影忍者中宇智波. 鼬的一句名言：”任何术都是有缺陷的” 同样,在数据库的世界里没有哪项技术是完美无缺的.根据实际的场景,情形,选择合理的实现方式才是我们的初衷.

原文出自：http://www.cnblogs.com/shanksgao/p/3988089.html