Temp Tables vs. Physical Tables

Question

Temp Tables vs. Physical Tables

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
January 23, 2009 at 3:25 pm

#129825

What kind of storage requirements are required when creating a physical table vs. creating a temp table since I know the temp table only exists while the session is open. So for optimal disk space/performance which is better?
thx,
John

Viewing 15 posts - 1 through 15 (of 20 total)

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

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

Physical tables are USUALLY created to store the application data. They store all the persistent data that your application needs.

Temporary tables are used to store temporary information. Most of the times you dont need to keep them after you are done with the data. For example, a reporting stored procedure might decide to store the intermediary results in a temp table and process them for better performance.

So use TEMP tables to store all the temporary information and physical tables to store all the REAL data.

.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

also keep in mind temp tables (@ / # / ## ) are hosted in tempdb. tempdb is used at (sqlserver) instance level, for more than only storing temp tables. (sorting, grouping, ..)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

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

ALZDBA (1/24/2009)
also keep in mind temp tables (@ / # / ## ) are hosted in tempdb. tempdb is used at (sqlserver) instance level, for more than only storing temp tables. (sorting, grouping, ..)

Actually, Temp Tables and Table Variables start out in memory... if they fit, that's as far as they go. If they don't, then they spill into the physical disk.

But, don't take anyone's word for that... instead of sifting through the manure to figure out what the horse was thinking, ask the horse. 😛 Please read the following Microsoft FAQ...

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

--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)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

Jeff Moden (1/24/2009)

... if they fit, that's as far as they go. If they don't, then they spill into the physical disk.

"If" is the unknown over here, so anyone needs to know temptb work different than regular tables.

And actually, it's the KB ref the OP needed.

Thanks for the ref.

Although it didn't pull up the kb.

this url worked for me http://support.microsoft.com/kb/305977/en-us

There is also a WP regarding tempdb. It is called "Working with tempdb in SQL Server 2005" and it contains "WorkingWithTempDB.doc".

general performance guidelines can be found overhere http://support.microsoft.com/kb/110352/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Thanks for the links, Johan... I'll take a look (am I spelling your name correctly?)

--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)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

Jeff Moden (1/25/2009)
Thanks for the links, Johan... I'll take a look (am I spelling your name correctly?)

Yep, Johan is correct.:w00t:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 7

Thank you all for the valuable input and resources.

John

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

ALZDBA (1/25/2009)
general performance guidelines can be found overhere http://support.microsoft.com/kb/110352/

Wow... they don't write 'em like that anymore.

--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)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 9

It's a petty they've put a "Retired KB Content Disclaimer".

Apparently it isn't flashy anymore to summarise basic symple guidelines.

It must all be point and click and it will work..... until one notices errors, locks, unresponsive apps., ...

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

465789psw Hall of Fame Points: 3314 More actions · Answer 10

one important thing if you are going to be using temp tables make sure your temp DB is on it own spindle.

With that said make sure you create one tempDB file for each CPU, so if you are running a a 2 quad proc then create 8 temp db files and keep the one log.

also make sure you create take the current size of the temp DB, and then divide it up and reboot the server this will allow the files to work evenly.... so again if the tempdB is 4 gig then create 8 500 meg drive and reboot this will allow them to grow together.

This will allow the each CPU to have its own temp db and will avoid long wait times......

good luck

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

Funny how people keep trying to optimize hardware... the code is the great divide... I've seen servers with all those wonder changes to the hardware occur... and the code didn't budge for performance.

--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)

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 12

jacob sebastian (1/24/2009)
Physical tables are USUALLY created to store the application data. They store all the persistent data that your application needs.
Temporary tables are used to store temporary information. Most of the times you dont need to keep them after you are done with the data. For example, a reporting stored procedure might decide to store the intermediary results in a temp table and process them for better performance.
So use TEMP tables to store all the temporary information and physical tables to store all the REAL data.

Very true, must disagree with Jeff's last post tho, sorry Jeff, but there's nothing better than setting up your first raid server with striped and partitioned drives according to tempdb, data, log and index use. Does anyone still do that or is it all fibre optic SANs.

Max

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Sorry... didn't say it quite the right way... I meant that the real problem code they were trying to fix at my old company didn't budge... aggregate views of aggregate views... triangular joins... all sorts of nightmares.

So far as setting up that very first raid server goes.... yyyeeeaaaahhhh! That was fun and exciting... the good code just flew.

--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)

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 14

Yip, the pleasures are few enough, rejoice.

Umm, yeah, well unfortunately the raid didn't sort out my indexes...

Still love temp tables all sorts and sizes there's @ and # and my personal favourite for enterprise solutions: ## (please note the mild sarcasim).

Here comes SYSTEM_USER, anyone use this to determine table spaces?

Max