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 pagebypage 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.
