表变量在SQL Server存储过程中插入时性能较差 [英] Table variable poor performance on insert in SQL Server Stored Procedure

问题描述

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

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存储过程中插入时性能较差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！