Database tuning to improve report performance
You can perform specific database tuning and maintenance tasks to help improve report performance. Enlist the help of your database administrator to perform these tasks and to provide additional guidance. These tasks vary depending on the database vendor you are using. Regardless of the database that your application runs on, these techniques can help you improve report performance.
You can prepopulate a staging environment with production-like data to test your reports with a realistic data volume. Many organizations require any sensitive information to be removed (scrubbed) before running this type of test, and this can take some time. Plan your testing accordingly.
Partitioning tables
Table partitioning allows tables or indexes to be stored in multiple physical sections. A partitioned index is like one extensive index made up of multiple little indexes. Each chunk, or partition, has the same columns but a different range of rows. How you partition your tables depends on your business requirements.
For more information on partitioning Pega tables, see PegaRULES table partitioning.
Executing Explain Plans on your queries
An Explain Plan describes the path the query takes to return a result set. This technique can help you determine if the database is taking the most efficient route to return results. You can extract the query with substituted values by using the Database profiler or by tracing the report while it runs. Once you have the query with substituted values, you can run the Explain Plan for the query in the database client of your choice.
Creating table indexes
After you have exposed one or more columns in a database table, you can create an index on that column. Do not create too many indexes, because this can degrade performance. In general, create an index on a column if any of the following statements is true.
- The column is queried frequently.
- A referential integrity constraint exists on the column.
- A UNIQUE key integrity constraint exists on the column.
Dropping the pzpvStream column on pr_index tables
pr_index tables do not require the pzPvStream column. Removing this column prevents replicated data from being returned to the application and taking up memory on the clipboard.
Purging and archiving data
Depending on the retention requirements for your application, consider archiving data to Nearline or Offline storage, either in another database table or in a data warehouse. Purging and archiving data that is either no longer needed or infrequently accessed can improve report performance because the application has a smaller set of records to consider when running the query. You can also use the Purge and Archive wizard to achieve this purpose.
For more information about purging and archiving data, see the help topic Purge/Archive wizards.
Want to help us improve this content?