临时表和内存表差别

最新推荐文章于 2021-04-25 15:34:08 发布

shaily

最新推荐文章于 2021-04-25 15:34:08 发布

阅读量1k

点赞数

分类专栏： SQL专辑文章标签： table insert 存储 sql server 测试 go

SQL专辑专栏收录该内容

64 篇文章 0 订阅

订阅专栏

你会看到这个提示，那是因为你的系统无法识别某栏目的模型信息，或者你新建模型后，没为这个模型设计单独的模板。不同模型的文档浏览页的模板为：article_模型名字标识.htm （更多的信息你可以在频道模型管理的地方查看）。

文章内容

Create table #tb1
{
myID int,
myText nvarchar(200)
}

和

Declare @tb1 table(myID int,myText nvarchar(200))

有什么差别？
网友回复:

SQL code


Code highlighting produced by Actipro CodeHighlighter (freeware)

http://www.CodeHighlighter.com/



区别一:

<PRE lang=sql>CREATE table #T (s varchar(128))

DECLARE @T table (s varchar(128))

INSERT into #T select 'old value #'

INSERT into @T select 'old value @'

BEGIN transaction

  UPDATE #T set s='new value #'

  UPDATE @T set s='new value @'

ROLLBACK transaction

SELECT * from #T

SELECT * from @T

s             

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

old value #



s                

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

new value @</PRE>这行代码一目了然,临时表能起到事务回滚的作用,
但是table变量不可以.作者解释是table变量不在事务作用范围之内.所以当table变量即使碰到回滚命令,
但是也不会真的执行回滚.



区别二:

任何含有临时表的存储过程是不能被预编译的.这在一个很长的存储过程中,优势会更加凸显.



区别三:

table变量作为变量只能在同范围内存在,不可能跨范围.还有就是table变量在内置的存储过程中
或者exec(string)语句中是不可见的还有就是不能被用于INSERT/EXEC statements.



下面的例子我来演示如何对比临时表和table变量在存储过程中预编译的区别.

创建一个测试表:

CREATE table NUM (n int primary key, s varchar(128))

GO

SET nocount on

DECLARE @n int

SET @n=1000000

WHILE @n>0 begin

  INSERT into NUM

    SELECT @n,'Value: ' convert(varchar,@n)

  SET @n=@n-1

  END

GO

然后创建存储过程:T1

CREATE procedure T1

  @total int

AS

  CREATE table #T (n int, s varchar(128))

  INSERT into #T select n,s from NUM

    WHERE n0>0 and n<=@total

  DECLARE @res varchar(128)

  SELECT @res=max(s) from NUM

    WHERE n<=@total and

      NOT exists(select * from #T

      WHERE #T.n=NUM.n)

GO这个存储过程的参数@Total给定的越大,那么执行的时间越长.





为精确测量存储过程执行时间,我使用了以下代码:

DECLARE @t1 datetime, @n int

SET @t1=getdate()

SET @n=100 – (**)

WHILE @n>0 begin

  EXEC T1 1000 – (*)

  SET @n=@n-1 end

SELECT datediff(ms,@t1,getdate())

GO(*) 是存储过程参数.



现在我们给这个存储过程来第一次提速:个它加个主键

CREATE procedure T2

  @total int

AS

  CREATE table #T (n int primary key, s varchar(128))

  INSERT into #T select n,s from NUM

    WHERE n0>0 and n<=@total

  DECLARE @res varchar(128)

  SELECT @res=max(s) from NUM

    WHERE n<=@total and

      NOT exists(select * from #T

      WHERE #T.n=NUM.n)

GO在下面的对比中你可以发现已经大大的提速了.



然后再来一次提速:给它加个聚集索引

CREATE procedure T3

  @total int

AS

  CREATE table #T (n int, s varchar(128))

  INSERT into #T select n,s from NUM

    WHERE n0>0 and n<=@total

  CREATE clustered index Tind on #T (n)

  DECLARE @res varchar(128)

  SELECT @res=max(s) from NUM

    WHERE n<=@total and

      NOT exists(select * from #T

      WHERE #T.n=NUM.n)

GO

很惊奇速度已经提高了很多!



那好了,咱们再来测试一下table变量的速度吧

CREARE procedure V1

  @total int

AS

  DECLARE @V table (n int, s varchar(128))

  INSERT into @V select n,s from NUM

    WHERE n0>0 and n<=@total

  DECLARE @res varchar(128)

  SELECT @res=max(s) from NUM

    WHERE n<=@total and

      NOT exists(select * from @V V

      WHERE V.n=NUM.n)

GO

然后再来创建一个有主键的:

CREATE procedure V2

  @total int

AS

  DECLARE @V table (n int primary key, s varchar(128))

  INSERT into @V select n,s from NUM

    WHERE n0>0 and n<=@total

  DECLARE @res varchar(128)

  SELECT @res=max(s) from NUM

    WHERE n<=@total and

      NOT exists(select * from @V V

      WHEREre V.n=NUM.n)

GO<

然后咱们来看看我测试的结果吧!

Table 1, using SQL Server 2000, time in ms  

Records  T1 T2 T3 V1 V2 

10 0.7 1 13.5 0.6 0.8 

100 1.2 1.7 14.2 1.2 1.3 

1000 7.1 5.5 27 7 5.3 

10000 72 57 82 71 48 

100000 883 480 580 840 510 

1000000 45056 6090 15220 20240 12010 







But the real shock is when you try the same on SQL Server 2005: 



Table 2 

N  T1 T2 T3 V1 V2 

10 0.5 0.5 5.3 0.2 0.2 

100 2 1.2 6.4 61.8 2.5 

1000 9.3 8.5 13.5 168 140 

10000 67.4 79.2 71.3 17133 13910 

100000 700 794 659 Too long!

 Too long!

 

1000000 10556 8673 6440 Too long!

 Too long!

 



发现对比在某些情况下sql2000的速度要比2005的要快上很多!





结论:

没有通用的规则指导你什么时候用临时表什么时候用table变量.

将复杂逻辑的存储过程移植到sql2005的时候,要格外小心!他可能比2000的效率要慢上好几十倍的!

在你的实际测试中,请测试两个极端:销量数据和超大量数据.

网友回复:

SQL code


Code highlighting produced by Actipro CodeHighlighter (freeware)

http://www.CodeHighlighter.com/



--关于临时表和Table表变量的注释   

    

  注释   

  可将函数和变量声明为   table   类型。table   变量可用于函数、存储过程和批处理中。   

    

  尽可能使用表变量而不使用临时表。table   变量有以下优点：     

    

  table   变量的行为类似于局部变量，有明确定义的作用域。该作用域为声明该变量的函数、存储过程或批处理。     

  在其作用域内，table   变量可像常规表那样使用。该变量可应用于   SELECT、INSERT、UPDATE   和   


DELETE   语句中用到表或表的表达式的地方。但是，table   不能用在下列语句中：   

    

  INSERT   INTO   table_variable   EXEC   存储过程。   

    

  SELECT   select_list   INTO   table_variable   语句。   

    

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

    

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

    

    

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

  不支持在表变量之间进行赋值操作。另外，由于表变量作用域有限，并且不是持久数据库的一部分，