How do I do Batch Insert
Hi there,
How do I do batch operation for large tables (with ACID) and multiple tables . We have seen deadlock on a table with 1m+ rows
https://community.pega.com/support/support-articles/upgrade-pega-717-causes-db-deadlock-exceptions
Questions:
1. Is there a way to do batching as to increase DB operation performance and control transaction conn.setAutoCommit(false) and commit;?
Would Obj-Save (deferred save) batch the insert/update and send the query to DB in a batch with one commit call? Would the Obj-Save semantically perform the addBatch() and executeBatch()?
2. RDB-xxx/Connect SQL does not do Transaction Control which is not ideal for multiple table insert, is there alternative to insert into multiple table within a transaction?
With large table, RDB-xxx/Connect SQL, would this incur excessive commit per RDB-save /per row.?
How do I control (BeingTrans/EndTrans or RollbackTrans) if I need to insert into multiple tables?
Hi there,
How do I do batch operation for large tables (with ACID) and multiple tables . We have seen deadlock on a table with 1m+ rows
https://community.pega.com/support/support-articles/upgrade-pega-717-causes-db-deadlock-exceptions
Questions:
1. Is there a way to do batching as to increase DB operation performance and control transaction conn.setAutoCommit(false) and commit;?
Would Obj-Save (deferred save) batch the insert/update and send the query to DB in a batch with one commit call? Would the Obj-Save semantically perform the addBatch() and executeBatch()?
2. RDB-xxx/Connect SQL does not do Transaction Control which is not ideal for multiple table insert, is there alternative to insert into multiple table within a transaction?
With large table, RDB-xxx/Connect SQL, would this incur excessive commit per RDB-save /per row.?
How do I control (BeingTrans/EndTrans or RollbackTrans) if I need to insert into multiple tables?
3. What is the implication for the suggestion in https://community.pega.com/support/support-articles/upgrade-pega-717-causes-db-deadlock-exceptions to set prconfig.xml
<env name="classmap/usemergestatement" value="false" />
<env name="database/batchupdates" value="-1" />
NOTE: the semantically there are two completely different DML operation. ie. MERGE v.s. INSERT/UPDATE.
I notice that when set usemergestatement to false, it will do 2 DML ie. INSERT, followed by UPDATE.
Why would it needs to do INSERT, followd by UPDATE DML?
Why would the first INSERT does not cause SQL Exception Violation of PRIMARY KEY ??? if the record exists
Regards
Seri