CODEWITHSUNDEEP
sql-serverstored-procedurestemp-tablestable-variable
MaxiWheat
MaxiWheat

Reputation: 6251

Table variable poor performance on insert in SQL Server Stored Procedure

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 ?

Upvotes: 13

Views: 11863

Answers (4)

Duncan Smart
Duncan Smart

Reputation: 32058

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

Upvotes: 1

John Sansom
John Sansom

Reputation: 41819

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.

Upvotes: 13

Kuberchaun
Kuberchaun

Reputation: 30324

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.

Upvotes: 1

C-Pound Guru
C-Pound Guru

Reputation: 16368

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.

Upvotes: 0

Related Questions