I faced with strange situation: inserting of ~800 000 rows into table variable is much more faster than inserting (with the same select) into temporary table. INSERT is under stored procedure. SELECT have parameters from stored procedure.
Sample code below:
create procedure p1 @Param1 int AS declare @t table (id int) insert into @t select id from dbo.SomeTable where SomeField = @Param1
And if I change @t to #t - than I have more than 2.5 difference in procedure's execution time.
All the time as DBA I thought that so big row count should be processed in temporary tables... But now, I don't think so :)
Anyway, I'm just trying to understand what is going on and why the same SELECT have different execution plans during INSERT.
The same case (as I thought) was here: INSERT performance difference between temporary tables and table variable. But I have the opposite situation - INSERT into # is SLOWER than @
So, do you have any ideas?
- what is the better in this case: use @ or #?
- why do I get different execution plans ?
Execution plan here: https://drive.google.com/file/d/0B1iqgEJ5a7vfcjAwMmtJaVFrNWM/view?usp=sharing
Thank you in advance!
P.S. Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
P.P.S. I've found really good article about temporary tables and table variables here: What's the difference between a temp table and table variable in SQL Server?. But, unfortunately, it didn't help me.