@table变量或#temp表：性能
我有一个大型用户定义的表类型变量，具有 129 列。
我将一次在此变量中存储大约 2000-3000 条记录，并将其传递给各种存储过程和函数以获取其他数据并在相同的新变量中进行修改类型并将这个新变量返回到源 SP。（这是因为表类型参数只能作为 READONLY 传递）。
这是我的算法。
SP1
@tmp tableType
{
INSERT @tmp EXEC
SP2 (@tmp)
INSERT @tmp EXEC
SP3 (@tmp)
}
我应该使用 @table varible 或 #temp table 中的哪一个
I have a big user defined table type variable having 129 Columns.
I will store around 2000-3000 Records in this variable at a time and passing this to various stored procedures and functions to get additional data and make modifications in a new variable of same type and returning this new variable to the source SP.(This is because a table type parameter can only passed as READONLY).
This is my algorithm.
SP1
@tmp tableType
{
INSERT @tmp EXEC
SP2 (@tmp)
INSERT @tmp EXEC
SP3 (@tmp)
}
Which one should I use @table varible or #temp table
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这里有一篇有用的文章 其中说：
与许多其他技术领域一样，这里没有“正确”的答案。对于不打算在过程范围之外保留的数据，您通常在 #temp 表和表变量之间进行选择。您的最终决定应取决于性能和合理的负载测试。随着您的数据量变大，和/或重复使用临时数据的增加，您会发现使用 #temp 表更有意义。根据您的环境，该阈值可能在任何地方 - 但是，如果上述任何限制构成重大障碍，您显然需要使用 #temp 表。
然而，另一种方法是使用真正的表来存储您需要的行任何事务中使用的信息都通过 GUID 列来表示。然后，您只需将 GUID 作为参数传递，这可能会提高性能。然而，这可能不适合您。
我建议您尝试这两个选项并查看 SQL Profiler 以了解哪些选项提供最佳性能。
There's a useful article here which says:
Like many other areas of technology, there is no "right" answer here. For data that is not meant to persist beyond the scope of the procedure, you are typically choosing between #temp tables and table variables. Your ultimate decision should depend on performance and reasonable load testing. 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. Depending on your environment, that threshold could be anywhere — however you will obviously need to use #temp tables if any of the above limitations represents a significant roadblock.
However another way would be to have real table where the rows you need used in any transaction are denoted by using a GUID column. You would then have to pass only the GUID as a parameter which may improve performance. However this may not be an option for you.
I suggust you just try out both options and have a look at SQL Profiler to see which options offer the best performance.
如果
#tmp只是用作堆来临时存储数据，那么我认为 @ 与 # 不会有什么区别，特别是对于仅包含几千行的表。唯一要记住的是，如果在某个时候您认为需要对表建立索引，那么临时表显然是最佳选择，因为表变量无法建立索引。
除此之外，有趣的是，我从未发现临时表的性能优于表变量，反之亦然。
If
#tmpis going to be used simply as a heap to temporarily store data then I don't think @ vs # will make a difference, especially for a table containing only several thousand rows.The only thing to keep in mind is that if at some point you think you'd need to index the table, then temp table is obviously the choice since table variables can't be indexed.
Aside from that, anecdotally speaking, I've never found a temp table to outperform a table variable, or vice versa.