Issue with Merge Into Statement
Hi All,
PDC has raised an alert about slow SQL on a table. Investigation shows that this table is a custom data table with class Keys using option "Automatically generate a unique ID for records of this type" and so pyGUID becomes the primary key. Checked the table and sure enough this column is defined as the primary key and has an index. All good, except the merge statement doesn't use this primary key but pzInskey which doesn't have any index.
Is this a bug when the class and subsequent table was created by Pega?
What kind of impact is this likely to have, should we create the secondary index against pzInskey to help with the merge statement?
We are running Oracle 19c BTW.
Thanks Craig
Accepted Solution
Virgin Media
GB
OK adding the secondary unique index against pzInsKey has fixed the issue with the alert.
Will need to be mindful of this switching of database in future.
Pegasystems Inc.
GB
@CraigA52 It appears that the issue arises because the merge statement is using pzInsKey which does not have an index instead of the primary key pyGUID. This could indeed be a bug or an oversight when the class and subsequent table were created by Pega. The lack of an index on pzInsKey can lead to slow SQL performance as indicated by the PDC alert. Creating a secondary index on pzInsKey would likely help improve the performance of the merge statement by allowing the database to quickly locate the relevant records. However it is important to consider the potential impact on database performance and maintenance before making this change.
⚠ This is a GenAI-powered tool. All generated answers require validation against the provided references.
Virgin Media
GB
@MarijeSchillern Thanks for the response. Based on the second post, seems this is a bug. Keys on class as mentioned is using pyGUID to generate unique id but on database it is also the primary key with 3 constraints, 1 for primary key and 2 others for Check pzinskey is not null and pyguid is not null.
Looks like Pega didn't generate this correctly during creation.
Seems I'll need to add an index to pzinskey to fix this issue.
Thanks Craig
Pegasystems Inc.
GB
@CraigA52 please log a ticket with GCS via the MSP and provide the INC id here if you believe you can replicate this as an undocumented product bug.
Virgin Media
GB
@MarijeSchillern A little investigation with one of our developers. seems the data class database table was originally associated with CustomerDATA when created and later switched to PegaDATA.
This could explain why the primary key and the constraints appear to be incorrect.
Thanks Craig
Accepted Solution
Virgin Media
GB
OK adding the secondary unique index against pzInsKey has fixed the issue with the alert.
Will need to be mindful of this switching of database in future.