表变量在SQL Server存储过程中插入时性能不佳

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 :

SELECT返回138个结果，但插入TABLE变量需要1分15但是当我使用具有相同SELECT的临时表时，woops需要0sec：

CREATE TABLE #temp (iId_company INT)

INSERT INTO #temp(iId_company)
  SELECT id FROM ...

What could cause the behavior ?

什么可能导致这种行为？

4 个解决方案

#1

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:

对此背后的推理的详细解释超出了最初问题的范围，但总结如下：

A table variable is optimized for one row, by SQL Server i.e. it assumes 1 row will be returned.
SQL Server的表变量针对一行进行了优化，即它假定将返回1行。
A table variable does not create 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.

谷歌临时表比。表变量用于丰富的资源和讨论。如果您需要特定帮助，请给我发电子邮件或在Twitter上与我联系。

#2

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.

话虽如此，我没有经历过，或者发现这样一小组数据对于表变量和临时表的速度较慢。

#3

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.

不是它应该重要，但你的选择是什么样的？我在SQL Server 2005中遇到了一个问题，我自己的选择运行起来相对较快，因为我的查询正在做5分钟，通过网络返回所有数据大约150,000行。但是当我尝试将相同的select插入临时表或表变量时，语句运行超过1小时才杀死它。我还没弄清楚到底发生了什么。我最终添加了查询提示强制命令，它开始插入更快。

#4

Key point about temp tables also is that you can put indexes, etc on them whereas you can't with table variables.

关于临时表的关键点还在于你可以在它们上放置索引等，而不能使用表变量。

#1