SQL Server Performance

Weird Transaction Behavior

Discussion in 'SQL Server 2005 General Developer Questions' started by Righteousman, May 24, 2007.

  1. Righteousman New Member

    ok, I have some weird behaviour on our new 64 bit box.

    When I do this:

    WHILE @Counter < 5000
    BEGIN
    INSERT INTO Table VALUES(...)
    SET @Counter = @Counter + 1
    END

    It takes forever. On any of our other boxes it's about 1 second.

    Now when I change the above statement to read:

    BEGIN TRANSACTION

    WHILE @Counter < 5000
    BEGIN
    INSERT INTO Table VALUES(...)
    SET @Counter = @Counter + 1
    END

    COMMIT TRANSACTION

    It's as fast as any other machine.

    So I'm guessing that the transaction ensures that only one commit happens, whereas the first query causes each INSERT to make a commit, slowing things down badly.

    My only problem is...why doesn't this happen on our other servers? Is there a 64 bit setting that might cause this? Or is it a database setting that happens to be different?

    Anyone have any insight into this?

    Any help would be greatly appreciated!
  2. satya Moderator

    Have you seen any blocking when BEGIN ..END not used?
    You might try with TRY....CATCH to see what is happening,http://msdn2.microsoft.com/en-us/library/ms175976.aspx fyi.

    BEGIN and END define a series of TSQL statements that execute together. If the BEGIN...END block were not included, both ROLLBACK TRANSACTION statements would execute

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. Adriaan New Member

    Any reason why you're using a cursor instead of

    INSERT INTO target_table (...)
    SELECT ... FROM source_table

    ?
  4. Righteousman New Member

    quote:Originally posted by Adriaan

    Any reason why you're using a cursor instead of

    INSERT INTO target_table (...)
    SELECT ... FROM source_table

    ?

    Well, it's just a test script to see how quickly 5000 inserts can occur. My concern isn't the logic of the statement, it's the fact that it behaves differently between a 32 bit instance and a 64 bit instance. It's not a production query.

    I haven't seen any blocking because I'm the only person on the box. The other interesting thing is that when I run the query in tempdb it's fast as well, but in any other database it's slow without the begin transaction...commmit transaction. When I use perfmon to see what's happening, the log writes max out the disk for about 20 seconds while the disk the data file is on has virtually no activity...so it's definteily something to do with the way things are being logged.

Share This Page