SQL Server中的临时表和表变量

转载 2012年01月18日 10:51:53

在SQLServer的性能调优中,有一个不可比拟的问题:那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表变量和临时表是两种选择。记得在给一家国内首屈一指的海运公司作SQLServer应用性能评估和调优的时候就看到过大量的临时数据集处理需求,而他们的开发人员就无法确定什么时候用临时表,什么时候用表变量,因此他们就简单的使用了临时表。实际上临时表和表变量都有特定的适用环境。

  先卖弄一些基础的知识:

  表变量

  变量都以@或@@为前缀,表变量是变量的一种,另外一种变量被称为标量(可以理解为标准变量,就是标准数据类型的变量,例如整型int或者日期型DateTime)。以@前缀的表变量是本地的,因此只有在当前用户会话中才可以访问,而@@前缀的表变量是全局的,通常都是系统变量,比如说@@error代表最近的一个T-SQL语句的报错号。当然因为表变量首先是个变量,因此它只能在一个Batch中生存,也就是我们所说的边界,超出了这个边界,表变量也就消亡了。

  表变量存放在内存中,正是因为这一点所有用户访问表变量的时候SQLServer是不需要生成日志。同时变量是不需要考虑其他会话访问的问题,因此也不需要锁机制,对于非常繁忙的系统来说,避免锁的使用可以减少一部分系统负载。

  表变量另外还有一个限制就是不能创建索引,当然也不存在统计数据的问题,因此在用户访问表变量的时候也就不存在执行计划选择的问题了(也就是以为着编译阶段后就没有优化阶段了),这一特性有的时候是件好事,而有些时候却会造成一些麻烦。

  临时表

  临时对象都以#或##为前缀,临时表是临时对象的一种,还有例如临时存储过程、临时函数之类的临时对象,临时对象都存储在tempdb中。以#前缀的临时表为本地的,因此只有在当前用户会话中才可以访问,而##前缀的临时表是全局的,因此所有用户会话都可以访问。临时表以会话为边界,只要创建临时表的会话没有结束,临时表就会持续存在,当然用户在会话中可以通过DROPTABLE命令提前销毁临时表。

  我们前面说过临时表存储在tempdb中,因此临时表的访问是有可能造成物理IO的,当然在修改时也需要生成日志来确保一致性,同时锁机制也是不可缺少的。

  跟表变量另外一个显著去别就是临时表可以创建索引,也可以定义统计数据,因此SQLServer在处理访问临时表的语句时需要考虑执行计划优化的问题。

  表变量vs. 临时表

表变量

临时表

数据集的存储位置

内存(不考虑被换到页面文件这种情况)

磁盘(不考虑访问后被缓存到内存中)

是否需要日志

是否可以创建索引

是否可以使用统计数据

是否可以在多会话中访问

是否需要锁机制

  结论

  综上所述,大家会发现临时表和表变量在底层处理机制上是有很多差别的。

  简单地总结,我们对于较小的临时计算用数据集推荐使用表变量。如果数据集比较大,如果在代码中用于临时计算,同时这种临时使用永远都是简单的全数据集扫描而不需要考虑什么优化,比如说没有分组或分组很少的聚合(比如说COUNT、SUM、AVERAGE、MAX等),也可以考虑使用表变量。使用表变量另外一个考虑因素是应用环境的内存压力,如果代码的运行实例很多,就要特别注意内存变量对内存的消耗。

  一般对于大的数据集我们推荐使用临时表,同时创建索引,或者通过SQLServer的统计数据(Statisitcs)自动创建和维护功能来提供访问SQL语句的优化。如果需要在多个用户会话间交换数据,当然临时表就是唯一的选择了。需要提及的是,由于临时表存放在tempdb中,因此要注意tempdb的调优。

附:

表变量和临时表的比较:

·        临时表是利用了硬盘(tempdb数据库),表名变量是占用内存,因此小数据量当然是内存中的表变量更快。当大数据量时,就不能用表变量了,太耗内存了。大数据量时适合用临时表。

·        表变量缺省放在内存,速度快,所以在触发器,存储过程里如果数据量不大,应该用表变量。

·        临时表缺省使用硬盘,一般来说速度比较慢,那是不是就不用临时表呢?也不是,在数据量比较大的时候,如果使用表变量,会把内存耗尽,然后使用TEMPDB的空间,这样主要还是使用硬盘空间,但同时把内存基本耗尽,增加了内存调入调出的机会,反而降低速度。这种情况建议先给TEMPDB一次分配合适的空间,然后使用临时表。

·        临时表相对而言表变量主要是多了I/O时间,但少了对内存资源的占用。数据量较大的时候,由于对内存资源的消耗较少,使用临时表比表变量有更好的性能。

