Demystifying Pega Blob and UDF: Unraveling the Core Concepts-Part 2

Continuation from the previous part; if you haven't already, please refer to part 1 available at Demystifying Pega Blob and UDF: Unraveling the Core Concepts-Part 1 before proceeding with this article.

What is optimization?

Optimization involves exposing a property as a dedicated database column, eliminating the necessity for data extraction from Blob through UDF and substantially enhancing reporting performance. From a technical standpoint, it may marginally impact insert and update operations, hence it's advisable to optimize only those properties essential for Report Definition (avoid optimizing unnecessary ones). Additionally, it's important to note that properties in classes mapped to the pr_others table cannot be optimized.

Optimization encompasses two patterns – Single Value property and Page List property. When optimizing a Single Value property, the column is seamlessly added to the existing table. On the other hand, if you optimize a property within a Page List property, a new Index table is generated, and the column is incorporated therein. Refer to the sample image below for illustration.

During the development phase, optimization is commonly executed using the optimization wizard within the Dev Studio. In a production environment, specifically for Pega 8 and later versions, the optimization process is seamlessly handled by importing a R-A-P (Rule-Application-Product) file. Further details are elaborated in the subsequent points below.

How to optimize a property in Dev?

1.Optimization wizard

Launching the optimization wizard in Dev Studio is as simple as right-clicking on the property. The following example illustrates the process for a Single Value property, but the steps remain consistent for a Page List property as well.

2.Prerequisites for using the optimization wizard

Certain prerequisites must be met for developers to utilize the optimization wizard in Dev Studio.

How to Optimize a property in Prod?

Stay tuned for upcoming articles on this topic. Regularly check this space for updates and new content.

Declare Index

When optimizing properties within a Page List property, the system generates various artifacts. For instance, in the earlier PurchaseRequest sample application, the following objects were created:

1. Declare Index rule: "Index_ItemList"

2. Index class: "Index-MyCo-Data-Item"

3. Property rules in the Index class: "ID," "Name," and "Price"

4. Physical table in the database: "PR_INDEX_MYCO_DATA_ITEM"

These artifacts collectively facilitate the optimization process and enhance data retrieval performance.

How to unoptimize a property?

If you have mistakenly optimized a property that should not have been optimized, or if the Report Definition requirement no longer exists, and the property does not need optimization, you might consider reverting the optimization for cleanup purposes.

1.Reversing optimization in a development environment

1.1 Single Value property

The most straightforward method is to directly remove the column from the Database Management System (DBMS) and then restart the system or resave the Data-Admin-DB-Table instance. This technique is applicable across all versions. It's important to note that without restarting the system, you won't be able to resave the work object (resulting in a FATAL pyCommitError), and re-optimizing the same property is also not possible.

1.2 Page List property

Handling the removal of a Page List property is more involved than a Single Value property due to the inclusion of additional elements. If you want to eliminate a specific property while keeping others, you can follow a process similar to that of a Single Value property. However, if the goal is to remove all properties and the entire Declare Index, a comprehensive cleanup is required. This includes deleting all related elements such as properties in the Index-MyCo-Data-Item class, the Index-MyCo-Data-Item class itself (ensuring all records are deleted first), the physical database table, and the Declare Index rule. Importantly, a system restart is not needed in this case. Deleting the Index-MyCo-Data-Item class automatically removes the Data-Admin-DB-Table instance, notifying the engine of the changes. As a result, you can promptly resave the work object or re-optimize the same Page List property.

2.1.Reversing optimization in a production environment

When dealing with an already optimized property in a production environment with active end-user usage, the initial consideration revolves around assessing the associated risks. Two primary options are available: either proceed with unoptimizing the property or maintain the current state. Retaining the optimization may result in potentially slower insert and update operations. While the impact is likely negligible for a single or a few properties, if numerous properties are affected, it might be worthwhile to address and resolve the situation to avoid potential performance issues.

When it comes to unoptimization, importing the Data-Admin-DB-Table instance from Dev to Prod effectively transfers the optimization state. However, the reverse process of unoptimization, where a column is dropped in Dev, a product file is created, and then imported into Prod, does not prompt PRPC to detect table structure changes and execute DDL (DROP COLUMN) to synchronize it. In the case of a Single Value property, the recommended approach aligns with Dev practices – drop the column directly from the Database Management System (DBMS) and restart the system.

For a Page List property, removing all properties and the entire Declare Index presents a challenge. Physical deletion of these rules is not feasible because Declare Index and property rules are instances that are already locked in Prod. Instead, the workaround involves withdrawing the Declare Index rule (and the property rule) in Dev and then importing the R-A-P to Prod. It is crucial to ensure that new records are no longer inserted into the Index table following this process.

How to eliminate unoptimized Report Definitions?

Now that we comprehend the importance of optimizing all properties in Report Definitions, the challenge lies in identifying whether a property is optimized or not, as the property rule form itself doesn't explicitly indicate its optimization status. To ensure that there are no unoptimized Report Definitions, a straightforward approach is to check Guardrails. Unoptimized properties trigger a Severe warning, appearing at the top, facilitating easy navigation to the problematic Report Definitions. In environments without User-Defined Functions (UDF) installed, these reports might fail at runtime. Therefore, it's crucial to confirm the absence of such reports in the environment.

Concluding the article here!!!

Happy Learning :) :)