SQL Server Performance

use of temp tables

Discussion in 'SQL Server 2005 General Developer Questions' started by jazzy, Jan 30, 2010.

  1. jazzy New Member

    I am basically new to t-sql 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?
  2. preethi Member

    Welcome to forums!
    [quote user="jazzy"]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?[/quote]
    The answer is not that simple. (Or the simple answer is "it depends")
    It depends on many things including the query, your expectations, current issues with the environment and many others.
    1. What do you mean by "Complex queries"? Using many tables or complex calculations?
    2. How big the tables are ?
    3. How heavily used the tables are?
    4. When are you executing the query?
    5. Do you have all necessary indexes to cover the query?
    6. Where your user database and tempdb are placed? Do they share the same drives?
    7. What do you want to achieve? Reduce the total execution time, IO operations, locks on key resources, or complexity of the query?
    There are some advantages in using tempdb under certain situations
    1. When you have many tables (LEFT) joined in a single query, the locks will be placed on all tables until the entire query is completed. When you break them into multiple smaller queries, not all tables will be locked at the same time. This gives more time for other users. (Unless you have a mission critical query and the whole thing happens under one transaction)
    2. It also reduces the complexity. This enables faster debugging
    3. When the query is complex, the execution plan become complex and cost for both creating a plan and cost for executing go high. This needs more resources from SQL Server.
    4. There is a possibility where SQL Server can make a less optimal plan.
    5. temp tables are created in a different database (tempdb). If they are placed in different disks you can take advantage of of parallel IO operations
    6. As temp tables are visible only to the users they will not have locks.
    7. The execution plans are predictable with simplex queries.
    The disadvantages are:
    1. Moving the data into temp table will take additional resources
    2. When you move a part of the data into temp table, the next time you need to perform a table scan.
    3. As many operations are added, the total execution time will increase.
    4. There is a possibility of unexpected tempdb growth.
    5. At times, even tempdb could be a bottleneck. Remember: you can have many user databases in a server, but you will have only one tempdb.
    [quote user="jazzy"]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?[/quote]
    I don't understadn the first part of the question. But SELECT INTO is better than INSERT INTO as former is minimally logged.
    I believe the answer to the first question answers the 3rd and 4th questions too.
  3. jazzy New Member

    Thank you very much!
    This answers my question
  4. preethi Member

    You are welcome. Glad that we were able to help you.

Share This Page