6

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?

  • 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
12

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.

3

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.

0

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

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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