@table variable or #temp table : Performance

Question

I have a big user defined table type variable having 129 Columns.
I will store around 2000-3000 Records in this variable at a time and passing this to various stored procedures and functions to get additional data and make modifications in a new variable of same type and returning this new variable to the source SP.(This is because a table type parameter can only passed as READONLY).

This is my algorithm.

  SP1
  @tmp tableType
  {
        INSERT @tmp EXEC
        SP2 (@tmp)

        INSERT @tmp EXEC
        SP3 (@tmp)

  }

Which one should I use @table varible or #temp table

Answer 1

There's a useful article here which says:

Like many other areas of technology, there is no "right" answer here. For data that is not meant to persist beyond the scope of the procedure, you are typically choosing between #temp tables and table variables. Your ultimate decision should depend on performance and reasonable load testing. As your data size gets larger, and/or the repeated use of the temporary data increases, you will find that the use of #temp tables makes more sense. Depending on your environment, that threshold could be anywhere — however you will obviously need to use #temp tables if any of the above limitations represents a significant roadblock.

However another way would be to have real table where the rows you need used in any transaction are denoted by using a GUID column. You would then have to pass only the GUID as a parameter which may improve performance. However this may not be an option for you.

I suggust you just try out both options and have a look at SQL Profiler to see which options offer the best performance.