SQL Server Performance

Temp table in Transaction

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, Jan 20, 2010.

  1. atulgoswami New Member

    Does it make any point to put temp table insert/update in transaction (BEGIN/COMMIT Tran)?
    I am not able to think of any such scenario however for any small error (Which could cause to continue SP processing), SET XACT_ABORT ON is alreay mentioned.
    And that too if it is happening in cursor loop....I mean for each itriation, BEGIN and COMMIT is happening.
    I think it is simply an un necessary use of transaction and could hurt performance. What experts say on this?
    Thanks in advance
  2. preethi Member

    I am not sure what the experts say, But this is what I feel:
    As you are using BEGIN TRAN and COMMIT TRAN within a loop. You will be using the same session for many transactions. In your case, if a transaction rolls back, temp table inserts/updates too will be rolled back. If you don't have a transaction each statement is considered as a single transaction. That means only the statement fails will rollback not all statements within that loop.
    If you want to avoid transactions, you can place and error handler and do the rollback code manually.
  3. michaellutz New Member

    Transactional support in SQL Server provides for maintaining ACID characteristics: Atomicity, Consistency, Isolation, and Durability. Isolation is automatically handled by temp tables because they can't be seen by other sessions. Durability is meaningless in tempdb (where all temp table live) because tempdb is recreated from scratch every time the SQL Server instance is restarted. Atomicity is meaningless for temp tables after the stored procedure creating them has finished execution because the temp tables are immediately unavailable; additionally, each individual temp table DML statement will either entirely pass or entirely fail without an explicit transaction coded in your stored procedure. The consistency of each temp table (its constraints) are guaranteed without explicitly defining a begin/end transaction in your loop.
    I'm assuming you're talking about a stored procedure whose net effect is read-only : meaning its sole purpose is to return data to the application and it doesn't modify data in any database other than tempdb (commonly referred to as the "fast lane reader pattern"). Adding in a begin/end trans will not affect performance for this type of stored procedure that is read-only with only temp table DML work inside of it.
    In my opinion, explicitly coding a being/end transaction in a read-only stored procedure is not recommended and rather confusing. As described above, temp tables are unique in their inherent support of ACID properties and explicitly coding begin/end transaction for temp tables is unnecessary. That being said, I don’t think there will be any major adverse effect.
    The only major performance impact I’m aware of from coding explicit begin/end transactions in code is that excessive blocking can result if the transactions are long-lived. Long living transactions can kill application scalability. Long running transactions can also take a long time to roll back and can also adversely impact the start-up time of SQL Server in the event of a shutdown. But when transactions are successful, a begin/end transaction results in nothing more than markers being placed in the transaction log delineating the beginning and end of the transaction. I don’t think there’s a performance impact in this regard.
  4. atulgoswami New Member

    michaellutz,Thank you very much for such a wonderful explanation and i got what exactly i want. Thanks again
  5. Adriaan New Member

    I agree, excellent explanation from Michael.
    There is another issue with creating and filling temp tables which has nothing to do with transactions, but still worth a mention at this point:
    If you use this syntax:
    SELECT columns
    INTO #tmp_table
    FROM source_table

    ... you're placing locks on the source_table. So instead, use this:
    CREATE TABLE #tmp_table (column_definitions)
    INSERT INTO #tmp_table (columns)
    SELECT columns
    FROM source_table

  6. FrankKalis Moderator

  7. Adriaan New Member

    Strange, that discusses locks in tempdb.
    On this here SSP site, the point has always been about locks on the source table. IIRC it's also an item in the Performance Audit articles.
  8. FrankKalis Moderator

    At the bottom of my link Remus mentions something about locks on the SELECTed table and when I think about it, I would also expect a SELECT INTO statement to not cause other locks than any other SELECT statements. Or maybe the effects have just been mitigated since SQL Server 2000.
  9. Adriaan New Member

    Well, I said "locks on the source table" --- this is from one of Brad McGehee's valued tips:

    http://www.sql-server-performance.com/tips/t_sql_select_p1.aspx
  10. FrankKalis Moderator

    [quote user="Adriaan"]
    Well, I said "locks on the source table" --- this is from one of Brad McGehee's valued tips:

    http://www.sql-server-performance.com/tips/t_sql_select_p1.aspx
    [/quote]
    Yes, but system tables in tempdb, that is. And exactly this effects have been mitigated from SQL Server 2000 onwards.
  11. Adriaan New Member

    Hm, I'll take your word for it then.
    What about locks on the rows being read from the source tables? (Unless of course you add some explicit no locks option.)
  12. moh_hassan20 New Member

    [quote user="Adriaan"]SELECT columns
    INTO #tmp_table
    FROM source_table[/quote]
    I have a question:
    What about locks in the source table using that syntax:
    SELECT columns
    INTO #tmp_table
    FROM ( select columns from source_table) st [;)]

  13. preethi Member

    <p>[quote user="moh_hassan20"]</p><p>[quote user="Adriaan"]SELECT <i>columns</i><br>INTO #tmp_table<br>FROM source_table[/quote]&nbsp;</p><p>I have a question: <br></p><p>What about locks in the source table using that syntax:</p><p>SELECT <i>columns</i><br>INTO #tmp_table<br><b>FROM&nbsp; ( select columns from source_table) st</b> <img src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif" mce_src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif" alt="Wink"></p><p>&nbsp;</p><p>[/quote]&nbsp;</p><p>In both cases, there will be shared lock on source_table.&nbsp; I have seen (in SQL 2000, not sure whether this behavior exists in SQL 2005 or later) in the second query, some times it places an exclusive lock.&nbsp; I have no clue on why it happens. </p><p>To prevent shared lock you need to either keep the transaction isolation level to read uncommitted or pass the with nolock query optimizer hint<br></p><p>&nbsp;</p>

Share This Page