CODEWITHSUNDEEP
sqlsql-serversql-server-2008table-valued-parameters
Sreekumar P
Sreekumar P

Reputation: 6050

@table variable or #temp table : Performance

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

Upvotes: 5

Views: 446

Answers (2)

openshac
openshac

Reputation: 5165

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.

Upvotes: 4

Derek
Derek

Reputation: 23238

If #tmp is going to be used simply as a heap to temporarily store data then I don't think @ vs # will make a difference, especially for a table containing only several thousand rows.

The only thing to keep in mind is that if at some point you think you'd need to index the table, then temp table is obviously the choice since table variables can't be indexed.

Aside from that, anecdotally speaking, I've never found a temp table to outperform a table variable, or vice versa.

Upvotes: 2

Related Questions