Optimizing performance tuning data processing through retrieval and indexing
|Description||Guidelines on retrieving and indexing application data for faster processing|
|Version as of||8.5|
|Application||Other Industry-Specific Application|
The data needed by an application can be retrieved in a number of ways, by using:
- A connector (for example, REST, SOAP, HTTP) to a service for external data.
- A report definition or lookup for a class (database table) for locally stored data.
- A data transform that can retrieve hardcoded, external, locally stored data, or aggregate from several data pages.
If the data sources (internal and external) of an application are not adequately indexed, performance can be impacted to the extent that business goals are not met and the deployment (or demonstration) of the application is considered a failure.
Learn what steps to take to prevent such an outcome.
Today, most data retrieval operations are performed by data pages. Because of that, when configuring a new data page, consider the following steps:
- Understand the data by answering the following questions:
- What is the size of this data table in production? Will the table contain just a few hundred or millions of records?
- What are the indexes available?
- What is the nature of the data? Does the data vary greatly or is there very little variation in the key data elements? If the latter is the case with a large table, revisit the business requirements.
- Ensure that the data page can perform. Ensure that the index needed (derived from the
whereclause) is available if no existing indexes can be re-used. Often, when two indexes are needed for the new report definition, one of them already exists. Do not index everything, because indexes are large and when large database tables contain many of them, this state can have an adverse effect on the application performance.
- Test your indexing approach:
- Test with enough data to ensure that the new index is used and has the desired effect.
- Insist on testing the data page with production-grade data.
Beginning with Pega Infinity release 8.5, you can use new features in Dev Studio to run and analyze SQL queries. The screenshots below show the Query Inspector and the Query Runner tools:
Additionally, Pega Predictive Diagnostic Cloud (PDC) contains a host of information to analyze a running application, available in the System Resources category, in the Database node. Use the PDC to:
- Review metrics around index counts, unused and scanned indexes.
- Analyze tables based on index scan trends.
- Observe sequential and index scans