patternModerate
Why is @temp table performance some times worse than #temp table performance?
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:
to
The script then ran in ~2 seconds. What can explain this time difference?
declare @tempTable table
(
...
)
insert into @tempTable
select .....to
select ... into #tempTable from someTableThe 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.