1

I have something like:

DECLARE @tbl TABLE
(
     name varchar(255),
     type int
)

UPDATE c
SET c.name = t.name
FROM dbo.cars c
JOIN @tbl t ON t.type = c.type

I have a stored procedure that does something similar but it takes over 20 minutes with the table variable. It runs in less than 2 minutes if I change it from table variable to temp table. Why is this so?

user609926
  • 801
  • 2
  • 12
  • 25
  • 4
    Look at the execution plans. I would guess that the temporary table has better statistics, so the `join` is optimized better. – Gordon Linoff Nov 22 '16 at 02:03
  • There is no statistics associated with Clustered Key Creation in Table Variable which can be seen from the Estimated Number of Rows Value. This could be bad when data is more. Like @GordonLinoff mentioned if you see the execution plan you will where is the difference. [Here is](https://blogs.msdn.microsoft.com/naga/2015/05/10/sql-server-performance-tuning-table-variable-vs-temporary-tables/) a blog explains it bit why. Also you can try with `OPTION RECOMPILE`. – Mahesh Nov 22 '16 at 02:47
  • 1
    Also you can try with `OPTION RECOMPILE` for table variable query which will detect cardinality and improve the performance. [Here is](http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386) good answer explaining this. – Mahesh Nov 22 '16 at 02:53

1 Answers1

1

I think this answer helpfull for you

https://stackoverflow.com/a/64891/1887827

I recommend that you look at this link;

https://support.microsoft.com/en-gb/kb/305977

Community
  • 1
  • 1
kcelik
  • 11
  • 3
  • 1
    Link only answers are not particularly good answers and are discouraged on Stack Overflow. Read more about that [here](https://stackoverflow.com/help/how-to-answer) (Provide context for links) and [here](http://meta.stackexchange.com/q/8231/273645). – TT. Nov 22 '16 at 07:35