Support Article
OBJ-SAVE performance degrades
SA-99760
Summary
In PRPC 6.3, on executing the Obj-Save method, an out-of-the-box feature generates an Insert statement. However, in Pega Platform 8.2.2, while executing the Obj-Save method, the out-of-the-box feature generates a Merge statement. Hence, the performance of the tables with high volume data, degrades.
For example,
Performance using Insert Elapsed Time – approximately 0.0010 (seconds)
Performance using Merge Elapsed Time – approximately 30.1510 (seconds)
Error Messages
Not Applicable
Steps to Reproduce
Execute any out-of-the-box activity which has the Obj-Save method.
Root Cause
A defect or configuration issue in the operating environment.
Since Pega Platform 7.1.8, the Obj-Save functionality is to use Merge as long as the database driver version in use supports it. Prior to this, the Obj-Save method used a Two-step approach of first attempting an Insert, and then use Update if the Insert returned a Constraint error.
Resolution
Here’s the explanation for the reported behavior:
The Merge function must perform better than the Insert or Update function. However, this depends on the use case. If the user has control over the calls made, perform a RDB-Save instead of an Obj-Save to perform the Insert with the table while still retaining the Merge for other tables.
To disable Merge entirely in the application, use the below, prconfig.xml:
<env name="classmap/usemergestatement" value="false" />
Set the below Dynamic System Settings (DSS):
Owing Ruleset: Pega-Engine
Setting purpose: prconfig/classmap/usemergestatement/default
Value: false
Alternatively, when the user is only inserting to a table, verify if the 'OnlyIfNew' option for Obj-Save provides what is required for this class. Use Merge for other classes in the application.
OnlyIfNew - Select to cause only an insert operation, not insert or update. If the activity is operating in batch mode, it does an insert instead of a defaulted update. For more information, refer to: https://community.pega.com/sites/default/files/help_v82/procomhelpmain.htm?portalThreadName=STANDARD&portalName=Developer#methods/obj-save/obj-save.htm
Published April 1, 2020 - Updated December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.