Configuring rules to improve report performance
A report definition (and the Obj-* methods) is just a query. Pega Platform™ constructs and optimizes a query based on parameters defined in the application rule. Then, Pega Platform delivers the query results to a clipboard page to either display to your end users or to use for other purposes, such as running an agent on the result set.
The same principles you use in tuning a database query can be applied to designing reports for performance. You can configure the report definition and Obj-* methods to use. You can apply techniques at the database level. You can choose to take an entirely different approach for gathering data, such as using Robotic automation or Elasticsearch.
The goal is to return data to your users in the most efficient way possible and have as little impact on other users.
The following techniques discuss best practices for configuring rules within the application.
Using data pages when possible
The best approach to optimizing your report is to avoid the use of report definition as a source for the user interface. Data pages are always efficient if the same data need to be reused. Design the refresh strategy to get data only when required. Use node scoped pages for static data whenever possible.
Paginating results
Paginating results allows you to return groups of data at a time. Returning big groups of records may make it difficult for users to find the information for which they are looking. For example, a report that returns 50 records at a time may be too much information for users to sift through. Select the Enable Paging option on the report definition and specify the page size.
For more information on how to configure paging in reports, see When and how to configure paging in reports.
Optimizing properties
If you expect to use a property in your selection criteria, optimize that property. Optimizing a property creates a column in the database table, which you can then index as needed.
For more information about optimizing properties, see the help topic Planning for property optimization
Using declare indexes
Declare indexes allow you to expose embedded page list data. For example, the application stores Work-Party instances in a pr_index_workparty table. This allows you to write a report definition that joins work object data to work party data instead of extracting the work party data from the pzpvstream column (the BLOB), which can be expensive.
For more information on how to use the declare index, see How to create a Declare Index rule for an embedded property with the Property Optimization tool.
Leveraging a reports database
To reduce the impact of queries on the production database, you can run reports against a reports database (also known as an alternate database). This approach offloads the demand of the production database to a replicated database.
For more information on using a reports database, see Setting up a reports database.
Avoiding outer joins
Selecting Include all rows on the Data Access tab of the report definition can be costly. This option causes the system to use an outer join for the report in which all instances of one of the classes are included in the report even if they have no matching instances in the other class. If possible, select Only include matching rows.
Using Report on descendant class instances
By default, Pega includes pxobjclass as the current class on the SQL query as "PC0"."pxobjclass" LIKE ?
This option is useful to include specific classes from the workpool or report created on the Framework layer. If Report on descendant class instances is selected, you can choose to include only the implementation class or all descendant classes (all framework and implementation classes).
The filter condition on pxobjclass changed to "PC0"."pxobjclass" IN (? , ? , ? , ? , ? , ? , ? , ? ), where ? depends upon the number of descendant classes. You can select a specific subset of descendant classes to include or exclude by adding a filter condition on .pxObjClass.
Note: Include all descendant classes applies to the applies to class. It does not apply to Join classes.
Caution: Performance may be poor unless all relevant database columns are indexed in all included class tables.
If you are having problems with your training, please review the Pega Academy Support FAQs.
Want to help us improve this content?