代码之家 › 专栏 › 技术社区 › gingerbreadboy

临时表性能异常

sql-server sql

gingerbreadboy · 技术社区 · 16 年前

慷慨开放: 好吧,伙计们,老板需要答案,我需要加薪。这似乎不是一个冷缓存问题。

更新:

我听从了下面的建议,但没有用。客户机统计数据如何产生一组有趣的数字。

#温度 VS @温度

插入、删除和更新语句数 0比1

受INSERT、DELETE或UPDATE语句影响的行 0比7647

select语句数 0比0

select语句返回的行 0比0

交易记录数 0比1

最有趣的是受影响的行数和事务数。为了提醒您,下面的查询将返回相同的结果集,只返回到不同样式的表中。

下面的查询是basically执行相同操作。它们都选择一组结果(大约7000个),并将其填充到一个temp或var表中。在我的头脑中,变量表 @温度 应该比临时表更快地创建和填充 γ温度 但是,第一个示例中的var表需要1分钟15秒才能执行,而第二个示例中的temp表需要16秒。

有人能解释一下吗?

declare @temp table ( 
id uniqueidentifier, 
brand nvarchar(255), 
field nvarchar(255),
date datetime, 
lang nvarchar(5), 
dtype varchar(50)
)
insert into @temp (id, brand, field, date, lang, dtype )
select id, brand, field, date, lang, dtype
from view 
where brand = 'myBrand' 
-- takes 1:15

select id, brand, field, date, lang, dtype
into #temp
from view 
where brand = 'myBrand'

DROP TABLE #temp
-- takes 16 seconds

6 回复 | 直到 14 年前

Brian 14 年前

我相信这几乎完全取决于表变量和临时表的性能。

表变量针对只有一行进行了优化。当查询优化器选择一个执行计划时,它是在假定表变量只有一行(通常为false)的情况下执行的。

我找不到一个好的来源,但至少在这里提到:

http://technet.microsoft.com/en-us/magazine/2007.11.sqlquery.aspx

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Remus Rusanu 16 年前

同时运行set statistics io on和set statistics time on。每次运行6-7次,丢弃两种情况下的最佳和最差结果,然后比较两次平均值。

我怀疑区别主要来自冷缓存(第一次执行)和热缓存(第二次执行)。统计IO的输出会给出这样的情况,因为运行之间的物理读取存在很大的差异。

并确保您有测试的“实验室”条件:没有其他正在运行的任务(没有锁争用)、数据库(包括tempdb)和日志预增长到所需的大小,这样您就不会遇到任何日志增长或数据库增长事件。

Gabriel McAdams 16 年前

这并不少见。表变量可以比临时表慢(在很多情况下也是如此)。原因如下:

SQL Server维护使用临时表的查询的统计信息,但不维护使用表变量的查询的统计信息。如果没有统计信息,SQL Server可能会为包含表变量的查询选择较差的处理计划。
除为主约束或唯一约束创建的系统索引外,不能在表变量上创建非聚集索引。与具有非聚集索引的临时表相比,这可能会影响查询性能。
表变量使用内部元数据的方式可以防止引擎在并行查询中使用表变量(这意味着它不会利用多处理器机器)。
SQL Server为一行优化了一个表变量(假定返回一行)。

Mitch Wheat 16 年前

我不是100%认为这是原因,但是表var将没有任何统计信息,而临时表将有。

womp 16 年前

select into是一个未记录的操作,这可能解释了大部分性能差异。insert为每个操作创建一个日志条目。

此外,select into正在创建表作为操作的一部分,因此SQL Server自动知道它没有约束,这可能会影响到它。

Aaronaught 16 年前

如果将7000条记录插入临时表(持久性或变量)需要一整分钟的时间,那么性能问题几乎肯定出现在填充它的select语句中。

你跑了吗? DBCC FREEPROCCACHE 和 DBCC DROPCLEANBUFFERS 在分析之前?我在想,也许它在为第二个查询使用一些缓存的结果。