但有时候,如果表变量中记录的数量太多,无法保存在内存中,就会像临时表一样被放在磁盘上。
但我不知道什么是"太多的记录"是多少,是十万条还是一百万条?我怎么知道我正在使用的表变量是在内存中还是在磁盘上?在SQL Server 2005中是否有函数或工具可以测量表变量的规模或让我知道何时从内存中将表变量放在磁盘上?
#temp
表(表值参数允许只读访问)。如果你需要在一个表变量上创建无法创建的索引,那么你当然需要使用一个#temporary
表。但是这个细节在不同的版本中可能会有所不同。对于SQL Server 2012及以下版本,只能通过UNIQUE
或PRIMARY KEY
约束隐式创建索引。SQL Server 2014引入了内联索引语法,支持CREATE INDEX
的一部分选项。此后,也允许使用过滤条件来创建索引。但是,在表变量上创建包含INCLUDE
列或列存储索引仍然不可能。
如果你需要反复添加和删除大量行,则使用#temporary
表。该表支持TRUNCATE
(对于大型表而言比DELETE
更有效率),并且在TRUNCATE
之后进行的插入操作可能比在DELETE
之后进行的插入操作性能更好如下所示。
#temporary
表。该表支持创建统计信息,可以根据数据动态重新编译计划(但对于存储过程中缓存的临时表需要单独了解重新编译行为)。SELECT
语句,则应考虑使用表变量,以阻止此使用并行计划的可能性。#temp
表时,锁定时间可能比表变量长(取决于锁的类型和隔离级别,可能会一直持有锁,直到事务结束而不是语句结束),并且它还可以防止tempdb
事务日志被截断,直到用户事务结束。因此,这可能更适合使用表变量。#temporary
表少。Bob Ward在他的tempdb
演示中指出,在高并发条件下,这可能会导致系统表上的额外争用。此外,在处理小量数据时,这可能会对性能造成可测量的影响。行集共享的影响
DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT);
CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);
INSERT INTO @T
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0
FROM master..spt_values v1, master..spt_values v2
SET STATISTICS TIME ON
/*CPU time = 7016 ms, elapsed time = 7860 ms.*/
UPDATE @T SET Flag=1;
/*CPU time = 6234 ms, elapsed time = 7236 ms.*/
DELETE FROM @T
/* CPU time = 828 ms, elapsed time = 1120 ms.*/
UPDATE #T SET Flag=1;
/*CPU time = 672 ms, elapsed time = 980 ms.*/
DELETE FROM #T
DROP TABLE #T
id
的表变量,并使用OPTION (RECOMPILE)
可能对此很好,但请测试两者。 - Martin Smith如果数据量很小(数千字节),请使用表变量
如果数据量很大,请使用临时表
另一种思考方式:如果您认为可以从索引、自动统计信息或任何SQL优化器好处中受益,那么您的数据集可能太大了,无法使用表变量。
例如,我只想将约20行数据放入一个格式中,并对它们作为一组进行修改,然后再使用它们更新/插入永久表,这时使用表变量是完美的。
但是,如果我要一次性填充成千上万行数据,我可以肯定地说,临时表比表变量表现更好。
这与CTE的大小问题类似 - 如果CTE中的数据非常小,则我发现CTE的性能与优化器提供的结果一样好甚至更好,但如果数据很大,则会对性能造成很大的影响。
我的理解主要基于http://www.developerfusion.com/article/84397/table-variables-v-temporary-tables-in-sql-server/,其中有更多详细信息。
Microsoft 在这里表示:
表变量没有分发统计数据,它们不会触发重新编译。因此,在许多情况下,优化器将基于表变量没有行的假设构建查询计划。因此,如果您预计有很多行(大于100),则应该谨慎使用表变量。在这种情况下,临时表可能是更好的解决方案。
我完全同意Abacus的观点(抱歉-我没有足够的积分来评论)。
此外,请记住,问题不一定在于您有多少条记录,而是您的记录的大小。
例如,您是否考虑过每个拥有50列的1,000条记录与每个只有5列的100,000条记录之间的性能差异?
最后,也许您正在查询/存储比您需要的数据更多的数据?这里有一个关于SQL优化策略的好读物。限制您提取的数据量,特别是如果您没有使用所有数据(一些SQL程序员会变得懒惰,即使他们仅使用了很小的子集,也会选择全部选择)。不要忘记SQL查询分析器也可能成为您最好的朋友。
变量表 仅对当前会话可用,例如,如果您需要在当前存储过程中执行另一个存储过程,则必须将表作为 Table Valued Parameter
传递,并且这将影响性能,使用 临时表 您只需传递临时表名称即可完成此操作。
测试临时表:
测试变量表:
我经历过的另一件事是:如果您的模式没有创建表的 GRANT
权限,则使用变量表。
在声明了declare @tb
表并与其他表连接后,我发现相比于临时表tempdb .. # tb
,写入数据的响应时间要高得多。
当我使用@tb进行连接时,返回结果的时间要长得多,不像#tm,几乎是瞬间返回。
我进行了一次涉及1万行连接和5个其他表连接的测试。
tempDB
中,所谓的“内存中”的说法是错误的。此外,查询优化器始终认为表变量仅包含一行数据,如果实际数据远多于一行,可能会导致严重的执行计划问题。 - marc_stempdb
中(但也可能完全在内存中)。 - Martin Smith