存储过程里大量使用临时表的优缺点?请指教

[ccshigenvwa]

1。存储过程,和表值函数,在写统计的时候常用到的临时表,没执行一次存储过程或表值函数,如果没有删除掉临时表,就会不停的创建临时表,会不会因为这样导致服务器死掉?
2。临时表与SQL拼接后使用EXEC()相比哪个效率更高呢?
3。觉得在有的时候 把查询出来的一些数据放到临时表然后再多个临时表关联起来查询似乎比直接拼SQL用exec要慢,因为我个人觉得吧查询的数据保存到临时表在进行关联查询本身就是多了一道工序可以这样理解么?

[叶子]

http://wenku.baidu.com/view/250f084d2e3f5727a5e9623f.html
临时表的应用

http://school.cnd8.com/sql-server/jiaocheng/47766.htm
临时表优缺点讨论

http://www.soft6.com/tech/16/162145.html
二、使用临时表的好处。

　　在T-SQL语言中使用临时表的好处是很显而易见的。笔者下面就举一些常见的好处。

　　如利用临时表来组织数据，比普通表会更加的简洁、紧凑。这主要是在临时表中可以实现很多的特性。如可以进行预处理计算。如当发现基本标中的索引不怎么合适，也可以在数据库临时表中重新创建索引以优化原有的索引。特别是当需要多次访问某个表或者视图的时候，利用临时表来组织数据是一个提高效率的好方法。即使只是一个简单的查询，其效率的提升也是很明显的。为此，使用临时表最明显的一个好处就似乎可以提高数据库的性能，特别是查询的性能。

　　另外使用临时表还可以减少中间表的产生。在进行某些操作时，本来往往需要一些中间表的帮助才可以完成。而现在数据库管理员可以让数据库在需要时自动生成中间表，并在用完后进行自动删除。如此的话，中间表的建立与删除就不需要数据库管理员人为的管理了。所以，使用临时表可以减少数据库系统中的垃圾表，也可以降低用户的工作量。为此笔者认为，临时表是SQL Server数据库中一个很使用的工具。作为数据库管理员，要在平时的工作中，合理使用这个临时表，发挥其最大的效用。虽然针对特定的任务该采用什么类型的临时表，有很多容易混淆的地方。但是数据库管理员不能够因噎废食，而应该积极的去尝试。

[dawugui]

临时表
可以创建本地和全局临时表。本地临时表仅在当前会话中可见；全局临时表在所有会话中都可见。

本地临时表的名称前面有一个编号符 (#table_name)，而全局临时表的名称前面有两个编号符 (##table_name)。

SQL 语句使用 CREATE TABLE 语句中为 table_name 指定的名称引用临时表：

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)

如果本地临时表由存储过程创建或由多个用户同时执行的应用程序创建，则 SQL Server 必须能够区分由不同用户创建的表。为此，SQL Server 在内部为每个本地临时表的表名追加一个数字后缀。存储在 tempdb 数据库的 sysobjects 表中的临时表，其全名由 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。为了允许追加后缀，为本地临时表指定的表名 table_name 不能超过 116 个字符。

除非使用 DROP TABLE 语句显式除去临时表，否则临时表将在退出其作用域时由系统自动除去： 

当存储过程完成时，将自动除去在存储过程中创建的本地临时表。由创建表的存储过程执行的所有嵌套存储过程都可以引用此表。但调用创建此表的存储过程的进程无法引用此表。


所有其它本地临时表在当前会话结束时自动除去。


全局临时表在创建此表的会话结束且其它任务停止对其引用时自动除去。任务与表之间的关联只在单个 Transact-SQL 语句的生存周期内保持。换言之，当创建全局临时表的会话结束时，最后一条引用此表的 Transact-SQL 语句完成后，将自动除去此表。 
在存储过程或触发器中创建的本地临时表与在调用存储过程或触发器之前创建的同名临时表不同。如果查询引用临时表，而同时有两个同名的临时表，则不定义针对哪个表解析该查询。嵌套存储过程同样可以创建与调用它的存储过程所创建的临时表同名的临时表。嵌套存储过程中对表名的所有引用都被解释为是针对该嵌套过程所创建的表，例如：

CREATE PROCEDURE Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t
GO
CREATE PROCEDURE Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (99)
GO
EXEC Test1
GO

