Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2009
    Posts
    37

    Unanswered: Commit in P-SQL script?

    Hi,
    When we are updating large tables, we use procedural SQL scripts in order to prevent massive transaction logs/buffers building up.
    For Db2/Oracle we have "conditional commits" every 1000 records, something like:

    Code:
    declare max_writes 1000;
    declare recordsWritten 0;
    
    loop:
    
    -- Update single table row
    update table...;
    
    recordsWritten++;
    
    if(recordsWritten == max_writes) {
        commit;
        recordsWritten = 0;
    }
    
    end loop
    commit;
    However, in any script I can find for SQLServer there is no such "conditional commit" - just one GO at the end of finishing with the cursor.


    Code:
    DECLARE ....;
    
    DECLARE DB_Cursor CURSOR FOR 
    select ......;
    
    BEGIN
        OPEN DB_Cursor;	
        FETCH DB_Cursor INTO ....;
    		
        WHILE @@FETCH_STATUS = 0      
            BEGIN
            -- Update single table row
            update table...;						
            FETCH DB_Cursor INTO ....;
        END
    
        CLOSE DB_Cursor;     		
        DEALLOCATE DB_Cursor; 	
    END
    GO
    Does SQLServer handle the transaction buffer internally so that we dont need to do it, like we do for DB2/Oracle. Is there some setting in SqlServer for this?

    thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You have to BEGIN TRANSACTION explicitly, then you COMMIT as usual.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2009
    Posts
    37
    Quote Originally Posted by n_i View Post
    You have to BEGIN TRANSACTION explicitly, then you COMMIT as usual.
    So does the above MSSQL example I have leave me open to problems with a high volume of update statements as I dont use transactions at all?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm not sure what you mean by that. You will still have the same amount of changes logged, whether you commit frequently or not. I don't know why you chose to commit every 1000 records so I can't comment on the potential outcome. The default SQL Server behaviour is to commit each statement automatically, so if you don't use an explicit transaction and run your code as a script, each update will be committed separately. If you create a stored procedure instead, I think it will run as a single transaction in its entirety.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Nov 2009
    Posts
    37
    My understanding (again for DB2/Ora as I said) is to do the updates in batches of 1000 so the amount of data to be committed isn't "huge" i.e. the general thinking being its not a good idea to have 1 million update statements being persisted by a single commit, its "better" ( to break them into blocks of 1000.

    If SQLServer commits after each update then we are fine....

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by FLANDERS View Post
    the general thinking being its not a good idea to have 1 million update statements being persisted by a single commit, its "better" ( to break them into blocks of 1000.
    You should talk to Tom Kyte about that...

    Transaction scope should be dictated by your business requirements, not by some "general thinking".
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Well, there is a lot to cover here. SQL Server and Oracle view transactions and locks differently. In SQL Server transactions are implicitly committed. Oracle waits for an explicit commit. If you run an update in SQL Server, you can not issue a subsequent rollback to undo it, unless you explicitly issued a BEGIN TRANSACTION statement. In your case, with a row by row cursor, each row would be committed separately.

    That said, SQL Server typically does not handle row by row operations very well, and set based operations are usually faster.

    Either way, the transaction log will have to be sized to handle all of the transactions (or be backed up partway through), if the database is set for FULL recovery mode. If it is set for SIMPLE recovery mode, it will only have to be sized for the largest concurrent transaction(s).

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MCrowley View Post
    If it is set for SIMPLE recovery mode, it will only have to be sized for the largest concurrent transaction(s).
    My experience is that this is not necessarily true in practice. I issue CHECKPOINT commands throughout any large import\ cleansing routines to force recycling the log for simple databases. IME a checkpoint is not issued by SQL Server until after the proc execution, which may be too late.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •