Post reply

TempTables

  • May 22, 2014 at 2:55 pm

    #307638

    Hi,

    I am working with the SP tuning. I want to know clearly about the temp tables.

    Some people say

    Instead of Select * into #table, it is always better to create temp table structure and insert the data later

    Is it true in the case, if the data is small?

    insert into #table (select colmn1, column 2 from TableA join Table B on JoinC on joinD on..

    If they use several joins, in that situation, which way of creating temp table would be better?

    Thanks

  • May 22, 2014 at 3:49 pm

    #1715891

    The benefit of creating the table first is the ability to control the data types being used a little more easily.

    I will often use the Select into method for ease of use.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • May 22, 2014 at 4:42 pm

    #1715904

    Hi SQL RNNR,

    Your opinion is that there will nothing performance difference. Either way of creation of temp table is same.

  • May 22, 2014 at 4:44 pm

    #1715905

    ramana3327 (5/22/2014)

    Hi SQL RNNR,

    Your opinion is that there will nothing performance difference. Either way of creation of temp table is same.

    Performance between the two is equal.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • May 22, 2014 at 5:29 pm

    #1715911

    SQLRNNR (5/22/2014)

    ramana3327 (5/22/2014)

    Hi SQL RNNR,

    Your opinion is that there will nothing performance difference. Either way of creation of temp table is same.

    Performance between the two is equal.

    I guess I'd have to say "It Depends" here especially since TempDB is in the SIMPLE recovery model. SELECT INTO will be minimally logged... INSERT INTO will not.

    --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)

  • May 22, 2014 at 5:41 pm

    #1715914

    Jeff Moden (5/22/2014)

    SQLRNNR (5/22/2014)

    ramana3327 (5/22/2014)

    Hi SQL RNNR,

    Your opinion is that there will nothing performance difference. Either way of creation of temp table is same.

    Performance between the two is equal.

    I guess I'd have to say "It Depends" here especially since TempDB is in the SIMPLE recovery model. SELECT INTO will be minimally logged... INSERT INTO will not.

    Thanks Jeff for reminding us of that one.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • May 22, 2014 at 7:33 pm

    #1715926

    Thanks Jeff,

    I am thinking that insert into bulk logged operation.

    How the recovery model effect the performance here? In simple recovery log will not grow. We can't take any log backup.

    If you feel my questions are class room questions, sorry for that. I started my SQL career recently.

  • May 22, 2014 at 8:21 pm

    #1715928

    The logging can be a big deal, although tempdb has some special optimizations for tlog stuff.

    Another reason though to do the SELECT ... INTO is that come SQL 2014 that can be done FULLY PARALLIZED!! This can result in a massive speed increase for some actions.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • May 23, 2014 at 1:40 am

    #1715963

    Both INSERT INTO and SELECT ... INTO can achieve minimal logging.

    INSERT INTO can be minimally logged if the following conditions are met:

    * The recovery model of the database is set to simple or bulk-logged.

    * The target table is an empty or nonempty heap.

    * The target table is not used in replication.

    * The TABLOCK hint is specified for the target table.

    SELECT ... INTO holds a schema lock on the source table that can end up blocking other processes.

    -- Gianluca Sartori

  • May 23, 2014 at 2:20 am

    #1715974

    spaghettidba (5/23/2014)

    SELECT ... INTO holds a schema lock on the source table that can end up blocking other processes.

    It does not.

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    BEGIN TRANSACTION

    SELECT * INTO #SomeTable FROM <large table>

    SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID AND request_mode LIKE 'Sch-%'

    ROLLBACK

    You can run the select from sys.dm_tran_locks from another window while it's running too, to see that the only schema locks are in DB 2

    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

  • May 23, 2014 at 3:19 am

    #1715992

    GilaMonster (5/23/2014)

    spaghettidba (5/23/2014)

    SELECT ... INTO holds a schema lock on the source table that can end up blocking other processes.

    It does not.

    Thanks for correcting me, Gail.

    I remember I had this issue in the past but I'm unable to reproduce it now. Maybe an older version?

    Nevermind, I'm glad I don't need to worry about it anymore.

    -- Gianluca Sartori

  • May 23, 2014 at 7:15 am

    #1716043

    spaghettidba (5/23/2014)

    Both INSERT INTO and SELECT ... INTO can achieve minimal logging.

    INSERT INTO can be minimally logged if the following conditions are met:

    * The recovery model of the database is set to simple or bulk-logged.

    * The target table is an empty or nonempty heap.

    * The target table is not used in replication.

    * The TABLOCK hint is specified for the target table.

    SELECT ... INTO holds a schema lock on the source table that can end up blocking other processes.

    With the correction that Gail made, agreed, and thank you for the reminder on that (it's amazing how many options there are for such a "simple" thing). The one thing missing is that you don't have to write or maintain CREATE TABLE code for the SELECT INTO in most cases and you don't need to remember to use the TABLOCK hint.

    --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)

  • May 23, 2014 at 7:48 am

    #1716061

    ramana3327 (5/22/2014)

    Hi,

    I am working with the SP tuning. I want to know clearly about the temp tables.

    Some people say

    Instead of Select * into #table, it is always better to create temp table structure and insert the data later

    Is it true in the case, if the data is small?

    insert into #table (select colmn1, column 2 from TableA join Table B on JoinC on joinD on..

    If they use several joins, in that situation, which way of creating temp table would be better?

    Thanks

    Plenty of good tips here. I would like to add mine, at least, what I've seen from my Developers.

    If the temp table does not need Indexes, don't create any for them. I've found many cases like that at work. Where a simple temp table that is medium size has an Index on it and does not speed up the joins at all. In fact, they hammer CPU and makes it slower.

    So add Indexes if needed but test without them as well.

  • May 23, 2014 at 8:34 am

    #1716099

    Plenty of good tips here. I would like to add mine, at least, what I've seen from my Developers.

    If the temp table does not need Indexes, don't create any for them. I've found many cases like that at work. Where a simple temp table that is medium size has an Index on it and does not speed up the joins at all. In fact, they hammer CPU and makes it slower.

    So add Indexes if needed but test without them as well.

    I have come across this at clients so many times I don't think I can count them. Devs routinely slap indexes on for a SINGLE join or hit on that table, completely ignoring the significant work that has to be done to create the index in the first place. IF they tune that code, it must be in isolation and not as a package deal.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • May 23, 2014 at 8:43 am

    #1716106

    TheSQLGuru (5/23/2014)

    Plenty of good tips here. I would like to add mine, at least, what I've seen from my Developers.

    If the temp table does not need Indexes, don't create any for them. I've found many cases like that at work. Where a simple temp table that is medium size has an Index on it and does not speed up the joins at all. In fact, they hammer CPU and makes it slower.

    So add Indexes if needed but test without them as well.

    I have come across this at clients so many times I don't think I can count them. Devs routinely slap indexes on for a SINGLE join or hit on that table, completely ignoring the significant work that has to be done to create the index in the first place. IF they tune that code, it must be in isolation and not as a package deal.

    Just to amplify what Kevin has stated above... ostensibly, the only thing in the Temp Table is ALL the stuff you need and ONLY the stuff you need. A table scan is usually inevitable (even if it materializes as a SEEK followed by a range scan) and the overhead of the index is frequently unnecessary. Of course, it depends. It can sometimes prevent a huge number of reads from the "other" table during a join to the Temp Table and it's definitely worth looking at the Execution Plan.

    --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)

Viewing 15 posts - 1 through 15 (of 29 total)

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