2

I create a temporary table variable (@table). Then I inner join it with another table. It takes a long time to display result. I try again with #table. It's normal. What's wrong with it?

Avinash Jain
  • 7,200
  • 2
  • 26
  • 40
blu3
  • 27
  • 7
  • Different query plans? – jazza1000 Nov 20 '15 at 07:21
  • 1
    This `@Table` is a **table variable** (not a temporary table) which differs from a temporary table in such a manner that the query optimizer will **always** assume there's only a single row in that table variable. If you have hundreds or thousands of rows in there, this can lead to a horribly bad execution plan and therefore to bad performance. Use a **proper** temp table ('#table`) instead- that temp table has statistics and the query optimizer will know how many rows it has – marc_s Nov 20 '15 at 07:25
  • @marc_s, [SQL Server 2014 improved cardinality estimates for table variables](http://sqlmag.com/sql-server-2014/improvements-table-variables-and-temporary-tables-sql-server-2014). There are still differences with temp tables, but row count of table variable is visible to the optimizer. – Vladimir Baranov Nov 20 '15 at 12:51
  • @marc_s : Thank you ! – blu3 Dec 14 '15 at 10:01

1 Answers1

0

If you store too much data in temp table or temp table variable then it result in poor performance. Because these temp tables variables not allow indexing and in temp table most of the time developer forget to use proper indexing therefore during join complete table scan happen which slow the query output.

Another important point to notice, avoid joins on varchar column.

Avinash Jain
  • 7,200
  • 2
  • 26
  • 40
  • You can index a temp table (but not a temp table variable) – jazza1000 Nov 20 '15 at 10:07
  • Yes, but most of time i found developer not implement it and run into the performance issue. I have edited the response – Avinash Jain Nov 20 '15 at 10:14
  • @jazza1000, In SQL Server 2014 it is possible to create indexes on table variables, though not all possible variants: http://stackoverflow.com/questions/886050/sql-server-creating-an-index-on-a-table-variable – Vladimir Baranov Nov 20 '15 at 12:40