Planning for property optimization
The values of all aggregate properties and all
Single Value
properties are stored in a BLOB column (the Storage Stream), usually in a compressed form. These properties cannot be retrieved for reports and can slow retrieval and processing in other operations. You can improve reporting and searching performance by optimizing, that is exposing,
Single Value
properties as distinct columns; however, doing so might slow insert and update operations, depending on your hardware and software.
You can expose a property even after the property has values. You then need to copy values from the BLOB into the new column by using one of the Column Populator tools.
Optimization tools
You can optimize properties in various ways, depending on whether you have a production environment or a development environment. Some tools expose but do not populate the new property with values; other tools expose and populate in one step.
Property Optimization tool
In development systems where you have only a few Single Value, top-level
properties to expose, use the Property Optimization tool in Dev Studio. This tool exposes and populates newly exposed columns as a
single step. For more information, see Optimizing properties from the user interface.
Modify Schema wizard
To expose properties in a production system, use the Modify Schema wizard in Dev Studio, which exposes the property without immediately populating it with values. After exposing the property, 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 can be used later 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.
Column Populator tool
After exposing a property with the Modify Schema wizard, populate the new database column using one of the command-line Column Populator tools.
- Service-enabled Column Populator Utility – This command-line utility populates columns by using a REST service to communicate with the system. It performs the locking that is needed to ensure data integrity in an active system. A running instance of Pega Platform is required. For more information, see Populating properties by submitting a request to an active instance.
- Database Column Populator Utility – This command-line utility populates columns by communicating directly with the database. Use this utility when all instances that share your database have been stopped. For more information, see Populating properties by using a direct connection to the database.
Planned and actual optimization
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 Planning for property optimization.
- Planning for property optimization
Changing the database schema to expose additional properties as columns requires planning. You should be a database administrator familiar with SQL, DDL, and database schemas with access to a database account and password that allows database restructuring (SQL ALTER TABLE grant).
- Optimizing properties from the user interface
You can expose a property as a database column, and populate the new column by extracting values from the storage stream (BLOB or pzPVStream ) column, using the Property Optimization tool in a development system.
- 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 Locks tab on the Class Group form Next topic Planning for property optimization