Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
We can validate MartinC's answer if you post the execution plans for both versions of your stored proc. –  Nick Chammas Jan 19 '12 at 21:01
    
@NickChammas - I'd love to, but I don't recall which stored procedure it was now :(. –  O.O Jan 19 '12 at 21:03
add comment

3 Answers 3

up vote 12 down vote accepted

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.

share|improve this answer
add comment

Just to add to @MartinC's answer that the row count for table variables is maintained in tempdb.sys.partitions and OPTION(RECOMPILE) can cause this to be used but it doesn't have any more granular statistics to use so will need to fall back on guesses based on that.

You have only shown the population code rather than any code that uses it. Another limitation of queries that insert to table variables is that they cannot have a parallel plan so that could explain why the population query might run faster.

share|improve this answer
add comment

MartinC is correct, plus you could also apply indexes to temp tables. This couldn't be done with a table variable.

share|improve this answer
2  
It is possible to add indexes to table variables via constraints both Primary Key and Unique but these still have the same assumptions i.e. 1 row. –  MartinC Jan 19 '12 at 21:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.