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. Tuning and maintenance tasks vary, depending on the database vendor that you use. Regardless of the database that your application runs on, these techniques can help you improve report performance.
You can pre-populate 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.
Execute Explain Plans on your queries
An Explain Plan describes the path that the query takes to return a result set. This technique can help you determine whether a 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 query with substituted values, you can run the Explain Plan for the query in the database client of your choice.
Create table indexes
After you expose 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 applies:
- The column is queried frequently
- A referential integrity constraint exists on the column
- A UNIQUE key integrity constraint exists on the column
Purge and archive 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 a query. You can also use the Purge and Archive wizard to achieve this purpose.
For more information about purging and archiving data, see the Purge/Archive wizards article on the Pega documentation site.
Check your knowledge with the following interaction:
This Topic is available in the following Module:
Want to help us improve this content?