我在表变量中学习更多细节。它说临时表总是在磁盘上,而表变量在内存中,也就是说,表变量的性能优于临时表,因为表变量使用的IO操作比临时表少。
但有时,如果一个表变量中有太多的记录无法包含在内存中,那么该表变量会像临时表一样放在磁盘上。
但我不知道“太多记录”是什么。 100,000 条记录?还是 1000,000 条记录?我如何知道我正在使用的表变量是在内存中还是在磁盘上? SQL Server 2005 中是否有任何函数或工具来测量表变量的规模或让我知道表变量何时从内存放入磁盘?
tempDB
中 - “在内存中”是一个神话。另外:查询优化器将始终认为表变量只保留一行 - 如果您有更多,这可能会导致严重错误的执行计划。
tempdb
中(但也可能完全在内存中)
您的问题表明您已经屈服于围绕表变量和临时表的一些常见误解。
我写了 quite an extensive answer on the DBA site 来查看两种对象类型之间的差异。这也解决了您关于磁盘与内存的问题(我没有看到两者之间的行为有任何显着差异)。
关于标题中关于何时使用表变量和本地临时表的问题,您并不总是有选择的余地。例如,在函数中,只能使用表变量,如果您需要在子范围内写入表,则只有 #temp
表可以执行(表值参数允许 readonly access)。
在您可以选择的地方,以下是一些建议(尽管最可靠的方法是使用您的特定工作负载简单地测试两者)。
如果您需要一个无法在表变量上创建的索引,那么您当然需要一个#temporary 表。然而,这方面的细节取决于版本。对于 SQL Server 2012 及以下版本,可以在表变量上创建的唯一索引是通过 UNIQUE 或 PRIMARY KEY 约束隐式创建的索引。 SQL Server 2014 为 CREATE INDEX 中可用选项的子集引入了内联索引语法。这已被扩展,以允许过滤索引条件。但是,仍然无法在表变量上创建具有 INCLUDE-d 列或列存储索引的索引。如果您将重复从表中添加和删除大量行,请使用#temporary 表。它支持 TRUNCATE(对于大型表来说比 DELETE 更有效),另外,TRUNCATE 之后的后续插入可以比 DELETE 之后的插入具有更好的性能,如此处所示。如果您要删除或更新大量行,那么临时表的性能可能比表变量好得多 - 如果它能够使用行集共享(请参阅下面的“行集共享的影响”以获取示例)。如果使用该表的最佳计划会因数据而异,则使用#temporary 表。这支持创建允许根据数据动态重新编译计划的统计信息(尽管对于存储过程中的缓存临时表,需要单独理解重新编译行为)。如果使用表的查询的最佳计划不太可能改变,那么您可以考虑使用表变量来跳过统计信息创建和重新编译的开销(可能需要提示来修复您想要的计划)。如果插入到表中的数据源来自一个可能很昂贵的 SELECT 语句,那么考虑使用表变量将阻止使用并行计划的可能性。如果您需要表中的数据在外部用户事务的回滚中幸存下来,请使用表变量。一个可能的用例可能是在长 SQL 批处理中记录不同步骤的进度。在用户事务中使用#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
如果数据量非常小(数千字节),请使用表变量
对大量数据使用临时表
另一种思考方式:如果您认为您可能会受益于索引、自动统计或任何 SQL 优化器的优点,那么您的数据集可能对于表变量来说太大了。
在我的示例中,我只想将大约 20 行放入一个格式并将它们作为一个组进行修改,然后再使用它们来更新/插入永久表。所以一个表变量是完美的。
但我也在运行 SQL 以一次回填数千行,我可以肯定地说临时表的性能比表变量好得多。
这与 CTE 因类似大小原因而受到关注的方式没有什么不同 - 如果 CTE 中的数据非常小,我发现 CTE 的性能与优化器提出的一样好或更好,但如果它非常大,那么它伤害了你。
我的理解主要基于http://www.developerfusion.com/article/84397/table-variables-v-temporary-tables-in-sql-server/,它有更多的细节。
表变量没有分布统计,它们不会触发重新编译。因此,在很多情况下,优化器会在假设表变量没有行的情况下构建查询计划。出于这个原因,如果您期望有更多的行数(大于 100),您应该谨慎使用表变量。在这种情况下,临时表可能是更好的解决方案。
我完全同意 Abacus(抱歉 - 没有足够的评论点数)。
另外,请记住,它不一定取决于您拥有多少条记录,而是取决于您的记录的大小。
例如,您是否考虑过每条 50 列的 1,000 条记录与每条只有 5 列的 100,000 条记录之间的性能差异?
最后,也许您查询/存储的数据超出了您的需要?这是关于 SQL optimization strategies 的好读物。限制您提取的数据量,尤其是在您没有全部使用的情况下(一些 SQL 程序员确实变得懒惰并且只选择所有内容,即使他们只使用了一个很小的子集)。别忘了 SQL 查询分析器也可能成为你最好的朋友。
变量表仅对当前会话可用,例如,如果您需要 EXEC
当前会话中的另一个存储过程,则必须将表作为 Table Valued Parameter
传递,当然这将影响性能,使用 临时表,您只需传递临时表名称即可
要测试临时表:
打开管理工作室查询编辑器
创建临时表
打开另一个查询编辑器窗口
从此表中选择“可用”
要测试变量表:
打开管理工作室查询编辑器
创建变量表
打开另一个查询编辑器窗口
从此表中选择“不可用”
我经历过的其他事情是:如果您的架构没有创建表的 GRANT
权限,则使用变量表。
在声明为 declare @tb
的表中写入数据并与其他表连接后,我意识到与临时表 tempdb .. # tb
相比,响应时间要长得多。
当我与@tb 一起加入他们时,返回结果的时间要长得多,与#tm 不同,返回几乎是瞬时的。
我用 10,000 行加入测试并加入了 5 个其他表
不定期副业成功案例分享
id
上具有主键并使用OPTION (RECOMPILE)
的表变量可能会很好 - 但同时测试两者。