ITKeyword,专注技术干货聚合推荐

注册 | 登录

sql server 存储过程中使用变量表,临时表的分析(续)

分享于

2021腾讯云限时秒杀,爆款1核2G云服务器298元/3年!(领取2860元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1062

2021阿里云最低价产品入口+领取代金券(老用户3折起),
入口地址https://www.aliyun.com/minisite/goods

推荐:ORACLE 在存储过程中使用临时表

  一 临时表语法       临时表只在Oracle 8i 以及以上产品中支持。       语法: [java] view plain copy print create global temporary table 临时表名 on c

      最近,我有一朋友,对我说他的数据库中的很多存储过程,执行都是超时.让我替他看看是什么原因.我一看,原来他的存储过程中用了很多的临时表与变量表.于是我跟他说过犹不及.

在存储过程中使用临时表或变量表,使用的好可以提高速度,使用的不好,可能会起到反作用. 然后给了他几个示例让他自己去看,然后针对自己的数据库进行修改.

那么表变量一定是在内存中的吗?不一定.

通常情况下,表变量中的数据比较少的时候,表变量是存在于内存中的。但当表变量保留的数据较多时,内存中容纳不下,那么它必须在磁盘上有一个位置来存储数据。与临时表类似,表变量是在 tempdb 数据库中创建的。如果有足够的内存,则表变量和临时表都在内存(数据缓存)中创建和处理。

 说明:

     1) CPU-- 事件(sql语句)使用的 CPU 时间(毫秒)。

     2)  Reads--由服务器代表事件读取逻辑磁盘的次数。这些读取操作数包含在语句执行期间读取表和缓冲区的次数。

     3) Writes--由服务器代表事件写入物理磁盘的次数。

 

 

示例1.变量表

1) 10000条记录

declare @t table
(
id nvarchar(50),
supno nvarchar(50),
eta datetime
)
insert  @t

select top 10000 ID,supno,eta from 表

 

--cpu :125    reads :13868    writes: 147

--表 '#286302EC'。扫描计数 0,逻辑读取 10129 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

--表 '表'。扫描计数 1,逻辑读取 955 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

declare @t table
(
id nvarchar(50),
supno nvarchar(50),
eta datetime
)
insert  @t

select top 1000 ID,supno,eta from 表

 

--    cpu:46    reads:2101     writes:    17   
--表 '#44FF419A'。扫描计数 0,逻辑读取 1012 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
--表 '表'。扫描计数 1,逻辑读取 108 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。



--示例2。临时表:

 

create table #t
(
id nvarchar(50),
supno nvarchar(50),
eta datetime
)
end
insert #t
select top 10000 ID,supno,eta
from 表

--cpu :125    reads:13883       writes:148    
--表 '#t00000000005'。扫描计数 0,逻辑读取 10129 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
--表 '表'。扫描计数 1,逻辑读取 955 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。


create table #t
(
id nvarchar(50),
supno nvarchar(50),
eta datetime
)

insert #t
select top 1000 ID,supno,eta
from 表

--cpu: 62    reads: 2095        writes: 17

--表 '#t00000000003'。扫描计数 0,逻辑读取 1012 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
--表 '表'。扫描计数 1,逻辑读取 108 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。


 


--示例3。不创建临时表,直接插入到临时表

select top 10000 ID,supno,eta
into #t
from 表

--cpu:31    reads:1947        writes:83

--表 '表'。扫描计数 1,逻辑读取 955 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。




select top 1000 ID,supno,eta
into #t
from 表

--cpu: 0    reads: 997        writes:11

--表 '表'。扫描计数 1,逻辑读取 108 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

从以上的分析中可以看出,如果使用3)方式,则会少建一个临时表.那么IO中的读写也将减少次数.

1)与2)都会有先建临时表的动作,并进行相应的IO读取操作.

从sql语句对服务器的cpu使用上来看,第三种情况cpu使用率也相对较低.

从物理写入磁盘操作来看,第三种情况的物理写入次数较少.

 

在什么情况下使用表变量来代替临时表:

取决于以下三个因素:

• 插入到表中的行数。本人认为最好是小于1000行,具体情况具体分析. • 从中保存查询的重新编译的次数。 • 查询类型及其对性能的指数和统计信息的依赖性。 在某些情况下,可将一个具有临时表的存储过程拆分为多个较小的存储过程,以便在较小的单元上进行重新编译。

个人建议,当记录行小于1000行的情况下,应尽量使用表变量,除非数据量非常大(大于1000行)并且需要重复使用表。在这种情况下,可以在临时表上创建索引以提高查询性能。但是,各种方案可能互不相同。

Microsoft 建议您做一个测试,来验证表变量对于特定的查询或存储过程是否比临时表更有效。

 

 

 

推荐:Oracle存储过程中使用临时表

一、Oracle临时表知识    在Oracle中,临时表分为SESSION、TRANSACTION两种,SESSION级的临时表数据在整个SESSION都存在,直到结束此次SESSION;而 TRANSACTIO

      最近,我有一朋友,对我说他的数据库中的很多存储过程,执行都是超时.让我替他看看是什么原因.我一看,原来他的存储过程中用了很多的临时表与变量表.于是我跟他说过犹不及. 在存储过程中使用

相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。