Home Forums SQL Server 2008 SQL Server 2008 Performance Tuning very slow INSERT to #tmptable RE: very slow INSERT to #tmptable

  • April 21, 2015 at 8:23 pm

    #1792607

    joepositive (4/21/2015)

    Jeff Moden (4/21/2015)

    Just a curious shot in the dark on this... what is the default collation of the database you're select from and what is the default collation of TempDB?

    Interesting question, but both the user db and tempdb are SQL_Latin1_General_CP1_CI_AS.

    Other things to consider:

    1) this behavior is sporadic

    2) the behavior seems to be related to the method of INSERT, not the SELECT itself

    3) in addition to the other workarounds mentioned, recompiling the SP seems to alleviate this. But it shouldn't/

    To be honest, that's why I almost always use SELECT/INTO to create a Temp Table. And the old wives table about it locking up TempDB hasn't been true since SP1 of SQL Server 6.5. Yep... it still takes a couple of shared locks that will tick off anyone trying to expand a folder in Object Explorer, but it has very little effect, otherwise. Just don't use it across linked servers. It still tends to lockup the source server especially if it's an SQL Server. Not sure how or why it happens, though.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)