Table variable poor performance on insert in SQL Server Stored Procedure

Question

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 ?

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