On Monday 06 October 2008, toly wrote:
I can answer about Sybase: update is delete/insert, and optimizer will do it more effectively the executing 2 different transaction.
Hi Daniel,
i think that doing a update is faster then doing two queries. The core need to construct another SQL string, the DB need to be parse this and so on.. It probably depend on the DB type, and exact configuration, but i doubt the difference will be that relevant. Doing a delete and insert in two steps is also different from the update that you have one small interval of inconsisteny where the old record has been deleted, and the new one not yet created (if you don't use transactions).
Aparently also for postgres an UPDATE is also the same as and DELETE/INSERT. [1], which means its faster here too.
Cheers,
Henning
[1] http://archives.postgresql.org/pgsql-performance/2005-12/msg00589.php