MSSQL Transaction Question

Say I have 200 - 300 update statements that get sent in a batch to SQL Server and all are encompassed in one transaction.

Would it be better to check for @@ERROR after each single update statement, after each N update statements (say 50), or after all the update statements?

I'm thinking that checking @@ERROR after every 50 update statements would be the most efficient, because waiting till the end would mean a larger transaction log that would have to be rolled back and doing it every single update statement would add more overhead.

Any thoughts on this?  What if you substituted 200 - 300 update statements with 800 - 1,000?

Wayne
Thursday, May 20, 2004

To me, the best plan would be to check after each statement, then you won't waste loads of resources creating changes that will ultimately get rolled back.

The overhead of checking for an error 1,000 times is a lot smaller than rolling back loads of updates.

If you can wait for the Yukon timescale (or use the beta), you can do a try...catch block, which'll give you the best of both worlds.

Steve Jones (UK)
Thursday, May 20, 2004

How often do you expect errors?

If the input is coming directly from the user and so may well cause errors then check more frequently.

If the changes are originated by the logic in your application and an error indicates that something is seriously wrong somewhere then it doesn't matter that dealing with the error is more costly.

Rob Walker
Thursday, May 20, 2004

I don't expect errors often, because the SQL is generated by my own process.  The error handling is there in the off chance that something goes horribly wrong like the disk running out of space or some.

I see the disk space error a lot with customers that don't have a *real* DBA working for them (most).

Wayne
Thursday, May 20, 2004

Error gets overwritten after each statement. If, for some reason, one update failed, and the next one succeeded, @@ERROR will be set back to 0.

Dennis Forbes
Thursday, May 20, 2004

With MS SQL, you *have* to check it after each statement, otherwise you might miss an error. It resets to 0 as each statement is executed. If you wait, and only check it every N statements, you might miss potential errors.

Sgt. Sausage
Thursday, May 20, 2004

Whoops, I have some code to go fix.  I thought I tested that!

Wayne
Thursday, May 20, 2004

This is exactly when an exception-throwing language would come in handy.

Data Miner
Thursday, May 20, 2004

T-SQL will (apparently) become an exception-throwing language with the next version.

Steve Jones (UK)
Friday, May 21, 2004

  Recent Topics

  Fog Creek Home