SQL Server 2000临时表vs表变量

时间：2022-12-09 07:09:25

What would be more efficient in storing some temp data (50k rows in one and 50k in another) to perform come calculation. I'll be doing this process once, nightly.

存储一些临时数据(一个50k行,另一个50k)执行计算会更有效。我每晚都要做一次这个过程。

How do you check the efficiency when comparing something like this?

在比较这样的事情时,你如何检查效率?

2 个解决方案

#1

The results will vary on which will be easier to store the data, in disk (#temp) or in memory (@temp).

结果将更容易在磁盘(#temp)或内存(@temp)中存储数据。

A few excerpts from the references below

以下参考文献的一些摘录

  • A temporary table is created and populated on disk, in the system database tempdb.

    • 在系统数据库tempdb中的磁盘上创建并填充临时表。

  • A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.

    • 表变量在内存中创建,因此比#temp表稍微好一点(也因为表变量的锁定和记录更少)。表变量可能仍然对tempdb执行I / O(这是#temp表的性能问题使其显而易见的地方),尽管文档对此并不十分明确。

  • Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.

    • 与临时表相比,表变量导致存储过程的重新编译更少。

  • [Y]ou can create indexes on the temporary table to increase query performance.

    • [Y]您可以在临时表上创建索引以提高查询性能。

Regarding your specific case with 50k rows:

关于50k行的具体情况:

As your data size gets larger, and/or the repeated use of the temporary data increases, you will find that the use of #temp tables makes more sense

随着您的数据量变大,和/或临时数据的重复使用增加,您会发现使用#temp表更有意义

References:

#2

There can be a big performance difference between using table variables and temporary tables. In most cases, temporary tables are faster than table variables. I took the following tip from the private SQL Server MVP newsgroup and received permission from Microsoft to share it with you. One MVP noticed that although queries using table variables didn't generate parallel query plans on a large SMP box, similar queries using temporary tables (local or global) and running under the same circumstances did generate parallel plans.

使用表变量和临时表之间可能存在很大的性能差异。在大多数情况下,临时表比表变量更快。我从私有SQL Server MVP新闻组获取以下提示,并获得Microsoft的许可与您共享。一个MVP注意到虽然使用表变量的查询没有在大型SMP盒上生成并行查询计划,但使用临时表(本地或全局)并在相同环境下运行的类似查询确实生成了并行计划。

More from SQL Mag (subscription required unfortunately, I'll try and find more resources momentarily)

更多来自SQL Mag(不幸的是,我会尝试订阅更多资源)

EDIT: Here is some more in depth information from CodeProject

编辑:这是CodeProject的一些更深入的信息

#1

The results will vary on which will be easier to store the data, in disk (#temp) or in memory (@temp).

结果将更容易在磁盘(#temp)或内存(@temp)中存储数据。

A few excerpts from the references below

以下参考文献的一些摘录

  • A temporary table is created and populated on disk, in the system database tempdb.

    • 在系统数据库tempdb中的磁盘上创建并填充临时表。

  • A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.

    • 表变量在内存中创建,因此比#temp表稍微好一点(也因为表变量的锁定和记录更少)。表变量可能仍然对tempdb执行I / O(这是#temp表的性能问题使其显而易见的地方),尽管文档对此并不十分明确。

  • Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.

    • 与临时表相比,表变量导致存储过程的重新编译更少。

  • [Y]ou can create indexes on the temporary table to increase query performance.

    • [Y]您可以在临时表上创建索引以提高查询性能。

Regarding your specific case with 50k rows:

关于50k行的具体情况:

As your data size gets larger, and/or the repeated use of the temporary data increases, you will find that the use of #temp tables makes more sense

随着您的数据量变大,和/或临时数据的重复使用增加,您会发现使用#temp表更有意义

References:

#2

There can be a big performance difference between using table variables and temporary tables. In most cases, temporary tables are faster than table variables. I took the following tip from the private SQL Server MVP newsgroup and received permission from Microsoft to share it with you. One MVP noticed that although queries using table variables didn't generate parallel query plans on a large SMP box, similar queries using temporary tables (local or global) and running under the same circumstances did generate parallel plans.

使用表变量和临时表之间可能存在很大的性能差异。在大多数情况下,临时表比表变量更快。我从私有SQL Server MVP新闻组获取以下提示,并获得Microsoft的许可与您共享。一个MVP注意到虽然使用表变量的查询没有在大型SMP盒上生成并行查询计划,但使用临时表(本地或全局)并在相同环境下运行的类似查询确实生成了并行计划。

More from SQL Mag (subscription required unfortunately, I'll try and find more resources momentarily)

更多来自SQL Mag(不幸的是,我会尝试订阅更多资源)

EDIT: Here is some more in depth information from CodeProject

编辑:这是CodeProject的一些更深入的信息

标签：

相关文章