CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
CM2K
CM2K

Reputation: 883

performance/space sql: select into a @variable or into a #temp

My stored procedure gives me this error:

Could not allocate space for object 'dbo.Large Object Storage System object: 422213818384384' in database 'tempdb' because the 'PRIMARY' filegroup is full. "

In my query I'm using a lot of temporary tables. I was wondering:

What is better performance/space wise? Is there a difference between using temporary tables or table variables with SELECT INTO?

An example:

select * 
into #temp1 from myTable

select * 
into #temp2 from #temp1

--then add some columns on temp2 and do some joins with temp1

Would this same SELECT/JOIN be better with @temp1 and @temp2?

Upvotes: 1

Views: 385

Answers (2)

Anuj Tripathi
Anuj Tripathi

Reputation: 2281

Yes, Table variable and Temporary table are different though they both use TempDb.

Basic thumb rule for choosing table variable and temporary table is that you should choose table variable when you are dealing with less amount of data. Table variable don't involve themselves in transaction, locking and logging so they are comparatively faster (usually good for less than 100 rows)

Other than that, temporary tables allows you to have indexes on it and they are just like normal table which exists on temporary database (TempDb).Do not create too many temporary tables because TempDb has to do various other operations other than handling user defined temporary objects. So, in your case you can opt for physical table with proper indexes. I believe, you are dealing with lots of blob objects, which in both the cases (i.e. table variable and temp table) use TempDb.

Upvotes: 0

Goody
Goody

Reputation: 391

As others have stated, in terms of both space and performance there's really no difference. There is a misconception that table variables are stored in RAM, which is incorrect. Both are allocated in tempdb space.

Your error message indicates that the files used by tempdb have run out of allocated space, this probably needs addressing, running out of tempdb space can be a serious problem.

A general rule of thumb is in the first instance avoid creating unnecessary objects (i.e. temporary tables / temporary data sets) and let SQL Server do its best at optimising the query in its most simple terms.

If you create two temporary tables, without seeing data/queries, that's gonna generate twice the amount of data so will likely worsen your issue.

From what you have posted, you should avoid the use of a temporary table / table variable and instead use plain queries. You can you subqueries or common table expressions to tame query complexity.

Upvotes: 1

Related Questions