Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Performance Tuning for the procedure RE: Performance Tuning for the procedure

  • June 25, 2013 at 8:39 am

    #1627159

    Luis Cazares (6/25/2013)

    Sean Pearce (6/25/2013)

    michal.lisinski (6/25/2013)

    Hi Gila

    You have the right to disagree with my opinion, but there is no "right" answer here. For data that is not meant to persist beyond the scope of the procedure, you are choosing between #temp tables and table variables.

    Decision should depend on performance and reasonable load testing.

    I read an article on SqlServerCentral about it some time ago http://www.sqlservercentral.com/articles/Temporary+Tables/66720

    and I fully agree that real test give optimal solution.

    Regards

    Mike

    There is a "correct" answer here. Table variables do not perform faster than temp tables and your suggestion will not help the OP with his performance issues.

    Well, table variables might perform better in very specific ocassions. However, it won't be a significant improvement if the procedure is taking several minutes or even hours to run. There's not much to work here and even less to give a correct solution.

    Exploit the differences. Table variables are handy if you wish to save some state when a transaction rolls back. It's about all I use them for.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden