临时表VS表变量--因地制宜,合理使用

原文:临时表VS表变量--因地制宜,合理使用

一直以来大家对临时表与表变量的孰优孰劣争论颇多,一些技术群里的朋友甚至认为表变量几乎一无是处,比如无统计信息,不支持事务等等.但事实并非如此.这里我就临时表与表变量做个对比,对于大多数人不理解或是有歧义的地方进行详细说明.
注:这里只讨论一般临时表,对全局临时表不做阐述.
生命周期
临时表:会话中,proc中,或使用显式drop
表变量:batch中
这里用简单的code说明表变量作用域
DECLARE @t TABLE(i int) ----定义表变量@t

SELECT *FROM @t        -----访问OK

insert into @t select 1 -----插入数据OK

select * from  @t      -------访问OK
go                     -------结束批处理
select * from @t       -------不在作用域出错
注意:虽然说sqlserver在定义表变量完成前不允许你使用定义的变量.但注意下面情况仍然可正常运行!




if 'a'='b'
begin
DECLARE @t TABLE(i int)
end
SELECT *FROM @t        -----仍然可以访问！





日志机制


临时表与表变量都会记录在tempdb中记录日志


不同的是临时表的活动日志在事务完成前是不能截断的.


这里应注意的是由于表变量不支持truncate,所以完全清空对象结果集时临时表有明显优势,而表变量只能delete


事务支持


临时表:支持


表变量:不支持


我们通过简单的实例加以说明



create table #t (i int)
declare @t table(i int)BEGIN TRAN ttt
insert into #t select 1
insert into @t select 1
SELECT * FROM #t  ------returns 1 rows
SELECT * FROM @t  ------returns 1 rows
ROLLBACK tran tttSELECT * FROM #t    -------no rows
SELECT * FROM @t    -------still 1 rows
drop table #t       ----no use drop @t in session


 


锁机制(select)


临时表 会对相关对象加IS(意向共享)锁


表变量 会对相关对象加SCH-S(架构共享)锁(相当于加了nolock hint) 


可以看出虽说锁的影响范围不同,但由于作用域都只是会话或是batch中,临时表的IS锁虽说兼容性不如表变量的SCH-S但绝大多数情况基本无影响.


感兴趣的朋友可以用TF1200测试


索引支持


临时表  支持


表变量  条件支持(仅SQL2014)


没错,在sql2014中你可以在创建表的同时创建索引 图1-1


注:在sql2014之前表变量只支持创建一个默认的唯一性约束code



DECLARE @t TABLE
(
col1 int index inx_1 CLUSTERED,
col2 int  index index_2 NONCLUSTERED,
       index index_3 NONCLUSTERED(col1,col2)
)





                                图1-1


 


用户自定义函数(UDFs)


临时表 不支持作为UDF的结果集返回


表变量 支持作为UDF的结果集返回


注:当表变量作为UDF的结果集返回时分为TVF(Table-Valued Function),TVP(Table-Valued Parameters)两种类型,只有TVF支持plan cache


如图1-2Code



CREATE FUNCTION TVP_Customers (@cust nvarchar(10))
RETURNS TABLE
AS
 RETURN
 (SELECT RowNum, CustomerID, OrderDate, ShipCountry
 FROM BigOrders
 WHERE CustomerID = @cust);
GO
CREATE FUNCTION TVF_Customers (@cust nvarchar(10))
RETURNS @T TABLE (RowNum int, CustomerID nchar(10), OrderDate date,
 ShipCountry nvarchar(30))
AS
BEGIN
 INSERT INTO @T
  SELECT RowNum, CustomerID, OrderDate, ShipCountry
  FROM BigOrders
  WHERE CustomerID = @cust
  RETURN
END;

DBCC FREEPROCCACHE
GO
SELECT * FROM TVF_Customers('CENTC');
GO
SELECT * FROM TVP_Customers('CENTC');
GO
SELECT * FROM TVF_Customers('SAVEA');
GO
SELECT * FROM TVP_Customers('SAVEA');
GO

select b.text,a.execution_count,a.* from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.sql_handle) b
where b.text like '%_Customers%'


 




                                                                        图1-2


 


其它方面


表变量不支持select into，alter，truncate，dbcc等


表变量不支持table hint 如(force seek)


 


