What temporary table is better, physical, virtual or variable?

Question

What temporary table is better, physical, virtual or variable?

FreeHansje

SSChampion

Points: 11751
More actions
March 2, 2005 at 11:54 pm

#87663

I'm trying to solve an exploding tempdb problem. I know where in the code the problem occurs and I'm pretty certain, that the problem is caused by a virtual table which is filled in some kind of loop. In trying to find&solve this problem a coworker suggested the use of a variable table instead of a virtual table, that is: a table created with DECLARE:
DECLARE @tmpTable
TABLE(
F1 varchar(12),
F2 int,
F3 DateTime
)
opposed to
create table #tmpTable(
F1 varchar(12),
F2 int,
F3 DateTime
)
I've never used the DECLARE option and wonder if there is a difference with the #table option. I notice in the tempdb, that the @tmpTable does not show. In BOL I find the possibility mentioned in the DECLARE help, nothing else.
Can someone tell me more about his? Is there a site somewhere with more info on this?
Added: And is there a way to check on the size of #tables in the tempdb? The name showing up in sysobjects has characters added to it which makes it impossible for me to query these tables.
Greetz,
Hans Brouwer

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76001 More actions · Answer 1

Firstly, within TEMPDB your #tmpTable will be called something like tmpTable__________________345u3i5y3. It has to append the rubbish on the end because a # table is specific to your session where as a ##table is available to all users whilst the creator logged on.

TEMPDB tables are created on disk. Table variables are created in memory. If the use to which you are putting the temporary table is to hold a small amount of data then a table variable would be a good bet.

Note that you cannot say INSERT @tblVariable EXEC usp_MyStoredProcedure

I have cursed mightily at this defficiency.

As far as an exploding TEMPDB table is concerned, have you got someone running a query with a very large sort?

LinkedIn Profile

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 2

Just to add to what David has stated (and does well always), depending on the SIZE of the data and the available memory to SQL will also depend on IF a table variable will ALWAYS be memory-resident or not.

Have you attempted to run SQL Profiler to capture the actual executions for problem determination?

The characters added to the end of the #TEMP table should not matter (in this case) IF you are running the code from internal to the SP. SQL is designed for you to be able to SELECT * FROM #TEMP.... Or something else to think about is to :

Inside the code DUMP the contents of the #TEMP table to a REAL table structured the same way in a different database to review the data and help with PD as well.....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

FWIW, http://support.microsoft.com/default.aspx?scid=kb;EN-US;305977

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 4

An additional factor not mentioned above :

Table variables cannot be used in parallel processing and so may lead to a less efficient query plan on a multi-processor box. I have found this in practice where a stored procedure ran fine in development on a single processor machine but took minutes on the much more powerful multi-processor production box. Simply replacing table variables with temporary tables solved the problem. Note that other stored procedures (which did not invoke parallel processing) ran perfectly well with table variables.