·        建议:触发器、自定义函数用表变量;存储过程看情况,大部分用表变量;特殊的应用,大数据量的场合用临时表。

·        表变量有明确的作用域,在定义表变量的函数、存储过程或批处理结束时,会自动清除表变量。

·        在存储过程中使用表变量与使用临时表相比,减少了存储过程的重新编译量。

·        涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。

·        表变量需要事先知道表结构,普通临时表,只在当前会话中可用与表变量相同into一下就可以了,方便;全局临时表:可在多个会话中使用存在于temp中需显示的drop。(不知道表结构情况下临时表方便一些)

·        全局临时表的功能是表变量没法达到的。

·        表变量不必删除,也就不会有命名冲突,临时表特别是全局临时表用的时候必须解决命名冲突。

·        应避免频繁创建和删除临时表,减少系统表资源的消耗。

·        在新建临时表时,如果一次性插入数据量很大,那么可以使用selectinto代替create table,避免log,提高速度;如果数据量不大,为了缓和系统表的资源,建议先createtable,然后insert。

·        如果临时表的数据量较大,需要建立索引,那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中,这样才能保证系统能够很好的使用到该临时表的索引。

·        如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先truncatetable,然后drop table,这样可以避免系统表的较长时间锁定。

·        慎用大的临时表与其他大表的连接查询和修改,减低系统表负担,因为这种操作会在一条语句中多次使用tempdb的系统表。

SQL Server 表变量和临时表系列之概念篇

问题引入 “菜鸟啊,最近我看到阿里云开发者论坛的数据库RDS中有人在提SQL Server表变量和临时表如何选择的问题,你去深入探讨下这个问题吧,解答解答他们的疑惑吧”,老鸟又开始为菜鸟找活干了...
  • lishimin1012
  • lishimin1012
  • 2017年01月06日 14:37
  • 544

SQL Server 表变量与临时表区别

问题 1:为什么在已经有了临时表的情况下还要引入表变量?解答 1:与临时表相比,表变量具有下列优点: • 如 SQL Server ...
  • lovehongyun
  • lovehongyun
  • 2008年01月09日 09:27
  • 10991

sqlserver 中临时表、临时变量和with as关键词创建“临时表”的区别

SQL数据库中数据处理时,有时候需要建立临时表,将查询后的结果集放到临时表中,然后在针对这个数据进行操作。   创建“临时表”(逻辑上的临时表,可能不一定是数据库的)的方法有一下几种:  ...
  • miqi770
  • miqi770
  • 2016年05月26日 15:39
  • 3956

SQL server 临时表和表变量

临时表临时表与永久表相似,只是它的创建是在Tempdb中,它只有在一个数据库连接结束后或者由SQL命令DROP掉,才会消失,否则就会一直存在。临时表在创建的时候都会产生SQL Server的系统日志,...
  • misol
  • misol
  • 2010年12月15日 09:25
  • 616

SQL Server临时表和表变量

文章主要描述的是SQL Server临时表和表变量在实际操作中的区别介绍,我们在数据库中实际操作中,对于表的使用的时候,一般都会使用两种使用表的方法,分别就是使用临时表及表变量。在实际使用的时候,我们...
  • andycode
  • andycode
  • 2011年07月25日 11:09
  • 407

SQL SERVER临时表的使用&表变量的使用

临时表: 创建临时表可以创建本地和全局临时表。本地临时表仅在当前会话中可见;全局临时表在所有会话中都可见。 本地临时表的名称前面有一个编号符 (#table_name),而全局临时表的名称前面有两个...
  • S630730701
  • S630730701
  • 2016年08月03日 16:57
  • 2450

SQL Server中的临时表和表变量

SQL Server中的临时表和表变量 http://www.weaseek.com  2008-07-08 11:49:33  来源:IT专家网 在SQL Server的性能调优中,有一个不可比拟的...
  • wuzhong369
  • wuzhong369
  • 2009年02月16日 14:19
  • 324

SQL Server中的临时表和表变量

在SQL Server的性能调优中,有一个不可比拟的问题:那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表变量和临时表是两种选择。   在SQL Server的性能...
  • socoldinxian
  • socoldinxian
  • 2013年08月30日 17:36
  • 516

sql临时表:SQL Server中的临时表和表变量

在SQL Server的性能调优中,有一个不可比拟的问题:那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表变量和临时表是两种选择。  在SQL Server的性能调优中,有一个不...
  • dingchenglong00
  • dingchenglong00
  • 2010年03月04日 08:55
  • 176

利用临时表对查询数据重新排序

先创建临时表,然后把查询结果存入临时表,最后从临时表查询数据并按需要重新排序。...
  • gaofeng2000
  • gaofeng2000
  • 2010年10月29日 23:27
  • 1324
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server中的临时表和表变量
举报原因:
原因补充:

(最多只允许输入30个字)