Question

1
Replies
2032
Views
chars1 Member since 2015 46 posts
PEGA
Posted: 4 years ago
Last activity: 4 years 5 months ago
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
Share this page LinkedIn