Slow ResultSet Processing

Fetching results from a relational database contains multiple steps. One of such steps is processing the results received by iterating over the returned result set. Whenever the result set is large, it can take a considerable amount of time, impacting the end user.

Start your free trial

How Plumbr will help you

The screenshot above is taken from Plumbr and exposes a root cause where processing the 1,297,822 results returned from the database has been a source for the performance bottleneck. Processing these 1.2M results took close to 13 seconds to complete. As seen, during the last 30 days there were 2,402 where processing the results of the same query has been a problem.

The information needed to fix the problem is embedded in the following two blocks. First of these exposes the SQL statement responsible for fetching the 1,297,822 rows:

SELECT
    p.id,
    ...
FROM
    Problem AS p
    ...
WHERE
    p.sessionId = ?

The block next to it exposes the call stack in the source code, zooming in to the exact line where the statement was executed:

com.zaxxer.hikari.pool.HikariProxyResultSet.next()N/A*
org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData():92
org.codehaus.groovy.runtime.callsite.AbstractCallSite.call():141
eu.plumbr.dashboard.stats.JpaSessionStatService.getSessionProblemRows():138

As seen from above, the statement was executed at line 138 in JpaSessionStatService.getSessionProblemRows() method in our business logic.


The Solution

The solutions to the slow resultset processing problem boil down to fetching less data from the database, either via:

  • Applying page­by­page iterator pattern on large lists, limiting the number of rows fetched from the storage to the number of records visible
  • Performing necessary aggregations and calculations in the database side, skipping the need to fetch raw data to the JVM.

Start your free trial