create table #temptable 临时表 和 declare @bianliang table ()表变量

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

先卖弄一些基础的知识:

表变量

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

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

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

临时表

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

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

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

表变量 vs. 临时表

 

结论

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

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

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

SQL中的临时表和表变量


我们经常使用临时表和表变量,那现在我们就对临时表和表变量进行一下讨论.

临时表

局部临时表

全局临时表

表变量

                              

临时表

临时表存储在TempDB数据库中,所有的使用此SQL Server 实例的用户都共享这个TempDB,因为我们应该确保用来存储TempDB数据库的硬盘有足够的空间,以使之能够自己的增长.最好能够存储在一个拥有独立硬盘控制器上.因为这样不存在和其它的硬盘I/O进行争用.

我们很多程序员认为临时表非常危险,因为临时表有可能被多个连接所共享.其实在SQL Server中存在两种临时表:局部临时表和全局临时表,局部临时表(Local temp table)以#前缀来标识,并且只能被创建它的连接所使用.全局临时表(Global temp table)以##前缀来进行标识,并且可以和其它连接所共享.

局部临时表

局部临时表不能够被其它连接所共享的原因其实是在SQL Server 2000中自动为局部临时表的表名后面加上了一个唯一字符来标识.如:

      CREATE TABLE [#DimCustomer_test]

      (

         [CustomerKey] [int]

         ,    [FirstName] [nvarchar](50)  

     ,[MiddleName] [nvarchar](50)  

     ,[LastName] [nvarchar](50)

         )

现在我们来查看一下TempDB中 sysobjects表,我们会发现我们新创建的临时表#DimCustomer_test已经被加上了后缀:

  USE TempDB

   GO

   SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’

the Result is:

name

#DimCustomer_test___________________________________________________________________________________________________000000000005

全局临时表

下面我们来看一下全局临时表:

      CREATE TABLE [##DimCustomer_test]

      (

         [CustomerKey] [int]

         ,       [FirstName] [nvarchar](50)  

     ,[MiddleName] [nvarchar](50)  

     ,[LastName] [nvarchar](50)

         )

现在我们来查看一下TempDB中 sysobjects表,我们会发现我们新创建的临时表##DimCustomer_test没有被加上了后缀:

  USE TempDB

   GO

   SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’

The Result are:

#DimCustomer_test___________________________________________________________________________________________________000000000005

##DimCustomer_test

--Drop test temp tables

                               DROP TABLE [##DimCustomer_test]

                               DROP TABLE [#DimCustomer_test]

可以看到我们刚才创建的全局临时表名字并没有被加上标识.

表变量

表变量和临时表针对我们使用人员来说并没有什么不同,但是在存储方面来说,他们是不同的,表变量存储在内存中.所以在性能上和临时表相比会更好些!

另一个不同的地方是在表连接中使用表变量时,要为此表变量指定别名.如:

  USE AdventureWorksDW

   GO

   DECLARE @DimCustomer_test TABLE

   (

      [CustomerKey] [int]

      ,       [FirstName] [nvarchar](50)  

,[MiddleName] [nvarchar](50)  

,[LastName] [nvarchar](50)

      )

   ---insert data to @DimCustomer_test

   INSERT @DimCustomer_test

   (

      [CustomerKey]  

      ,       [FirstName]  

,[MiddleName]  

,[LastName]

      )

   SELECT  

      [CustomerKey]  

      ,       [FirstName]  

,[MiddleName]  

,[LastName]

   FROM DimCustomer

   SELECT [@DimCustomer_test].CustomerKey,SUM(FactInternetSales.OrderQuantity)

FROM @DimCustomer_test   INNER JOIN FactInternetSales    ON

@DimCustomer_test.CustomerKey = FactInternetSales.CustomerKey

Group BY CustomerKey

Result:

Server: Msg 137, Level 15, State 2, Line 32

Must declare the variable ’@DimCustomer_test’.


如果我们对上面的查询进行更改,对查询使用别名(并且找开IO):

-----in the follow script,we used the table alias.

DECLARE @DimCustomer_test TABLE

(

     [CustomerKey] [int]

     ,       [FirstName] [nvarchar](50)  

,[MiddleName] [nvarchar](50)  

,[LastName] [nvarchar](50)

     )

INSERT @DimCustomer_test

(

     [CustomerKey]  

     ,       [FirstName]  

,[MiddleName]  

,[LastName]

     )

SELECT  

     [CustomerKey]  

     ,       [FirstName]  

,[MiddleName]  

,[LastName]

FROM DimCustomer

SELECT t.CustomerKey,f.OrderQuantity

FROM @DimCustomer_test t INNER JOIN FactInternetSales   f ON

t.CustomerKey = f.CustomerKey

where t.CustomerKey=13513

表变量在批处理结束时自动被系统删除,所以你不必要像使用临时表表一样显示的对它进行删除.


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

另外在今天帮同事Tuning SQL 脚本地时候,发现对于大数据量表的查询(10w-100W),用变量的方式比用select 的方式居然执行时间减少了100倍!!似懂非懂,但从来没有想到差别如此大,惊讶ing,记录一笔,研究一下

M1:

declare @tempID int

set @tempID =(select lots_id from qs_notes where id='CVT20080321')

select * from ls_Qs_notes where id = @tempID

---返回记录998,行执行时间6589

M2:

select * from ls_Qs_notes where id =(select lots_id from qs_notes where id='CVT20080321')

---返回记录998 ,行执行时间60


 

 

/**************************************************************************************************************

在开发过程中,经常会遇到使用表变量和本地临时表的情况。下面是对二者的一个介绍:

1. 为什么要使用表变量

表变量是从2000开始引入的,微软认为与本地临时表相比,表变量具有如下优点:
  a.与其他变量的定义一样,表变量具有良好的定义范围,并会被自动清除;
  b.在存储过程中使用表变量会减少存储过程重新编译的发生;
  c.表变量需要更少的锁请求和日志资源;
  d.可以在表变量上使用UDF,UDDT,XML。

2.表变量的限制

与临时表相比,表变量存在着如下缺点:
  a.在表变量上没有统计信息,查询优化器根据固定的预估值来选择执行计划,在数据很多的情况下,会导致查询优化器选择很差的执行计划;
  b.不能直接在表变量上创建索引,但可以通过创建约束(主键、唯一)来建立索引;
  c.在DECLARE后,不能再对表变量进行更改;
  d.不能对表变量执行INSERT
EXEC,SELECT INTO语句;
  e.不能通过EXEC或sp_executesql来执行牵涉到表变量的动态SQL语句,但如果表变量是在动态SQL语句内定义的,则可以。

3.那什么时候可以使用表变量

要使用表变量应该根据如下规则来判断:
  a.表的行数;
  b.使用表变量能够减少的重新编译次数;
  c.查询的类型和对索引或者统计信息的依赖程度;
  d.需要生用UDF,UDDT,XML的时候。
其实也就说,得从实际出发,根据具体的查询,作出具体的选择。但是,其中很关键的一点,如果表的行数非常多,使用表变量其实是更费资源的。有人提出了这样的建议:对于行数较少的情况下(小于1000行)可以使用表变量;如果行数很多(有几万行),则使用临时表。

因此,在实际的开发中,应通过分别使用临时表或表变量进行对比后,才作出决定。


下面是一个例子,插入临时表和表变量的数据有20多万行,可以看到,使用临时表的时间是使用表变量所花时间的1
/5

'SalesOrderHeader'。扫描计数3,逻辑读取130 次,物理读取9 次,预读43 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。
'#SalesOrderDetail___________________________________________________________________________________________________00000000001F'。扫描计数3,逻辑读取12331 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。
'Worktable'。扫描计数0,逻辑读取0 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。

SQL Server 执行时间:
   CPU 时间
=2281 毫秒,占用时间= 19726 毫秒。
select with temporarytable: 20140 ms

********************************************************************************

'SalesOrderHeader'。扫描计数0,逻辑读取764850 次,物理读取17 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。
'#4E88ABD4'。扫描计数1,逻辑读取12331 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。

SQL Server 执行时间:
   CPU 时间
=4375 毫秒,占用时间= 107160 毫秒。
select with table variable:107160 ms

4.使用表变量的误区

对于表变量,很多人认为,表变量和其他变量一样,只存在内存中,其实这是不正确的,表变量也存在tempdb中。可以通过下面例子进行对比。

CREATE TABLE #TempTable (TT_Col1INT)

DECLARE @TableVariable TABLE (TV_Col1 INT)

SELECT TOP 2*

FROM tempdb.sys.objects

ORDER BY create_date DESC


name
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#03317E3D
#TempTable__________________________________________________________________________________________________________000000000003


#03317E3D就是刚创建的表变量;

5.其他

  表变量不受rollback影响,某些情况下会破坏数据的完整性。

CREATE TABLE #TempTable (TT_Col1INT)
DECLARE @TableVariable TABLE (TV_Col1 INT)
INSERT #TempTableVALUES (1)
INSERT @TableVariable VALUES (1)
BEGIN TRANSACTION
    
INSERT #TempTableVALUES (2)
     
INSERT@TableVariableVALUES (2)
ROLLBACK
SELECT * FROM #TempTable

/*
TT_Col1
-------
1
*/

SELECT * FROM@TableVariable
--返回了两条记录
/*

TV_Col1
-------
1
2
*/

http://www.cnblogs.com/jokey/archive/2010/06/19/1760994.html

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值