none
Query using temp objects are slow

    Question

  • Inserts and updates into temp tables are really slow. There are two datafiles for tempdb, same size on the same disk.

    Is there anything I should do to increase the performance of the queries that involve temptables?

    Wednesday, February 29, 2012 4:19 PM

Answers

  • Hi,

    the usage of the temp DB should be transparant for the Query writer. You should write well-tuned SQL-queries.

    However, if your temp db is a bottleneck, you could move it to a Solid State disk in your server!! Strange idea?!
    No:

    • it is temporary storage
    • Temp DB's are most of the time relatively small.
    • their read:write speeds exceed those of classical HD's

    You could buy the smallest SSD available on the market, which is more than enough for temp db's.

    Of course, the quality of you queries should be checked first; maybe you could give an example here of a query with a problem...

    Regards
    Wim


    Wednesday, February 29, 2012 9:46 PM
  • What kind of load are you doing into a temporary table? Do you have indexes on the table?

    http://technet.microsoft.com/en-us/library/cc966545.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, March 01, 2012 7:32 AM
    Moderator

All replies

  • Hi,

    the usage of the temp DB should be transparant for the Query writer. You should write well-tuned SQL-queries.

    However, if your temp db is a bottleneck, you could move it to a Solid State disk in your server!! Strange idea?!
    No:

    • it is temporary storage
    • Temp DB's are most of the time relatively small.
    • their read:write speeds exceed those of classical HD's

    You could buy the smallest SSD available on the market, which is more than enough for temp db's.

    Of course, the quality of you queries should be checked first; maybe you could give an example here of a query with a problem...

    Regards
    Wim


    Wednesday, February 29, 2012 9:46 PM
  • It's a bit early to be talking about hardware modifications when the OP has not provided any details on the tables or queries being used.

    OP, please provide DDL for your tables and queries, and sample data to test your queries.


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    Thursday, March 01, 2012 2:00 AM
  • What kind of load are you doing into a temporary table? Do you have indexes on the table?

    http://technet.microsoft.com/en-us/library/cc966545.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, March 01, 2012 7:32 AM
    Moderator
  • Hi Wharty,

    That's the reason why I stated: "Of course, the quality of you queries should be checked first; maybe you could give an example here of a query with a problem"

    However, If you read his question, it is really a question about the physical aspects of the Temp db: slow read and slow writes.
    I know that tuning doesn't start at the point of changing hardware, but in this case I just answered the question.

    And honestly, if I was a DBA I would always suggest to add a local SSD for Temp DB! Especially when working with external storage systems.

    Regards
    Wim





    Thursday, March 01, 2012 9:10 AM
  • However, If you read his question, it is really a question about the physical aspects of the Temp db: slow read and slow writes.

    Don't think so.  The performance issues could be with the temp tables they've created i.e. no or inappropriate indexes, wrong datatypes.  If it was TempDB then inserts and updates would be slow for all tables, not just temp tables.

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    Thursday, March 01, 2012 9:15 AM
  • And honestly, if I was a DBA I would always suggest to add a local SSD for Temp DB! Especially when working with external storage systems.

    This would also be a bad suggestion as SSD is not required for high performance.  If you beleive it is, can you please provide some statistics or proof that shows SSD should be used for TempDB.

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    Thursday, March 01, 2012 9:19 AM
  • Maybe we should wait for his first reaction ;)

    I think the question above can go in many directions...

    Thursday, March 01, 2012 9:20 AM
  • Yes.  It all depends on whether the OP means TempDB when they talk about Temp Tables as they are two different things i.e, a temp table is not TempDB.

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    Thursday, March 01, 2012 9:21 AM
  • What would you consider as a proof?

    (maybe we should continue this via another medium?)

    Thursday, March 01, 2012 9:26 AM
  • Setting up a new discussion topic would be better.

    Proof would be statistical comparisons of tests conducted on SSD and other drives.  These tests would also need to include a comparison of cost, performance, life expectancy etc

    It's very easy to come out with a statement saying that SSD should always be used for TempDB however the cost to performance ratio may not be worth it.


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    Thursday, March 01, 2012 9:33 AM
  • Hi Mr. Wharty,

    I've sent a message on your facebook....

    Regards
    Wim


    Thursday, March 01, 2012 11:00 AM