表格与临时表的性能比较

Question

表格与临时表的性能比较

sqlsql-servert-sqlsql-server-2008

10

对于数百万条记录来说，哪种方式更快：永久表格还是临时表格？

我需要处理仅包含1500万条记录的数据。处理完成后，我们会删除这些记录。

- ManishKumar1980

2

这强烈取决于情况。你想用它做什么？ - Lukasz Lysik

永久表。您连接到服务器，百万条记录已经存在，无需任何操作，子纳秒时间！...也许您想详细说明一下您的问题？ - Remus Rusanu

我需要处理5000万条记录。为此，我需要创建永久/临时表。情景是：为了处理5000万条记录，我创建另一个？/？表，并将其插入到该表中。然后，我按（Fname）优先级插入到另一个永久/临时表中，并从第一个表中删除。然后应用优先级2，再次执行第一步。所以我问了这个问题，请回复。 - ManishKumar1980

7个回答

14

如果您不使用tempdb，请确保您正在使用的数据库的恢复模式未设置为“完整”。否则，这将对插入50M行造成很大的开销。

理想情况下，您应该使用一个分阶段的数据库，简单的恢复模式，在可能的情况下采用RAID 10，并且提前为所有操作提供足够的空间。关闭自动增长功能。

使用INSERT ... WITH (TABLOCK)来避免逐行记录：

INSERT INTO StagingTable WITH (TABLOCK) (.....)
SELECT .....

同样适用于BULK INSERT。如果您删除并重新创建，请在插入之前创建聚集索引，如果不能，则首先将其插入到一个表中，然后再从该表中插入到具有正确聚集的另一个表中，并截断第一个表。如果可能，请避免在BULK INSERT上使用小批量大小。仔细阅读BULK INSERT文档，因为错误的选项会影响性能。

避免使用INSERT ... EXEC。每一行都会被记录。

除非需要计算运行总数，否则请避免使用UPDATE。通常，从一个表插入到另一个表，然后截断第一个表的成本要低于原地更新。运行总数计算是例外，因为它们可以使用UPDATE和变量在行之间累积值来完成。

除控制结构外，不要使用表变量，因为它们会阻止并行化。不要将您的5000万行表连接到表变量，而应改用临时表。

不要害怕使用游标进行迭代。使用游标变量，并针对聚集索引前面的低基数列使用STATIC关键字进行声明。使用此方法将大表分成更易管理的块。

不要尝试在任何一个语句中做太多事情。

- Peter Radocchia

非常好的和令人满意的答案。谢谢你们所有人。 - ManishKumar1980

2

如果表结构完全相同，永久表更快，因为没有分配空间和建立表的开销。

在某些情况下，临时表更快（例如，在您不需要永久表上存在的索引会减慢插入/更新的情况下）。

- DVK

1

这要看情况。

临时表存储在tempdb数据库中，该数据库可能与您的实际数据库不在同一驱动器上。因此，很多事情取决于a)这些驱动器的速度和b)哪些数据库/文件在同一驱动器上。
（例如，如果数据库文件和日志文件位于不同的物理驱动器上，则实际数据库将更快）

如果您使用像数据库镜像这样的可用性解决方案，临时表可能更快：
在工作中，我们使用同步数据库镜像，这意味着如果我们写入数据库，数据会立即写入镜像服务器，并且主服务器会等待镜像的确认后才返回给调用者！。

因此，如果您向表中插入1500万条记录，对它们进行处理（可能涉及对所有记录的一些大更新），然后将其删除，SQL Server必须立即通过网络将所有这些更改传播到镜像服务器。

另一方面，在临时表中执行此操作将保留在服务器上的本地 tempdb 数据库中。

- Christian Specht

0

永久表在大多数情况下比临时表更快。

请查看：http://www.sql-server-performance.com/articles/per/derived_temp_tables_p1.aspx

- anishMarokey

0

个人而言，我会使用永久表格并在每次使用前进行截断。根据我的经验，这样更易于理解和维护。但是，我最好的建议是尝试两种方法，看哪一种表现更好。

- Mayo

2

只有当进程是单例且没有其他进程在此期间启动并需要使用该表时，才能起作用。我们有导入大量数据的进程，并且我们无法截断单个表，因为可能会同时运行多个进程。 - Aaron Bertrand

您可以通过使用具有唯一列的永久表来解决这个问题，以识别处理特定数据集的导入过程。我们针对用户驱动的基于文件的导入操作使用这些表（而不是夜间批处理，其中截断操作效果良好）。可能需要考虑一个清理过程来控制表格大小。 - Mayo

-1

临时表通常在内存中（除非它们太大），因此理论上它们应该非常快。但实际上通常并不是这样。一般来说，除非没有其他解决方案，否则尽量避免使用临时表。您能否提供更多关于您正在尝试做什么的信息？可能可以使用派生查询完成

- adamJLev

8

临时变量存储在内存中，而不是临时表中。 - ManishKumar1980

2

我没有看到这个问题是针对MSSQL的。在MySQL中，您可以声明一个临时内存表：CREATE TEMPORARY TABLE test ENGINE=MEMORY。 - adamJLev

1

表变量显然也存储在tempdb中 - 请参见http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386 - flash

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- HLGEM · Accepted Answer

在您的情况下，我们使用一个名为“暂存表”的永久性表。这是大量导入的常用方法。实际上，我们通常使用两个暂存表，一个包含原始数据，另一个包含清理后的数据，这使得研究有关源数据问题变得更加容易（它们几乎总是由于我们的客户以新的和不同的方式向我们发送垃圾数据所致，但我们必须能够证明这一点）。此外，您还可以避免问题，例如必须增加temp db或者导致其他想要使用temp db但需要等待它增长的用户的问题等。

您还可以使用SSIS并跳过暂存表，但我发现，如果不必重新加载5000万行的表就可以回溯和研究是非常有帮助的。