临时表比表变量更好!
#编程 #database #mssql

原始URL:https://siderite.dev/blog/temporary-tables-better-than-table-variables

今天,我与一位同事进行了非常有趣的讨论,他通过用temporary table替换table variable在Microsoft的SQL Server中优化了我的工作。这很烦人,因为我已经做了很多时间,以为我选择了最好的解决方案。毕竟,临时桌子的开销将被存储到磁盘上的tempdb中。使用表变量可能有什么问题?我相信这张表可以解释这一切:

A comparison between temporary table and table variable

首先,存储是相同的。如何?好吧,表变量从内存开始,但是如果它们超过限制,则将保存到tempdb!另一个有趣的位是索引。虽然您可以在表变量上创建主键,但您不能使用其他索引 - 不过,因为您几乎不需要非常复杂的变量表。但是,有一个并行性:没有用于表变量的!如您所见,这很重要。至少表变量不会引起重新编译。最后但并非最不重要的一点也许是最重要的区别:统计!您在表变量上没有统计信息。

让我们考虑一下我的方案:我正在执行存储过程并将所选值存储在表变量中。该SP具有过滤记录ID的单一理由,然后我必须提取这些记录 - 与许多其他表一起加入它们 - 并且可以返回200、800或几十万行。

使用表变量,这意味着:

  1. 当插入可能数十万行时,我将没有平行性(慢速!),它可能会将其保存到tempdb(slow!)
  2. 在加入其他表格时,没有统计数据,它将像对待一个简短的值一样对待,它可能不是,并循环通过它:Table Spool(slow!)
  3. 各种分析工具将显示相同甚至更少的物理读取和相同的SQL Server执行时间,但是CPU时间将大于执行时间(隐藏慢!)

在SQL Server 2019中,这种情况得到了很大改善,以至于在大多数情况下表变量和临时表显示相同的性能,但是以前的版本将显示更大程度。

那么临时表总是更好吗?否。表变量有几个优点:

  1. 他们在范围结束时自动清除
  2. 导致存储程序的重新编译较少
  3. 较少的锁定和资源,因为它们没有交易日志

对于许多简单的情况,例如您要生成一些少量数据,然后使用该数据,表是最好的。但是,一旦数据大小或方案复杂性增加,临时表就会变得更好。

一如既往,不要相信我,测试!在SQL中,所有内容“依赖”,您不能依靠诸如“ X总是更好”的固定规则,因此请介绍您的特定方案,并查看哪个解决方案更好。

希望它有帮助!