Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Peter He Temporary table performing much better than table variable RE: Temporary table performing much better than table variable

  • January 21, 2011 at 8:57 am

    #1276611

    CirquedeSQLeil (1/21/2011)

    Ajit Goswami-482889 (1/20/2011)

    All your guesses are true but my issues is not mainly about the performance but the weird behavior that is being demonstrated by SQL.

    I am using 3 table variables and i get the data into the first 2 and then use them to populate the 3rd one.

    So just changing the 3rd table variable into temp table to which i am just inserting this data from the same query is boosting the performance.

    And i can't believe that this is because of any memory issues because i have tried this same query in 3 different servers all having different configuration.

    This is not weird behavior by SQL, but expected. That is why Wayne made those three assertions. Table Variables may perform significantly slower if those three factors are true.

    I'd go so far to say that they WILL perform significantly slower if any of those three factors are true - especially when combined. The issue, as previously explained, is because of lack of statistics on the table variables - they can't have any, and sql needs them to generate good execution plans.

    Is there anything else different? Did you add any indexes to the temporary table?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2