Rollback or Delete? Which is nore efficient?

Question

Rollback or Delete? Which is nore efficient?

Annie-469370

Grasshopper

Points: 18
More actions
September 2, 2010 at 5:56 pm

#387721

One of my colleague wrote a Insert trigger on Table A. It calculates value for some of the fields in Table A.
We only want the calulcated result, showing to the user, then the user can decide if he/she wants to keep it.
Table A also has a Delete trigger.
On of the stored procedure does something like :
--------------------------------------------------------
Begin Transation
Insert into Table A (ID, A, B, etc..) Values (1, 'A', 'B', ...)
-- trigger the insert trigger and get updated values
Select * from A where ID = 1
RollBack -- so the record is removed
--------------------------------------------------------
My question is: Is it more efficient to do a "Delete FROM A Where ID = 1" or to do a Rollback in this case?
Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Where, or how, in this proc is the user actually going to be able to respond to 'liking' the new calculated value?

Transactions that require a user-initiated action to continue to commit/rollback status have the possibility of completely hosing your system. I would step away from this process and rethink the methodology, if you're initiating/completing the transaction from web/app code.

You would probably be best off dropping this to a proc for calculation, returning that result to whatever front end the user is actually using, and then when they commit there do the write to the actual table without worrying about a rollback.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Annie-469370 Grasshopper Points: 18 More actions · Answer 2

Hi,

Thanks for the reply. This stored procedure only does the calculation part. The result is then showing to the user on a web page, then when user clicks on save, another stored procedure would be called to really save the data.

I would just like to know if the Transaction + Rollback approach is more efficient then doing an insert +delete in this case. And which way would be better and why.

Thanks,

Annie

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 3

The insert/calculation trigger/rollback will be more efficient (than insert/calculation trigger/commit/delete/delete trigger/commit), but it's really only going to matter if the server is under a severe load, or the delete trigger is rather inefficient. As long as this calculation phase truly does keep the transaction time very small, you shouldn't have any trouble.

However, I would question why you need to persist a row into the table for the calculation? It just tingles my spidey-sense - permanent storage should be permanent. If the calculation depends on all other rows in the table, it doesn't make sense to store it, calculate, and then present it to the user. What happens if the user then saves, but the calculated value would change if it was recalculated at the saving point in time?

The most likely reason I can think of is that the calculation code is written into the trigger, and it you don't want to move or duplicate the logic.

Annie-469370 Grasshopper Points: 18 More actions · Answer 4

Ah ha... very clever! Yes, the developer wrote the insert trigger to avoid duplication of code else where and also to avoid writing cursor.

Thank you so much!