[
https://issues.apache.org/jira/browse/OAK-4028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15970879#comment-15970879
]
Julian Reschke edited comment on OAK-4028 at 4/17/17 9:01 AM:
--------------------------------------------------------------
As far as I recall, we discussed this multiple times but did not see how this
helps with the actual DocumentStore API. But we can try once more. Furthermore,
AFAIU, it'll introduce a considerable amount of DB-specific code - something we
have avoided so far.
was (Author: reschke):
As far as I recall, we discussed this multiple times but did not see how this
helps with the actual DocumentStore API. But we can try once more.
> Use UPSERT (i.e. MERGE) for RDB to limit roundtrips on writing
> --------------------------------------------------------------
>
> Key: OAK-4028
> URL: https://issues.apache.org/jira/browse/OAK-4028
> Project: Jackrabbit Oak
> Issue Type: Improvement
> Components: documentmk, rdbmk
> Reporter: Philipp Suter
> Fix For: 1.8
>
>
> All databases offer some kind of UPSERT functionality. Either as MERGE [] or
> INSERT .. ON CONFLICT UPDATE. Such statements could be used to prevent an
> additional roundtrip in case an INSERT was not successful and needed an
> actual UPDATE.
> Problem: Distinguish if the DB actually did an INSERT or UPDATE for each
> statement. This is done differently for each supported database but possible,
> except most likely for Oracle:
> MySQL: http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
> - 1 for insert, 2 for update, 0 for update but unchanged
>
> MS SQL: https://msdn.microsoft.com/en-us/library/bb510625.aspx
> - See example 'C'
> - Use the OUTPUT variable $action
> - Needs some testing with JDBC
>
> Oracle: https://community.oracle.com/thread/3803485
> - Don't use merge
> DB2:
> http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.apsg/src/tpc/db2z_selectvaluesmerge.dita?lang=en
> - SELECT FROM MERGE
> - use a column to identify if insert or update was done, e.g. createdDate or
> similar (Modified?)
> - Needs some testing with JDBC
> Postgres: http://www.postgresql.org/docs/current/static/sql-insert.html
> - INSERT ... ON CONFLICT UPDATE ... RETURNING ...
> - Similar to DB2: Use a DB field to separate freshly inserted values from
> updated ones
> Ideally this is first tested with one database, e.g. MySQL due to the very
> convenient syntax in MySQL. If a difference in performance can be measured it
> could be implemented across the board.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