执行计划预估


我想这里可能是引起使用何种方式争论比较突出的地方,由于表变量没有统计信息,无法添加索引等使得大家对其在执行计划中的性能表现嗤之以鼻,但实际情况呢?我们需要深入分析.


关于临时表的预估这里我就不做介绍了,主要对表变量的预估做详细阐述.


表变量在sql2000引入的一个原因就是为了在一些执行过程中减少重编译.以获得更好的性能.当然带来好处的同时也会带来一定弊端.由于其不涉及重编译,优化器其实并不知道表变量中的具体行数,此时他采取了保守的预估方式:预估行数为1行.如图2-1


 Code


declare @t table (i int)
select * from @t-----此时0行预估行数为1行
insert into @t select 1
select * from @t-----此时1行,预估行数仍为1行
insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)
select * from @t ----此时19行,预估行数仍为1行

--....无论实际@t中有多少行,由于没有重编译,预估均为1行


 


 




                                                                             图2-1


 所以当我们加上重编译的的操作,此时优化器就知道了表变量的具体行数.如图2-2


Code


 


declare @t table (i int)
select * from @t option(recompile)-----此时0行预估行数为1行
insert into @t select 1
select * from @t  option(recompile)-----此时1行,预估行数为1行
insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)
select * from @t  option(recompile)----此时19行,预估行数为19行
--....当加入重编译hint时,优化器就知道的表变量的行数.


 




                                                                     图2-2


 


至此,我们可以看到优化器知道了表变量中的行数.这样在表变量扫描的过程中,尤其针对数据量较大的情形,不会因为预估总是1而引起一些问题.


如果你刚知道这里的预估原理,现有的代码都加上重编译那工作量可想而知了..这里介绍一个新的跟踪标记,Trace Flag 2453.


TF2453可以一定程度上替代重编译Hint,但只是在非简单计划（trivial plans）的情形下


注:TF2453只在sql2012 SP2和SQL2014中的补丁中起作用



表变量谓词预估


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


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


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


