Post reply

using temp tables good Idea

  • January 30, 2010 at 12:26 pm

    #209560

    I am basically new to t-sql 2005 and have the following temp table questions:

    1. I am wondering what is a better idea to use temp tables or alot of left joins to solve complex query issues?

    If so, can you tell me what is better and why?

    2. If temp tables are a better solution, I know that temp tables take room from tempdb. If there some procedure I could follow to make certain that I restore the space to tempdb? Let me know if doing a table create and then an insert into table is better to use that a select field(s) into new table?

    3. If using more left joins is a better solution, can you tell me why?

    4. is using several left joins with one or two temp tables a good idea?

  • January 30, 2010 at 12:32 pm

    #1111235

    Hi

    (and welcome at SSC 🙂 )

    As a general answer: It depends.

    It's hard (impossible?) to answer this question. A database server cannot be compared to something like .NET/C++/Java code which always works top down and left to right.

    The query engine of a database uses tons of different keys and decisions to execute a statement. Two very similar looking requirements could end up in completely different statements.

    As a rule-of-thumb:

    Always look into execution plans and verify performance with different data and production size databases.

    Greets

    Flo

  • January 30, 2010 at 1:14 pm

    #1111239

    I agree with what Flo said. To go a little bit further, a complex query with a lot of left joins may actually be using TempDB to create work tables behind the scene. The rule of thumb I go by is if the joins seems slow, use a lot of IO, or generate a lot of internal rows, then I'll try something else and compare. But, overall, it's highly dependent on the data in the tables.

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

  • January 31, 2010 at 6:00 pm

    #1111390

    thank you very much!

  • January 31, 2010 at 7:16 pm

    #1111398

    You're welcome. Thanks for the comeback.

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

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

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