Question
TD
CA
Last activity: 16 Nov 2020 4:18 EST
How to insert 1000s of record in database table minimizing the database operation?
Hi,
I have a pageList that has 1000s of page that needs to be insert in database. The only way i know is to iterate through the list and do obj-save for every page. But to mu knowledge that will impact the performance as there will be 1000s of database operations.
How can i do it without making 1000s of database operation to minimize the impact. Basically is there any way to bulk insert all of the records?
TD
CA
Thanks Marc,
Yes this operation will be performed during the night when users are offline. Can i still use Obj-Save method by not checking WriteNow checkbox? That way instead of commiting every single instance i can deferred it by commiting at the end.
Would this sound the correct way of doing this or there are any other efficient way to perform this bulk database operation?
Thanks
TD
CA
Marc, We are not using prconfig.xml for database configuration instead we have configured database through server data sources. In this scenario how can i use this feature. Please help!
Batching updates and inserts for best performance
If your system accesses the PegaRULES database using an updated JDBC 2.0 driver, enabling an optional driver capability for batched updates may improve database performance.This benefit also applies to external databases when they are accessed as external classes.
Not all JDBC 2.0 drivers correctly implement this capability. As of May 2007, Pegasystems has verified this capability with specific drivers including UDB 9.1 and Z/OS DB2. Other vendors are reportedly addressing compliance. Contact Global Customer Support for the latest information.
To allow PRPC to use SQL prepared statements to batch updates for improved performance on row inserts, updates, and deletes for a single SQL database that is configured through the prconfig.xml file, add the following line:
<env name="database\databases\zzzzzz\batchUpdates" value="nn" />
Marc, We are not using prconfig.xml for database configuration instead we have configured database through server data sources. In this scenario how can i use this feature. Please help!
Batching updates and inserts for best performance
If your system accesses the PegaRULES database using an updated JDBC 2.0 driver, enabling an optional driver capability for batched updates may improve database performance.This benefit also applies to external databases when they are accessed as external classes.
Not all JDBC 2.0 drivers correctly implement this capability. As of May 2007, Pegasystems has verified this capability with specific drivers including UDB 9.1 and Z/OS DB2. Other vendors are reportedly addressing compliance. Contact Global Customer Support for the latest information.
To allow PRPC to use SQL prepared statements to batch updates for improved performance on row inserts, updates, and deletes for a single SQL database that is configured through the prconfig.xml file, add the following line:
<env name="database\databases\zzzzzz\batchUpdates" value="nn" />
where nn is a positive integer setting a maximum count for the number of rows to be batched and zzzzzz is the name of the datasource. (You can't use this option when the database is configured on the Database form or through application server datasources.)
After updating the file, redeploy and restart.
As an alternative to the prconfig.xml file, you can use Dynamic System Settings to configure your application.
See How to create or update a prconfig setting.
Typically, set a value between 5 and 30; vendor documentation and experimentation may provide guidance for a good value. Setting this value to 0 or 1 is equivalent to no batching.
When enabled, this feature applies to all processing — including any Commit operations of two or more objects — but may improve the performance of bulk processing by as much as 20 percent.
Pegasystems Inc.
AU
If the operation shall be performed when the users are offline, what is the performance concern you have?
Obj-Save of each item with WriteNow unticked is most guardrail compliant approach, so long as the Activity is running as part of something that Pega is running that issues a "Commit", such as flow processing or Standard Agent / Queue Processors that use auto queue management.
The result is all Obj-Save's are deferred until the Commit is issued, when all are sent to the database in sequence as part of the one transaction. If one save fails, then the whole batch rolls back. If this is the most desirable behavior then proceed with this approach. Performance-wise, this means N inserts/updates and 1 commit operation, all done in a contigious block. Use the Tracer with "DB Query" event turned on to see for yourself.
If there are the possibility of failures, and you want the successful ones to save and to perform exception handling on those that fail, use Obj-Save with WriteNow which issues a database Commit on each Obj-Save (again the Tracer can show this behavior). Performance-wise, this means N inserts/updates and a maximum of N commits, so is more "work" for the database, but if done in background processing whilst users are not using the system then this is a good use of idle capacity.
If the operation shall be performed when the users are offline, what is the performance concern you have?
Obj-Save of each item with WriteNow unticked is most guardrail compliant approach, so long as the Activity is running as part of something that Pega is running that issues a "Commit", such as flow processing or Standard Agent / Queue Processors that use auto queue management.
The result is all Obj-Save's are deferred until the Commit is issued, when all are sent to the database in sequence as part of the one transaction. If one save fails, then the whole batch rolls back. If this is the most desirable behavior then proceed with this approach. Performance-wise, this means N inserts/updates and 1 commit operation, all done in a contigious block. Use the Tracer with "DB Query" event turned on to see for yourself.
If there are the possibility of failures, and you want the successful ones to save and to perform exception handling on those that fail, use Obj-Save with WriteNow which issues a database Commit on each Obj-Save (again the Tracer can show this behavior). Performance-wise, this means N inserts/updates and a maximum of N commits, so is more "work" for the database, but if done in background processing whilst users are not using the system then this is a good use of idle capacity.
If you genuinely mean 1000s and not millions then your database is likely not going to be put under too much strain, but this will depend on how much data is involved and how big the tables are you are saving to.
As @MarcLasserre_GCS suggests, understand the exception handling for both scenarios. If you wind up using WriteNow, check StepStatusFail on the Jump configuration of the Obj-Save step. If you are using deferred saves and Pega is controlling the Commit (that is, on completion of the Flow Action, Standard Agent job, Queue Processor job), understand what a failure looks like ('Broken' standard agent job? ProblemFlow?) and ensure Operations staff are enabled on what to look for and how to resolve.