看到这里的朋友请为我点个赞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可以看出上述情形中临时表简直不堪重负.临时表与表变量到底该如何应用不是看谁比谁的优点多,应视具体情形而定


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


 
				


				
时间： 2024-10-21 17:13:18

		
		


		


	

	
	

		


		
临时表VS表变量--因地制宜,合理使用的相关文章


								

		

			

                SQL Server中临时表与表变量有什么区别
			

		

		

									

				我们在数据库中使用表的时候,经常会遇到两种使用表的方法,分别就是使用临时表及表变量.在实际使用的时候,我们如何灵活的在存储过程中运用它们,虽然它们实现的功能基本上是一样的,可如何在一个存储过程中有时候去使用临时表而不使用表变量,有时候去使用表变量而不使用临时表呢? 临时表 临时表与永久表相似,只是它的创建是在Tempdb中,它只有在一个数据库连接结束后或者由SQL命令DROP掉,才会消失,否则就会一直存在.临时表在创建的时候都会产生SQLServer的系统日志,虽它们在Tempdb中体现,是分配			

		

	

				

		

			

                SQL Server中临时表与表变量的区别
			

		

		

									

				  我们在数据库中使用表的时候,经常会遇到两种使用表的方法,分别就是使用临时表及表变量.在实际使用的时候,我们如何灵活的在存储过程中运用它们,虽然它们实现的功能基本上是一样的,可如何在一个存储过程中有时候去使用临时表而不使用表变量,有时候去使用表变量而不使用临时表呢? 临时表 临时表与永久表相似,只是它的创建是在Tempdb中,它只有在一个数据库连接结束后或者由SQL命令DROP掉,才会消失,否则就会一直存在.临时表在创建的时候都会产生SQLServer的系统日志,虽它们在Tempdb中体现,是			

		

	

				

		

			

                【T-SQL系列】临时表、表变量
			

		

		

									

				原文:[T-SQL系列]临时表.表变量 临时表临时表与永久表相似,只是它的创建是在Tempdb中,它只有在一个数据库连接结束后或者由SQL命令DROP掉,才会消失,否则就会一直存在.临时表在创建的时候都会产生SQL Server的系统日志,虽它们在Tempdb中体现,是分配在内存中的,它们也支持物理的磁盘,但用户在指定的磁盘里看不到文件. 临时表分为本地和全局两种,本地临时表的名称都是以"#"为前缀,只有在本地当前的用户连接中才是可见的,当用户从实例断开连接时被删除.全局临时表的名称都			

		

	

				

		

			

                SQL Server 临时表和表变量系列之踢馆篇
			

		

		

									

				摘要 在面对SQL Server选择使用临时表还是表变量作为数据暂存问题时,有一个非常重要的选择标准便是性能,两者对于查询语句和DML性能表现到底如何呢?我相信,很多人的认识是片面的,或者是错误的.这里以一篇引用率很高的文章来作为反面教材来纠正那些片面和错误的认识,我暂且称之为"踢馆". 背景 在研究临时表和表变量该如何选择的时候,一篇文章叫着SQL Server Temp Table vs Table Variable Performance Testing文章引用率是非常高的.通读			

		

	

				

		

			

                SQL Server 临时表和表变量系列之选择篇
			

		

		

									

				摘要 通过前面的三篇系列文章,我们对临时表和表变量的概念.对比和认知误区已经有了非常全面的认识.其实,我们的终极目的,还是今天要讨论的话题,即当我们面对具体的业务场景的时候,该选择临时表还是表变量? 几种典型场景 以下是几种典型的场景,让我们看看到底该作何选择,以及做出最终选择的具体原因和考量. 存储过程嵌套 在SQL Server中,使用存储过程的好处显而易见,往往会节约存储过程执行计划编译时间,提高查询语句的执行效率.有时候,我们在构建存储过程多层次嵌套场景中,会有内层存储过程需要临时使用外			

		

	

				

		

			

                sqlserver 临时表 Vs 表变量 详细介绍_MsSql
			

		

		

									

				这里我们在SQL Server 2005\SQL Server 2008版本上通过举例子,说明临时表和表变量两者的一些特征,让我们对临时表和表变量有进一步的认识.在本章中,我们将从下面几个方面去进行描述,对其中的一些特征举例子说明: 约束(Constraint) 索引(Index) I/0开销 作用域(scope) 存儲位置 其他   例子描述 约束(Constraint)            在临时表和表变量,都可以创建Constraint.针对表变量,只有定义时能加Constraint.			

		

	

				

		

			

                sqlserver 临时表 Vs 表变量 详细介绍
			

		

		

									

				这里我们在SQL Server 2005\SQL Server 2008版本上通过举例子,说明临时表和表变量两者的一些特征,让我们对临时表和表变量有进一步的认识.在本章中,我们将从下面几个方面去进行描述,对其中的一些特征举例子说明: 约束(Constraint) 索引(Index) I/0开销 作用域(scope) 存儲位置 其他 例子描述 约束(Constraint) 在临时表和表变量,都可以创建Constraint.针对表变量,只有定义时能加Constraint. e.g.在Microsof			

		

	

				

		

			

                SQLServer中临时表与表变量的区别分析
			

		

		

									

				在实际使用的时候,我们如何灵活的在存储过程中运用它们,虽然它们实现的功能基本上是一样的,可如何在一个存储过程中有时候去使用临时表而不使用表变量,有时候去使用表变量而不使用临时表呢? 临时表 临时表与永久表相似,只是它的创建是在Tempdb中,它只有在一个数据库连接结束后或者由SQL命令DROP掉,才会消失,否则就会一直存在.临时表在创建的时候都会产生SQL Server的系统日志,虽它们在Tempdb中体现,是分配在内存中的,它们也支持物理的磁盘,但用户在指定的磁盘里看不到文件. 临时表分为本地			

		

	

				

		

			

                再议SQL Server临时表和表变量
			

		

		

									

				今天在我和一家软件公司的开发人员讨论数据库设计调优的时候又讨论到了表变量和临时表的问题,觉得这个问题确实是一个争议比较大的问题. 其实从上次发表了表变量和临时表的一个帖子http://database.ctocio.com.cn/tips/442/8206442.shtml以来,也有些人留言,也有些人发过邮件讨论这个问题.其实表变量和临时表的区别虽然有一些,但是两者最根本的区别还是在于 对存储的需求:表变量和临时表都消耗Tempdb中的存储空间,但是进行数据更新的时候,表变量不会写日志,而临时表