Hello,
I am trying to get out you knowledge as DBA ... in some discussions in the past was brought out that in some cases doing a delete on primary key followed by a pure insert might be faster than one update. It might be dependent of DB server type and perhaps the question would have better answer in appropriate db-specific mailing lists, but I want to see if some of you has more experience/insights about this and is willing to share or got into same dilemma.
Thanks, Daniel
Hello Daniel,
I can answer about Sybase: update is delete/insert, and optimizer will do it more effectively the executing 2 different transaction.
Toly
miconda wrote:
Hello,
I am trying to get out you knowledge as DBA ... in some discussions in the past was brought out that in some cases doing a delete on primary key followed by a pure insert might be faster than one update. It might be dependent of DB server type and perhaps the question would have better answer in appropriate db-specific mailing lists, but I want to see if some of you has more experience/insights about this and is willing to share or got into same dilemma.
Thanks, Daniel
-- Daniel-Constantin Mierla http://www.asipto.com
Users mailing list Users@lists.kamailio.org http://lists.kamailio.org/cgi-bin/mailman/listinfo/users
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
Hello,
On 10/06/08 12:12, Henning Westerholt wrote:
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.
ok. I was looking to use prepare statements or a stored procedure. However, seems some db servers are doing it in this way, so probably there are internal optimizations.
Thanks, Daniel
Cheers,
Henning
[1] http://archives.postgresql.org/pgsql-performance/2005-12/msg00589.php
Users mailing list Users@lists.kamailio.org http://lists.kamailio.org/cgi-bin/mailman/listinfo/users
Hello,
On 10/06/08 06:20, toly wrote:
Hello Daniel,
I can answer about Sybase: update is delete/insert, and optimizer will do it more effectively the executing 2 different transaction.
good to know that some db systems are doing this way. Thanks!
Daniel
Toly
miconda wrote:
Hello,
I am trying to get out you knowledge as DBA ... in some discussions in the past was brought out that in some cases doing a delete on primary key followed by a pure insert might be faster than one update. It might be dependent of DB server type and perhaps the question would have better answer in appropriate db-specific mailing lists, but I want to see if some of you has more experience/insights about this and is willing to share or got into same dilemma.
Thanks, Daniel
-- Daniel-Constantin Mierla http://www.asipto.com
Users mailing list Users@lists.kamailio.org http://lists.kamailio.org/cgi-bin/mailman/listinfo/users
-
Daniel-Constantin Mierla -
Henning Westerholt -
toly