Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Unanswered: temp tables Vs Tables

    Hi,
    Are there any significant performance benefit if we avoid temp tables?
    Are the temp tables bad for good performance of queries?
    Is it better to create table ,then use it after that drop it ,rather then using temp tables?

    Any comments would be greatly appreciated.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think Temp Tables Vs Table Variables is probably more enlightening.
    http://sqlnerd.blogspot.com/2005/09/...variables.html

    I can't imagine that creating a "permanent" table and then dropping it will confer any advantages - but it could cause concurrency problems.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A lot depends on the number of rows and the complexity of the query. If you need to use indexing, temp tables are really tough. If you only have a few thousand rows of data or less, or if you have a simple table scan, then temp tables are ideal.

    The only time I really recommend using a permanant scratch table is if the queries involved really benefit from indicies. If that is the case, I recommend using a "token" such as the spid to separate different queries that might be running at the same time, and including that token value in the index expression(s). Delete any data using your token before you start (to clear any previous runs). Add data to the table using your token.

    For 99% of all queries I do, temp tables are the best answer.

    -PatP

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Thanks a lot to both of you...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Pat Phelan
    A lot depends on the number of rows and the complexity of the query. If you need to use indexing, temp tables are really tough. If you only have a few thousand rows of data or less, or if you have a simple table scan, then temp tables are ideal.

    The only time I really recommend using a permanant scratch table is if the queries involved really benefit from indicies. If that is the case, I recommend using a "token" such as the spid to separate different queries that might be running at the same time, and including that token value in the index expression(s). Delete any data using your token before you start (to clear any previous runs). Add data to the table using your token.

    For 99% of all queries I do, temp tables are the best answer.

    -PatP
    How about table variables. Sorry just read throught Pootle's post.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pat, temp tables can be indexed out the wazoo. It's just table variables that are limited to a clustered index.

    I use table variables for small datasets. I use temp tables for larger datasets, or data sets that are referenced multiple times in a procedure and could benefit from additional indexing.

    I NEVER create permanent tables as temporary storage. EVER.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Pat, temp tables can be indexed out the wazoo. It's just table variables that are limited to a clustered index.
    Yep, you can sure create those indexes on temp tables... Getting the blasted engine to actually use them is a different story. It can be done, but I've never found a way to reliably get the database engine to use them.

    -PatP

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Another concern is if your database is in FULL recovery mode, you may not want (or care) to have the extra transactions in the log.

  9. #9
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    This is something interesting here about temp tables....link
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan
    Yep, you can sure create those indexes on temp tables... Getting the blasted engine to actually use them is a different story. It can be done, but I've never found a way to reliably get the database engine to use them.
    -PatP
    I've never noticed the engine not using appropriate indexes on my temp tables, but I haven't necessarily checked for it either. I'll have to monitor it the next few times I do this. I HAVE seen tremendous performance improvements on my sprocs gained from adding indexes to temp tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rudra
    This is something interesting here about temp tables....link
    Articles that start off with "In general, temp tables should be avoided", when they should just say "Don't use temp tables if you don't need them" kinda pi$$ me off. Noobs don't read beyond the first few sentences and come away with the idea that temp tables are bad. They are not.
    As with any programming, simplicity is desirable, so you should not clutter up your code with temp tables unnecessarily. But there are instances where temp tables are MUCH faster than derived tables, such as when a derived recordset is referenced multiple times in a single sql statement or in a single procedure.
    Also, the article referenced discusses the advantages of usinge table variables over temp tables, but does not discuss the disadvantages. Overall, I would say this article is of very poor quality.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by blindman
    Noobs don't read beyond the first few sentences and come away with the idea that temp tables are bad.
    Noobs? whats that ?? used generally as an insult,really !! http://en.wikipedia.org/wiki/Newbie
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  13. #13
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by rudra
    This is something interesting here about temp tables....link
    Yes, I get mixed/conflicting feedback from websites concerning temp tables.

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    One thing to be aware of if you use temp tables: if tempdb runs out of space, which is not uncommon, then ALL your databases that are using tempdb will go down. It's a single point of failure, which is bad.

    To insulate yourself from this problem, you can use a scratch table local to your own db, not a temp table.

    but by all means, if you have a small number of rows to store, use table variables.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jezemine
    One thing to be aware of if you use temp tables: if tempdb runs out of space
    ..... then tempdb just grows as required right?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •