Hi Jan, I agree, it would be useful. We also use some insertOrUpdate mechanism, currently implemented as pure SQL, but it would be great if we can do it with Empire. 2 remarks:
1. With MySql we use REPLACE INTO instead ON DUPLICATE..., maybe it's the same. 2. It's also possible with MSSQL using the MERGE command. Regards, Ivan <[email protected]> ezt írta (időpont: 2016. aug. 8., H, 13:01): > 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 > >
