Hello,
I'm currently writing a few sync jobs. The naive approach was to run
an UPDATE first and an INSERT with same DBCommand object if nothing
was updated. This works but is slow.
I figured there is a INSERT ... ON DUPLICATE KEY UPDATE Syntax in
MySQL
(http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html)
which combines INSERT and UPDATE in one single statement. Using this
I'm able to perform a single batch satement instead of single
statements. In my first try it saved 70 % of running time.
My implementation is pretty simple, its just
public synchronized String getInsertOrUpdate()
{
StringBuilder buf = new StringBuilder(getInsert());
buf.append(" ON DUPLICATE KEY UPDATE ");
long context = CTX_NAME | CTX_VALUE;
addListExpr(buf, set, context, ", ");
return buf.toString();
}
in DBCommandMySQL, but to add this in my DBSQLScript I have to cast my
DBCommand to DBCommandMySQL every time.
I think we should add a method to do this in DBCommand with a default
implementation that throws a NotSupportedException. Same in DBDatabase
(executeInserOrUpdate(...). IMO this is a good idea because its
possible in Oracle (using MERGE) and at least Postgres
(https://wiki.postgresql.org/wiki/UPSERT) - and is very useful.
Opinions?
- jan
