Post reply

Create Table in stored proc

  • January 18, 2010 at 7:24 am

    #215646

    Is there any gain, performance or otherwise, to be had by placing all "Create Table" statements at the top of your stored procedure as opposed to throughout the procedure as needed?

  • January 18, 2010 at 7:32 am

    #1104928

    Yes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 18, 2010 at 7:36 am

    #1104931

    Thanks, can you explain further?

  • January 18, 2010 at 7:43 am

    #1104937

    Sure I can.

    I assume you're not just asking if I can, but would like said explanation? 😉

    Whenever a DDL statement is executed in a batch, the execution plan for that batch is invalidated and will be recompiled whent he next DML statement starts (not alway true for Creating temp tables in SQL 2005+). Hence you'll be seeing high recompiles/sec and a higher than necessary CPU usage due to excessive compiles.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 18, 2010 at 7:46 am

    #1104940

    That's great. Thanks for the explanation and the humor! 😀

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply