Temp Tables vs. Physical Tables

  • 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

  • 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.

    .

  • 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

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

  • 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

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

  • 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

  • Thank you all for the valuable input and resources.

    John

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

  • 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

  • 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

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

  • 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

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

  • 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

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

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