Temp Table Vs Table Variables

Question

Temp Table Vs Table Variables

vkundar

Hall of Fame

Points: 3431
More actions
June 10, 2011 at 9:51 pm

#239724

Hi All,
I have desgined a sproc which is called from a website , this sproc have lots of temp tables which get created calculated etc. I wanted to know:
1) If more than 20,000 users access this website which in turn call this sproc will the temp table create any perf problem? How these temp table is managed in a multi user environment ?
2) Table vaiable will be the right choice?
3) Do I have to use physical table to replace all the above two options?
Please share your recomendation.
Cheers,
Got an idea..share it !!
DBA_Vishal

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

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

vkundar (6/10/2011)
Hi All,
I have desgined a sproc which is called from a website , this sproc have lots of temp tables which get created calculated etc. I wanted to know:

Hmmm... please don't take this wrong but if it has "lots of temp tables", there may be a bit of a design problem and you may want to reconsider both the design of the tables and this proc that uses them. Of course, "It Depends" a whole lot on what the proc is actually doing.

1) If more than 20,000 users access this website which in turn call this sproc will the temp table create any perf problem? How these temp table is managed in a multi user environment ?

Again, "It Depends". SQL Server will certainly keep the Temp Tables in good order but if you have a couple of large ones or many small ones, you're going to need to make sure that every TempDB optimization and memory optimization has been made in order for 20,000 simultaneous users to perceive good performance. You should also have a hefty server (maybe even a federation of servers) to handle that many simultanous users. You'll also need lot's of memory whether you use Temp Tables or Table Variables because they both start out in memory and spill onto physical TempDB disk space when they no longer fit in memory. Please see what I consider to be the definitive article on that subject and others at the following URL: http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

2) Table vaiable will be the right choice?

Again, "It Depends". There are many factors affecting the choice to be made between Temp Tables and Table variables. See the article at the link I provided above for more information on that subject.

3) Do I have to use physical table to replace all the above two options?

Gosh... that's probably as bad an idea as using a global temp table but, depending on what you're doing, I have to again say "It Depends".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 2

Jeff Moden (6/10/2011)
Please see what I consider to be the definitive article on that subject and others at the following URL: http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

:blush:

Thanks Jeff!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

WayneS (6/11/2011)
Jeff Moden (6/10/2011)
Please see what I consider to be the definitive article on that subject and others at the following URL: http://www.sqlservercentral.com/articles/Temporary+Tables/66720/
:blush:
Thanks Jeff!

Well it IS! I don't know of any other article on the subject of Temp Tables vs Table Variables that covers so much in such a readable manner in a single place. I love that article! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ricer Right there with Babe Points: 722 More actions · Answer 4

Other things have been covered but be sure to have tempdb on its own disk so it doesn't fight for IO versus other databases.

There aren't any definitive answer foreveryone but creating 1/2 to 1 tempdb file per core seems the proper configuration.

-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer