CODEWITHSUNDEEP
sqlsql-server-2008
bluesky
bluesky

Reputation: 646

SQL Server performance temp tables or variables

I am currently optimizing a stored procedure, because it times out due to amount of calculations and entries present.

However, I am unsure about a situation. Is it best to use 8 temp tables (which draw their information from other data in the database) or to just put the data from the database into various variables and access them?

Which would give the best performance to fix the timeout issue?

Upvotes: 1

Views: 197

Answers (2)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

Based on your responses above, you should definitely use temp tables. You should also look at indexing them to increase your performance.

Upvotes: 0

marc_s
marc_s

Reputation: 755381

Depends on how many entries you have in the "temp" tables.

  • a real temp table #temp can have indices defined on it, it will be analyzed by SQL Server for its number of rows etc. and it does take part in transactions, e.g. you can insert rows and then rollback and those rows will "disappear" from your temp tabl

  • a table variable @temp will always be considered by SQL Server to have exactly one row - that's not a problem if you have just a handful of rows in there. But if you need to store hundreds of rows, this assumption by the SQL Server query optimizer can lead to highly inefficient query plans. Also: table variables do not participate in transactions - that can be a good thing - or a bad one - depending on your environment

Upvotes: 1

Related Questions