Update Vs Delete&insertion [message #658381] |
Mon, 12 December 2016 06:31 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi
If we have very huge data which one is efficient way to update on record based on certain business conditions.
So want to check instead of update if we go for delete and insert will be it good ?
Thanks
SaiPradyumn
|
|
|
|
Re: Update Vs Delete&insertion [message #658383 is a reply to message #658382] |
Mon, 12 December 2016 06:40 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
the problem is that your database has to backup the deleted data until the changes are deleted. the work to backup and alter a single column is MUCH less then backing up an entire row and then inserting a new one.
|
|
|
|
|
Re: Update Vs Delete&insertion [message #658389 is a reply to message #658388] |
Mon, 12 December 2016 07:32 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
It is not just about which is faster. It is logically different events. For example, replacing UPDATE with DELETE + INSERT might fail with "integrity constraint violated - child record found" if row we are deleting is parent row to some child table. We are also running DELETE and INSERT triggers (if there are any) instead of UPDATE triggers (if there are any). And even if there are no triggers now, we might add them later without realizing some piece of code switched UPDATE with DELETE + INSERT. Anyway, bad idea including performance.
SY.
|
|
|
|
|
Re: Update Vs Delete&insertion [message #658393 is a reply to message #658386] |
Mon, 12 December 2016 08:20 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
saipradyumn wrote on Mon, 12 December 2016 14:17
Thanks for your valuable information .
If I am updating table with huge data, is it better to check the count of the record before updating.
My intention is if the updated records count is equals to ZERO, don't go for DML operation by retrieving the records count from the table .
Will this improve the performance?
There will be no difference between counting the number of rows which will return 0 and updating 0 rows, the work is the same one, worse is counting as there is slight (but negligible) more work.
Counting and updating when there is some rows is worst because:
1/ You have to select twice the same rows so double the work
2/ You are not sure that you will have the same count of rows as someone can have updated some of them between the 2 statements.
[Updated on: Thu, 15 December 2016 01:04] Report message to a moderator
|
|
|
|