Configuring rules to improve report performance
A report definition (and its Obj-* methods) is just a query. Pega Platform™ constructs and optimizes queries 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 for other purposes, such as running an agent on the result set.
The same principles that you use in tuning a database query can be applied to designing reports for performance: you can configure which report definition and Obj-* methods to use; you can apply techniques at the database level, and you can choose to take an entirely different approach to 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 as possible on other users.
The following techniques discuss best practices for configuring rules within the application:
Use data pages whenever possible
The best approach to optimizing your report is to avoid the use of report definitions as a source for the user interface. Data pages are always efficient if the same data needs to be reused. Design the refresh strategy to get data only when required. Use node-scoped pages for static data whenever possible.
Paginate the results
Paginating results allows you to return groups of data at a time. Returning big groups of records can 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 the Report Definition Report Viewer tab article on the Pega documentation site.
Optimize 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 Planning for property optimization article on the Pega documentation site.
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 resource intensive.
For more information, see the Pega Community article about how to use the declare index to optimize embedded properties.
Use 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 demand on the production database to a replicated database.
For more information on using a reports database, see the Setting up a reports database article on the Pega documentation site.
Avoid outer joins
Selecting Include all rows on the Data Access tab of the report definition can also be resource intensive. 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, use the Only include matching rows option.
Report on descendant class instances
By default, Pega includes pxobjclass as the current class on the SQL query as "PC0"."pxobjclass" LIKE ?
This is useful for including specific classes from the workpool or report created on the Framework layer. If you use the Report on descendant class instances option, you can choose to include either only the implementation class or all descendant classes (all framework and implementation classes).
When you change the filter condition on .pxObjClass 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.
Th following figure shows the window, with only applicable descendant classes, instead of all descendant classes, when the filter criteria is defined on the .pxObjClass column. You open the window by clicking Select values on the column in which you want to define the filter criteria.
Note: The Include all descendant classes option applies to the Applies to class. It does not apply to Join classes.
Caution: Performance can suffer unless all relevant database columns are indexed in all included class tables.
Check your knowledge with the following interaction:
This Topic is available in the following Module:
If you are having problems with your training, please review the Pega Academy Support FAQs.
Want to help us improve this content?