Diagnosis of database issues

Diagnosing database performance issues in an application can be challenging. The source of most performance issues is how the application uses the database. To find the root cause of any database performance issue, analyze data access patterns and transactional boundaries for how they affect your data models.

Database issues typically affect the speed at which an application performs. Database issues are generally caused by database table size, reading a BLOB, or improper SQL queries.

Though most performance problems have one cause, sometimes you see performance issues with two or more causes. For example, a mobile app with many resolved cases and a SQL connection requires the app to perform a query of a case-sensitive field without regard to case sensitivity.

In the following image of a performance snapshot, click the + icons to see relevant details of all the database-related operations that occurred.

Database issues frequently manifest as slow application performance. Before you begin diagnosing database issues, obtain specifics about the symptoms. Ask the user reporting slow performance to describe the circumstances preceding the slowness. For example, users may see slow performance when opening a form for a customer call, but other processes have no perceived slowness.

To diagnose performance problems caused by database issues, use the Performance Analyzer (PAL) in Dev Studio to generate performance data. By running PAL and taking incremental readings for each form, you can identify where in a process the issue occurs, then drill into the summaries for the step. For more information, see Tracking system utilization for a requestor session with Performance Analyzer and Using the full details display of Performance Analyzer.

You can use DB Trace to examine a query made to the database. For example, sort the DB Trace results by the ReadBlob operation to identify the operations that read data from a BLOB. Remember to look at rules in application rulesets. The following image shows the results of a DB Trace that shows all readBlob operations.

View performance statistics in context. Another factor to consider is how often, and in what circumstances, the issue occurs. For example, 45 requests exceeding a threshold in a pool of 10,000 interactions is less than .5 percent of the interaction pool. If the pool of interactions is 70, the same 45 instances exceeding a threshold are more significant.

The following image shows an example Requestor Summary. Notice the number of server interactions, the database time threshold, and the number of database requests that exceeded the time threshold. In this example, approximately 65 percent of database requests exceeded the time threshold. 

Database issue causes include large database tables, unnecessary reading from a BLOB, and complex SQL queries. Before you begin, diagnose the problem using the tools available on the System Performance landing page in Dev Studio.

Large database table issues

Large database tables impact performance through sheer volume. The larger the database table, the longer it takes to access data on the database. For example, an insurance company processes hundreds of claims a day. The group of resolved claim cases becomes substantial. Within a year, case workers experience a slower-than-normal response time when searching for open cases.

To address large database tables, consider using the Purge/Archive wizard described in the next section.

If the large table maps to a class group, consider creating database table records for one or more classes in the class group. For example, an application creates five types of cases. Half of the cases created are of a single case type. Assigning a separate database table for the most-used case type reduces the table growth rate of the class group. For more information, see Working with class groups.

If the table is pr_other, create a database table record to map the instances of one or more specific classes to a different table. This also involves moving the instances from pr_other to the appropriate table. Use of the pr_other table triggers a PEGA0041 alert and a guardrail warning and is best addressed in development.

Large database tables also cause issues with list reports. You can enable paging on list reports to improve report performance. Paging limits the number of results displayed by a list report to the specified page size you configure. Paging also reduces clipboard size and the time needed to display each results page. Users can move to the next page to view more results.

BLOB issues impacting database size

Reading from a BLOB impacts performance by requiring an application to allocate memory to decompress a BLOB and extract the needed data. Requests that require the application to read an entire BLOB, decompress it, extract the required property, and then discard a BLOB take more time than requests to read the value from a column in the database table.

For example, a data page may source data using a report definition that queries the database for a list of values to display in a drop-down list. Reading data stored in a BLOB can lead to query times that exceed established performance thresholds and triggering performance alerts such as PEGA0005: Query time exceeds limit.

When addressing a BLOB issue, the first step is to determine if a property needs exposure in its own column. Avoid exposing properties until you assess the performance impact of reading from a BLOB. Using a BLOB reduces the size of table rows by compressing unexposed data. Exposing properties unnecessarily can increase database table size.

SQL query issues

Improper SQL queries impact performance by forcing Pega to read items from the database unnecessarily. Improper queries tend to occur either in SQL connectors or reports. For example, a SQL rule that performs an unnecessary case-sensitive query.

If the DBTrace reports that a particular query is taking a long time, look at the SQL construction. You start by copying the query out of the DBTrace data, along with the substitution values, then use the native tools for your database to analyze the query and look for ways to optimize the query.

In a production application, case data volume can grow to a size where hundreds of megabytes of database storage are required. The Purge/Archive wizard enables you to configure and schedule a background process to automatically delete old case data from the respective tables and optionally store them in archive files. Archiving case data enables you to retrieve the data for audits or backups. Purging old resolved cases and their related history and attachment records helps maintain system performance by reducing database processing demands.

Before you use the Purge/Archive wizard, establish a destination archive system that is a copy (same rules and data schemas) of the source production system that you are archiving. To use the Purge/Archive wizard, you first configure an activity to define the purge or archive criteria. Then, you schedule the purging or archiving activities.

There is no easy way to recover the deleted records, so use the purge-only option with care.

You cannot configure the Purge/Archive wizard to only archive records. Purging always occurs after archiving. Purging after archiving removes the rows from the tables.

For more information, see the Pega Community article Purging and archiving old work items.

Check your knowledge with the following interaction.