Which is faster or better for performance ??

Question

Which is faster or better for performance ??

kingram-15027

Ten Centuries

Points: 1074
More actions
May 16, 2005 at 6:51 am

#109590

hi,
which of the (2) is faster or better for performace.. #tempTable or @table
i have read information on both..

thanks

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

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

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

It depends.

Temp tables are created in tempdb, just as any other real table. That means it is created and stored on disk with the same structures as any other table. One of the benefits of this is that it is possible to create indexes for the table, which is of course good for performance.

Table variables are created in memory only, so they are fast to create and access. You cannot create an index for them however so if you are going to be accessing them several times over and/or performing seeks in them (especially if there is a lot of data) then a temp table is often faster.

As always, test.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

kingram-15027 Ten Centuries Points: 1074 More actions · Answer 2

thanks ! thats what i was thinking but wasnt really sure...

thanks

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 3

Chris has great advice. Be sure that you test under loads to be sure which one might run better.

Sebastiano Vacirca SSC Veteran Points: 281 More actions · Answer 4

I agree its important to test. I performed a similar test with a variable table and a temp table. The variable table was about 20% slower than using a temp table. If your process updates the variable table frequently, then the variable table will be slightly quicker. If your just using the temp table to filter data, the my testing concluded that the temp table was 20% faster than using the variable table.

FYI: The data definition of the variable table is stored in tempdb.

nileshsane Default port Points: 1465 More actions · Answer 5

Table variables are NOT created in memory. They are created in temp DB. As a rule of thumb use table variables if you are "NOT DEALING" with hugh amounts of data. You should also know the scope of the table variable before you use it.

========================================================

From Microsoft KB

A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977

What I hear I forget, what I see I remember, what I do I understand