`
uule
  • 浏览: 6297672 次
  • 性别: Icon_minigender_1
  • 来自: 一片神奇的土地
社区版块
存档分类
最新评论

表变量与临时表的优缺点

 
阅读更多

SQL知识整理一:触发器、存储过程、表变量、临时表

什么情况下使用表变量?
什么情况下使用临时表?

 

------------------------------------------------------------------------------------------------------------------------------

表变量在批处理结束时自动被系统删除,所以你不必要像使用临时表一样显示的对它进行删除.
表变量和临时表针对我们使用人员来说并没有什么不同,但是在存储方面来说,他们是不同的,表变量存储在内存中.所以在性能上和临时表相比会更好些! 
3个理论上的不同。
第一个不同是事务日志不会记录表变量。因此它们脱离了事务机制的范围。
第二个主要的不同是任何一个使用临时表的存储过程都不会被预编译,然而使用表变量的存储过程的执行计划可以预先静态的编译。预编译一个脚本的主要好处在于加快了执行的速度。这个好处对于长的存储过程更加显著因为对它来说重新编译代价太高.
最后表变量仅存在于那些变量能存在的相同范围内。和临时表相反它们在内部存储过程和execstring语句里是不可见的。它们也不能在insert/exec语句里使用


------------------------------------------------------------------------------------------------------------------------------

表变量只存放在内存中,临时表需要写磁盘。所以一般情况下,用表变量会快些。

用法不太一样,有些时候用表变量更方便。

 

1.表变量在内存中,临时表存放在硬盘上;
2.用临时表要考虑锁不锁表的问题;
3.数据量太大应该用临时表。

 

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

------------------------------------------------------------------------------------------------------------------------------

肤浅理解:
表变量:需要事先知道表结构
普通临时表:只在当前会话中可用与表变量相同 into一下就可以了,方便
全局临时表:可在多个会话中使用存在于temp中需显示的drop


------------------------------------------------------------------------------------------------------------------------------

要从表变量的作用域,支持不支持的操作,机器内存大小等几方面考虑。
如:
.表变量相当于ADO的RECORDSET,速度比临时表快得多。
表变量不能用在下列语句中:
INSERT INTO table_variable EXEC 存储过程。
SELECT select_list INTO table_variable 语句。


在定义 table 变量的函数、存储过程或批处理结束时,自动清除 table 变量。
但临时表支持。


.表变量速度比临时表快得多(如果内存足够)
如果数据量不大:
微软 BOOK ON LINE 内说:尽可能使用表变量而不使用临时表
------------------------------------------------------------------------------------------------------------------------------

 

表变量有以下优点

table 变量的行为类似于局部变量,有明确定义的作用域。该作用域为声明该变量的函数、存储过程或批处理。
在其作用域内,table 变量可像常规表那样使用。该变量可应用于 SELECT、INSERT、UPDATE 和 DELETE 语句中用到表或表的表达式的地方。

但是,table 不能用在下列语句中:

INSERT INTO table_variable EXEC 存储过程。

SELECT select_list INTO table_variable 语句。

在定义 table 变量的函数、存储过程或批处理结束时,自动清除 table 变量。

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


涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。
不支持在表变量之间进行赋值操作。另外,由于表变量作用域有限,并且不是持久数据库的一部分,因而不受事务回滚的影响。

 

表变量和临时表对比总结

特性

表变量

临时表

作用域

当前批处理

当前会话,嵌套存储过程,全局:所有会话

使用场景

自定义函数,存储过程,批处理

自定义函数,存储过程,批处理

创建方式

DECLARE statement only.只能通过DECLEARE语句创建

CREATE TABLE 语句

SELECT INTO 语句.

表名长度

最多128字节

最多116字节

列类型

可以使用自定义数据类型

可以使用XML集合

自定义数据类型和XML集合必须在TempDb内定义

Collation

字符串排序规则继承自当前数据库

字符串排序规则继承自TempDb数据库

索引

索引必须在表定义时建立

索引可以在表创建后建立

约束

PRIMARY KEY, UNIQUE, NULL, CHECK约束可以使用,但必须在表建立时声明

PRIMARY KEY, UNIQUE, NULL, CHECK. 约束可以使用,可以在任何时后添加,但不能有外键约束

表建立后使用DDL (索引,列)

不允许

允许.

数据插入方式

INSERT 语句 (SQL 2000: 不能使用INSERT/EXEC).

INSERT 语句, 包括 INSERT/EXEC.

SELECT INTO 语句.

Insert explicit values into identity   columns (SET IDENTITY_INSERT).

不支持SET IDENTITY_INSERT语句

支持SET IDENTITY_INSERT语句

Truncate table

不允许

允许

析构方式

批处理结束后自动析构

显式调用 DROP TABLE 语句.
  当前会话结束自动析构 (全局临时表: 还包括当其它会话语句不在引用表.)

事务

只会在更新表的时候有事务,持续时间比临时表短

正常的事务长度,比表变量长

存储过程重编译

会导致重编译

回滚

不会被回滚影响

会被回滚影响

统计数据

不创建统计数据,所以所有的估计行数都为1,所以生成执行计划会不精准

创建统计数据,通过实际的行数生成执行计划。

作为参数传入存储过程

仅仅在SQL Server2008, 并且必须预定义   user-defined table type.

不允许

显式命名对象 (索引, 约束).

不允许

允许,但是要注意多用户的问题

动态SQL

必须在动态SQL中定义表变量

可以在调用动态SQL之前定义临时表

 

二、

表变量存储在内存中,而临时表存储在tempdb中,会涉及到物理IO读写,那么我们是否可以由此得出结论,使用表变量要比使用临时表效率高呢?相信有一部分人会和我有同样的想法,使用表变量的效率高,真是如此吗?先从一次优化存储过程的经历说起。

      存储过程涉及到两个表,一个是用户今日积分表@tableUserScore(数据源来自用户积分详情表中的今日数据),一个是用户积分统计表 UserScoreSum,该存储过程逻辑就是统计@tableUserScore中用户不同原因的积分值,生成到表UserScoreSum中。数据量 不算很大,@tableUserScore中大概40万条,但这个存储过程执行时间却有些惊人,通常都在1个小时之上。优化的最终结果是将表变量@tabeUserScore换成了临时表#tableUserScore,并在userid和reason上添加了联合索引,优化的效果是执行时间控制在了40S左右。临时表和表变量效率相差百倍,这次优化经历让我对临时表和表变量有了重新认识,也有了一连串的疑问,它们是如何存储的,效率如何,如何选用?

declare@tableUserScoretable(
userid int, --用户编号
name varchar(10), --用户姓名
reason varchar(32), --积分原因
score int--积分值
)

createtable UserScoreSum(
userid int, --用户编号
name varchar(10), --用户姓名
createTime datetime, --时间
reason1Score int, --原因1积分值
reason2Score int, --原因2积分值
reason3Score int, --原因3积分值
reason4Score int, --原因4积分值
)

 

     以下是个人翻阅资料后的理解,总结出来希望能给和我有同样认识的人提个醒,起到抛砖引玉的作用,也希望大家对理解错误之处提出指正。

     临时表

     临时表有两种类型:本地表和全局表。在与首次创建或引用表时相同的 SQL Server 实例连接期间,本地临时表只对于创建者是可见的。当用户与 SQL Server 实例断开连接后,将删除本地临时表。全局临时表在创建后对任何用户和任何连接都是可见的,当引用该表的所有用户都与 SQL Server 实例断开连接后,将删除全局临时表。本地临时表的名称都是以“#”为前缀,全局临时表的名称都是以“##”为前缀。

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

     临时表可以创建索引,也可以定义统计数据,所以可以用数据定义语言(DDL)的声明来阻止临时表添加的限制,约束,并参照完整性,如主键和外键约束。

     表变量

     表变量是变量的一种,表变量也分为本地及全局的两种,本地表变量的名称都是以“@”为前缀,只有在本地当前的用户连接中才可以访问。全局的表变量的名称都 是以“@@”为前缀,一般都是系统的全局变量,像我们常用到的,如@@Error代表错误的号,@@RowCount代表影响的行数。

     表变量存放在内存中,正是因为这一点所有用户访问表变量的时候SQL Server是不需要生成日志。同时变量是不需要考虑其他会话访问的问题,因此也不需要锁机制,对于非常繁忙的系统来说,避免锁的使用可以减少一部分系统负载。[表变量存放在内存是有一定限制的,如果表变量数据量超过阈值,会把内存耗尽,然后使用TempDB的空间,这样主要还是使用硬盘空间,但同时把内存基本耗尽,增加了内存调入调出的机会,反而降低速度]

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

     临时表 vs. 表变量

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

     2.性能:不能一概而论,表变量存储数据有个性能临界点,在这个临界点之内,表变量比临时表快,表变量是存储在内存中的。

     3.索引:表变量不支持索引和统计数据,但可以有主键;临时表则可以支持索引和统计数据。

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

 

http://www.cnblogs.com/freshman0216/archive/2010/11/14/1868672.html

 

分享到:
评论

相关推荐

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

    说临时表和表变量,这是一个古老的话题,我们在网上也找到很多的资料阐述两者的特征,优点与缺点

    关于sqlserver 2005 使用临时表的问题( Invalid object name #temptb)

    使用表变量代替临时表,这样做法可以避免SSRS 2005 的语法错误提示,缺点就是如果处理的数据量比较大,会占用过大的内存,并且报表加载时间过长。 2.使用一个不包含临时表的SQL语句创建报表,创建成功以后,修改...

    PL/SQL 基础.doc

    表示变量具有与数据库的表中某一字段相同的类型 例:v_FirstName s_emp.first_name%TYPE; 3. RECORD类型 TYPE t_emp IS RECORD( /*其中TYPE,IS,RECORD为关键字,record_name为变量名称*/ field1 type [NOT...

    数据库和ado知识

    数据库语言 数据库的简易流程(数据库客户端软件和数据库服务软件的执行流程) ...三层结构的优缺点 邮件发送方法 Excel导入导出 MD5加密解密方法 读取数据库后,判断dataset里列的值是否为空 项目术语

    知名公司数据结构笔试题及答案

    哈希表和数组的定义,区别,优缺点。 12.链接表和数组之间的区别是什么? 任选一门语言,当场定义二叉排序树数据结构,写出两个函数:初始化,删除一个节点,20分钟 13. 递归的折半查找算法[不限语言] 14. 解释...

    Oracle8i_9i数据库基础

    §3.1.3 建立临时表结构 97 §3.3 修改表结构 98 §3.3.1 修改表结构命令 98 §3.3.2 修改表结构例子 99 §3.3.3 删除表结构 101 §3.3.4 使用CHECK作限制约束 101 §3.3.5 使用UNRECOVERABLE创建表 102 §3.3.6 将...

    mysql数据库my.cnf配置文件

    所有联合在一个DML指令内完成,并且大多数联合甚至可以不用临时表即可以完成。 # 大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。 # 如果某个内部...

    数据库基础

    §3.1.3 建立临时表结构 97 §3.3 修改表结构 98 §3.3.1 修改表结构命令 98 §3.3.2 修改表结构例子 99 §3.3.3 删除表结构 101 §3.3.4 使用CHECK作限制约束 101 §3.3.5 使用UNRECOVERABLE创建表 102 §3.3.6 将...

    大数据面试题.pdf

    Class.forName("xx.xx")等同于Class.forName("xx.xx",true,CALLClass.class.getClassLoader()),第⼆个参数(bool)表⽰装载类的时候是否 初始化该类,即调⽤类的静态块的语句及初始化静态成员变量。 ClassLoader ...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

     删除环境变量中的PATHT CLASSPATH中包含Oracle的值。  删除“开始”/“程序”中所有Oracle的组和图标。  删除所有与Oracle相关的目录,包括: C:\Program file\Oracle目录。 ORACLE_BASE目录。 C:\Documents ...

    操作系统相关题目总结

     *进程虚拟地址空间包括:代码段、堆栈段(临时数据:如函数参数、返回地址和局部变量)、数据段(包括全局变量)、堆(动态分配的内存)等等。  3.线程运行在进程的上下文中,所有运行在一个进程内的线程共享该进程...

    Access+2000中文版高级编程

    8.5.3 生成临时的查询对象QueryDef 210 8.5.4 使用DoCmd对象的RunSQL方法 211 8.5.5 在VBA中实现参数查询 211 8.6 用查询产生报表与窗体 212 8.7 用查询解决问题 213 8.7.1 分组以获得百分比 213 8.7.2 ...

    Access 2000中文版高级编程(part1)

    8.5.3 生成临时的查询对象QueryDef 210 8.5.4 使用DoCmd对象的RunSQL方法 211 8.5.5 在VBA中实现参数查询 211 8.6 用查询产生报表与窗体 212 8.7 用查询解决问题 213 8.7.1 分组以获得百分比 213 8.7.2 查找并...

    Oracle数据库管理员技术指南

    7.8.7 恢复丢失的临时表空间 7.8.8 只读表空间丢失 7.8.9 索引表空间丢失 7.9 恢复联机重做日志 7.9.1 联机重做日志组某个成员丢失 7.9.2 非活动的重做日志组丢失 7.9.3 活动重做日志组丢失 7.10 恢复丢失的...

    SQL.Server.2008编程入门经典(第3版).part1.rar

    索引的优缺点 锁和死锁对系统性能的各种影响 理解触发器及其使用方式 《SQL Server 2008编程入门经典(第3版)》读者对象 《SQL Server 2008编程入门经典(第3版)》适合于希望全面了解数据库设计概念和学习SQL的开发...

    SQL.Server.2008编程入门经典(第3版).part2.rar

    索引的优缺点 锁和死锁对系统性能的各种影响 理解触发器及其使用方式 《SQL Server 2008编程入门经典(第3版)》读者对象 《SQL Server 2008编程入门经典(第3版)》适合于希望全面了解数据库设计概念和学习SQL的开发...

    MySQL中文参考手册

    + 7.1.5 数据库,表,索引,列和别名的命名 # 7.1.5.1 名字的大小写敏感性 o 7.2 用户变量 o 7.3 列类型 + 7.3.1 列类型的存储要求 + 7.3.5 数字类型 + 7.3.6 日期和时间类型 # 7.3.6.1 问题和日期类型 # ...

    C#编程经验技巧宝典

    C#编程经验技巧宝典源代码,目录如下: 第1章 开发环境 1 <br>1.1 Visual Studio开发环境安装与配置 2 <br>0001 安装Visual Studio 2005开发环境须知 2 <br>0002 配置合适的Visual Studio 2005...

Global site tag (gtag.js) - Google Analytics