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

问题描述：

我们在存储过程中使用表变量遇到性能问题.

We are experiencing performance problems using a table variable in a Stored Procedure.

这是实际发生的事情:

DECLARE @tblTemp TABLE(iId_company INT)

INSERT INTO @tblTemp(iId_company)
  SELECT id FROM .....

SELECT返回138个结果，但是在TABLE变量中插入需要1分钟15分钟，但是当我使用具有相同SELECT的临时表时，请耗时0秒:

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 ...

什么可能导致此行为?

答

使用临时表.您会看到更好的性能.

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:

一个表变量针对一个变量进行了优化按SQL Server行，即假设1 该行将被返回.
表变量未创建统计信息.

A table variable is optimized for one row, by SQL Server i.e. it assumes 1 row will be returned.
A table variable does not create statistics.

Google 临时表与表变量，以获取大量资源和进行讨论.如果您随后需要特定的帮助，请给我发送电子邮件或在Twitter上与我联系.

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存储过程中插入时性能较差

相关推荐