Question

1
Replies
2120
Views
Close popover
Seri Charoensri (chars1)
PEGA
Sr. System Architect
Pegasystems Inc.
AU
chars1 Member since 2015 46 posts
PEGA
Posted: May 17, 2016
Last activity: May 18, 2016
Closed
Solved

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://pdn.pega.com/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://pdn.pega.com/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

Data Integration
Moderation Team has archived post,
Close popover This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Question, please write a new Question.