Optimizing database properties
You can improve reporting and search performance in your Pega application by optimizing (exposing) Single Value properties as distinct columns.
By default, your Pega database stores property values in a Storage Stream column called pzPvStream, using a Binary Large Object (BLOB) database type. You cannot retrieve the data for these properties without first decompressing the column data, which can significantly slow down data retrieval and processing in other operations.
Optimization tools
Use property optimization to enhance your application performance by avoiding retrieving property data directly from the BLOB. You can optimize properties in various ways, depending on your application structure and data retrieval requirements. To optimize (expose) these properties after the property has values, use the Pega-provided optimization tool or tools described in the following sections that best fits your application.
Property Optimization tool (recommended)
In Dev Studio, use the Property Optimization tool in the Application Explorer. This tool exposes and queues a background job, which populates the newly exposed columns as a single step. For more information, see Optimizing properties from the user interface.
Modify Schema wizard
To expose properties but not schedule a background job to populate the column with values from the BLOB, use the Modify Schema wizard in Dev Studio. This tool exposes the column without immediately populating it with values. After exposing the column, populate it using one of the Column Populator tools.
You can also view schema, increase the size of columns, and generate an SQL text file that defines a table, that you can later use to modify the schema. Use this wizard to review the current database table structure, understand class-to-database table mapping, and provide additional context. For more information, see Schema modification using the Modify Schema wizard.
Create a column using your SQL client
Pega supports using your SQL client to create a new column for your data that you need to expose. Before you do so, check the specific guidance for manual column creation. For more information, see Adding columns to your database schema.Use a Column Populator tool
After exposing a column with the Modify Schema wizard or manually creating the column, populate the new database column for existing instances using one of the command-line Column Populator tools:
- Column Populator REST service utility – This command-line utility, which requires a running instance of your application, populates columns by using a REST service to communicate with the system. For more information, see Populating properties by submitting a request to an active instance.
- Column Populator database utility – This command-line utility populates columns by communicating directly with the database. Unlike the REST service utility, you can use the command line utility when your application is not running. For more information, see Populating properties by using a direct connection to the database.
Other property optimization details
The Column Inclusion field on the Property form indicates the opinion of the developer who created or updated the property as to whether the property might need to be exposed. This value does not affect any runtime behavior of the property. For more information, see Property form — Completing the Advanced tab.
Exposed properties that have been fully implemented are shown on the Database Class Mappings tab. For more information, see Database Class Mappings tab and Adding columns to your database schema.
- Optimize a property from the user interface
Use the Property Optimization tool to improve data retrieval performance from your Pega database. This tool creates a dedicated database column for a selected property and starts a background process to populate the values for the new column from existing BLOB values. This eliminates the need to read the BLOB when fetching property data.
- Adding columns to your database schema
Pega supports the manual addition of columns to expose additional properties in your database schema for optimized reporting and search performance.
- Removing the optimized status from a property
Deoptimize a property that you previously optimized to completely clear the optimized property from a database schema. A deleted optimized property falsely retains its optimized status through its property-optimization log associations upon its recreation, and Pega Platform cannot optimize the property again. Deoptimize the property to completely clear its status from the schema and table in order to optimize the property again.
- Modifying a schema by using the Modify Schema wizard
You can view and modify the PegaRULES database schema by using the Modify Schema Wizard. You can determine which concrete classes are mapped to each database table, which properties in each class are exposed as distinct columns, and which Single Value properties in each class are not exposed but are contained in the Storage Stream (BLOB) column.
- Populating properties by using the Column Populator tool
After adding a new column to a table schema, you can populate values for that new column by using the Database Column Populator or the service-enabled Column Populator command-line tools.
- Optimizing properties by using the Select values gadget
To improve value retrieval performance, you can add and remove optimizations for fields in the Select values gadget on report definitions and the Report Editor. When you add or edit a filter in a report definition rule, you use the Select values gadget to select a value that the property of a filter is equal to. If the property is equal to many values, populating the Select values gadget can result in poor performance. By optimizing the property of the filter, the number of values that are queried is reduced, resulting in improved retrieval
- Using declarative indexes
Create a Declare Index rule to define criteria under which Pega Platform automatically maintains index instances for faster access. An index can improve search and reporting access for properties that cannot be exposed as database columns because they are embedded within an aggregate property. Creating a Declare Index rule manually is an alternative to the integrated approach used by the Property Optimization tool.
Previous topic Updating PegaRULES after external schema changes Next topic Optimize a property from the user interface