SQL Server 存储过程中的表变量插入性能不佳
我们在存储过程中使用表变量时遇到性能问题。
这是实际发生的情况：
DECLARE @tblTemp TABLE(iId_company INT)
INSERT INTO @tblTemp(iId_company)
SELECT id FROM .....
SELECT 返回 138 个结果，但插入 TABLE 变量需要 1 分钟 15，但是当我使用具有相同 SELECT 的临时表时，woops，需要 0 秒：
CREATE TABLE #temp (iId_company INT)
INSERT INTO #temp(iId_company)
SELECT id FROM ...
什么可能导致这种行为？
We are experiencing performance problems using a table variable in a Stored Procedure.
Here is what actually happens :
DECLARE @tblTemp TABLE(iId_company INT)
INSERT INTO @tblTemp(iId_company)
SELECT id FROM .....
The SELECT returns 138 results, but inserting in the TABLE variable takes 1min15 but when I use a temp table with the same SELECT, woops, takes 0sec :
CREATE TABLE #temp (iId_company INT)
INSERT INTO #temp(iId_company)
SELECT id FROM ...
What could cause the behavior ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
使用临时表。您会看到更好的性能。
对此背后原因的详细解释超出了最初的范围
但问题总结如下：
行，由 SQL Server 即假设 1
将返回行。
统计数据。
Google 临时表与临时表表变量提供丰富的资源和讨论。如果您需要具体帮助，请给我发送电子邮件或在 Twitter 上与我联系。
Use a temporary table. You will see much better performance.
A detailed explanation for the reasoning behind this is beyond the scope of the initial
question however to summarise:
row, by SQL Server i.e. it assumes 1
row will be returned.
statistics.
Google temp table Vs. table variable for a wealth of resources and discussions. If you then need specific assistance, fire me an email or contact me on Twitter.
这并不重要，但你的选择是什么样的？我在 SQL Server 2005 中遇到了一个问题，我的选择本身对于我的查询正在执行的操作来说运行得相对较快，比如 5 分钟即可通过网络返回大约 150,000 行的所有数据。但是，当我尝试将相同的选择插入临时表或表变量时，该语句运行了 1 个多小时，然后我将其杀死。我还没弄清楚到底发生了什么。我最终添加了查询提示强制顺序，并且它开始插入得更快。
Not that it should matter but what does your select look like? I had an issue in SQL Server 2005 where my select on it's own ran relatively fast for what my query was doing say 5 minutes to return all the data over the wire about 150,000 rows. But when I tried to insert that same select into a temp table or table variable the statement ran for more than 1 hour before I killed it. I have yet to figure out what really was going on. I ended up adding the query hint force order and it started inserting faster.
关于临时表的关键点还在于，您可以在其上放置索引等，而不能使用表变量。
Key point about temp tables also is that you can put indexes, etc on them whereas you can't with table variables.
通常，对于较小的数据集，表变量应该比临时表更快。对于较大的数据集，性能会下降，因为表变量不支持并行性（请参阅这篇文章）。
话虽如此，我还没有经历过或发现表变量与临时表相比如此小的数据集速度较慢。
Generally, for smaller sets of data, a table variable should be faster than a temp table. For larger sets of data, performance will fall off because table variables don't support parallelism (see this post).
With that said, I haven't experienced, or found experience with such a small set of data being slower for a table variable vs a temp table.