HiveBrain v1.2.0
Get Started
← Back to all entries
patternModerate

Why is @temp table performance some times worse than #temp table performance?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whytempthanworseperformancesometimestable

Problem

I was recently working in a very slow stored procedure (took 5 minutes to run). I made a very small tweak from doing this:

declare @tempTable table
(
  ...
)
insert into @tempTable
select .....


to

select ... into #tempTable from someTable


The script then ran in ~2 seconds. What can explain this time difference?

Solution

Table Variables don't have statisics in the same way as Temp Tables normally they're assumed to have only 1 row. This incorrect estimate of rowcount will make a nested loop operation look like the best plan but when this is done for a larger amount of rows the cost can easier be greater than a table scan.

Context

StackExchange Database Administrators Q#10999, answer score: 12

Revisions (0)

No revisions yet.