纸风车

随风转呀转。。。

SQL Server中的临时表和表变量

在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的系统表。

阅读更多
个人分类: SQLServer Table
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