We have a report definition which is extracting lot of data from BLOB and sql has multiple pr_read_from_streams which is a very expensive operation on the database. This also also the sql which is running when our database went to recovery mode. We decided to optimize the properties. Now we have lakhs of records where the value needs to be populated in the optimized column. One approach is
1) login to developer portal in production environment and click optimize for reporting on the property. I am not sure if this is best practice. The column population job will automatically populate the data but it requires time to populate all the data and the problem is business user will see empty value during hours of operation.
2) Use sql query to populate column and run activity to populate column values. But again lakhs of recors are there and it will take time to populate and business will see empty values in the UI for those properties.
Let me know any better approach for this scenario and if above two approach is option then how to mitigate empty value problem
I think it will not be a good advice to directly optimize the property in production environment. Instead you should optimize the property in a STAGING env and move the DB-Table changes via RAP. And for column population, you should rely on migration activity. Anyways the above concerns are valid in both case(s), but generally, we need to provide these migration on free time, not on business days.
Thanks for the reply Pankaj, Do Pega provide any OOTB migration activity where we could just pass the class group name? Also if we perform Obj-Save then the updatedatetime and update operator may change which may not be acceptable by user? How do we address the problem