Using reporting efficiently

=

Using reporting efficiently

Description	Instructions on using reporting efficiently
Version as of	8.5
Application	Pega Platform
Capability/Industry Area	Reporting

Getting started

This document provides best practices that instruct developers how to use Reporting functionality more efficiently while creating report definition rules.

Note: Before adopting any changes with respect to database, review the updates with your database administrators and finalized.

Choosing correct database index columns

As a best practice, create database table indexes that match as many of the queries in your workload as possible. That approach reduces the overall amount of indexes, and the load the indexes create on the database when inserting or updating data. It all depends on the uniqueness of the combination (cardinality) of the columns' values involved in the query 'WHERE' clause or filters in report definition.

Example

SELECT firstname, lastname

FROM students

WHERE street='XXX' AND city='XXX'

In the above example, the recommended choice is a multi-column index. From the above example multi-column index should be street AND city, but not city AND street.

Other important instructions:

• When choosing the indexes, consider the effect on insert, delete and update operations. More indexes means slower operations, because indexes require reorganization for each operation we perform on the table.
• Choose B-tree indexing for <,>,<>,=,!=,>=,<= operations.
• Choose hash based index only if !=,= operations are involved, because hash based index does not help with other comparison operations.
• Use Query analyzer / Explain statements to verify if the created indices are in use. If you require assistance, contact your Database Administrators for better understanding.

Choosing the right column for filter

Improving the performance of reporting depends on choosing the right column as a filter.

Example

Create a report definition that includes all resolved cases in your application. To achieve the goal, developers can choose one of the two options:

• pyStatusWork starts with “Resolve"
• pyResolvedTimeStamp set to not null

In the first option, where the "Starts with" filter is used, the database may not take full advantage of index even if it is present on pyStatusWork. As a result, the report definition may run slowly.

In the second option, where the pyResolvedTimeStamp is set to not null, the database query runs if a proper index is created. Index unique scan occurs, if null is considered as part of index.

Enabling pagination

Report definition provides the pagination option that allows the server to improve performance and memory use when loading the results. You can use pagination by accessing your data page, and checking the Enable pagination check box. Selecting this option improves data page memory use and performance for data pages that are used as the source of a grid. The pagination option propagates the settings from grid to datapage to report definition.

Note: The Enable pagination check box appears when you set the following parameters on the Definition tab of the data page rule form:

• Structure – List
• Mode – Read-only
• Scope – Thread or Requestor
• Source – Report definition

Ensure that the report definition is in the same class as the data page.