下面是结果集：

(1 row(s) affected)

Test1Col    
----------- 
1           

(1 row(s) affected)

Test2Col    
----------- 
2           

当创建本地或全局临时表时，CREATE TABLE 语法支持除 FOREIGN KEY 约束以外的其它所有约束定义。如果在临时表中指定 FOREIGN KEY 约束，该语句将返回警告信息，指出此约束已被忽略，表仍会创建，但不具有 FOREIGN KEY 约束。在 FOREIGN KEY 约束中不能引用临时表。

考虑使用表变量而不使用临时表。当需要在临时表上显式地创建索引时，或多个存储过程或函数需要使用表值时，临时表很有用。通常，表变量提供更有效的查询处理。

[cutesun]

个人理解
1.创建和添加临时表，貌似有很大的数据库日志操作，所以引起很多没有比较的数据库IO,显然会造成速度缓慢
2.创建完临时表，你还会再上面添加索引么？所以导致INNER Join之类的查询很缓慢。
3.SQLServer已经有很多解决方案了，很多情况没必要临时表，比如子查询 表表达式等等

[飘零一叶]

[Quote=引用 3 楼 cutesun 的回复:]
个人理解
1.创建和添加临时表，貌似有很大的数据库日志操作，所以引起很多没有比较的数据库IO,显然会造成速度缓慢
2.创建完临时表，你还会再上面添加索引么？所以导致INNER Join之类的查询很缓慢。
3.SQLServer已经有很多解决方案了，很多情况没必要临时表，比如子查询 表表达式等等
[/Quote]
临时表主要会增加tempdb的IO，导致性能下降。

[wing7742]

[Quote=引用 4 楼 dlut_liuq 的回复:]
引用 3 楼 cutesun 的回复:
个人理解
1.创建和添加临时表，貌似有很大的数据库日志操作，所以引起很多没有比较的数据库IO,显然会造成速度缓慢
2.创建完临时表，你还会再上面添加索引么？所以导致INNER Join之类的查询很缓慢。
3.SQLServer已经有很多解决方案了，很多情况没必要临时表，比如子查询 表表达式等等

临时表主要会增加tempdb的IO，导致性能下降。……
[/Quote]
了解了
看来下次临时表要酌情考虑了

[haitao]

[Quote=引用 4 楼 dlut_liuq 的回复:]
引用 3 楼 cutesun 的回复:
个人理解
1.创建和添加临时表，貌似有很大的数据库日志操作，所以引起很多没有比较的数据库IO,显然会造成速度缓慢
2.创建完临时表，你还会再上面添加索引么？所以导致INNER Join之类的查询很缓慢。
3.SQLServer已经有很多解决方案了，很多情况没必要临时表，比如子查询 表表达式等等

临时表主要会增加tempdb的IO，导致性能下降。……
[/Quote]

所以希望能把tempdb放到ramdisk。。。。。。。

另外，使用表变量应该比临时表要高效，但是局限也比较多：只能有一个主键。如果记录多，效率反而比临时表低

[bancxc]

临时表也会提高性能

[qiqu114]

当数据库达到成百万级别时,创建临时表就不行了
我试验过
我用的是sql2000
数据库数据10万条
用存储过程创建临时表来进行翻页操作
最后显示的数据没有10万,有时只有3万,有时只有4万

说明存储过程创建临时表没有完全创建好

小数据用临时表,对查询和翻页都很好的

[bote_china]

临时表是有它的作用的,但不能滥用,当一个查询涉及很多表时,查询的笛卡尔积是非常大的,单纯的用索引可能解决不了查询时间的问题.这个时候可以用临时表,将一个查询分成多步完成,减少输出查询结果时的笛卡尔积,会对提高查询效率有很大的帮助.比如
查询的主表有上百W条数据,条件都是针对主表字段,然后1:M:M关联两个子表,如果直接关联查询,那么笛卡尔积可能为100W*M*100W*n*100W,可能就会造成耗时很长或查询超时.用临时表先筛选出主表的数据,比如筛选取主表后还有1000条数据满足条件要求,这时再用临时表来关联两个子表,笛卡尔积就会下降一个几何积,会明显提高查询速度.
什么时候用临时表,取决于查询的复杂成度和数据量,简单的查询用临时表就得不偿失了.