Splunk Completes Acquisition of Plumbr Learn more

JDBC Multi-Queries

Executing many JDBC statements during one user interaction is a common source for performance issues. Each individual round­trip to database can be short in duration, but the aggregated latency will often result in poor performance.
Install Plumbr to expose impeding SQL statements and call stacks.
Expose the parts othe application that are slowed by large ResultSets.

How Plumbr will help you

The screenshot above is taken by Plumbr and is exposing the root cause where many SQL statements were executed during a single user interaction. The specific case above reflects a situation where 20,691 JDBC statements were executed, resulting in three minutes and 39 seconds of added wait time.

The information needed to fix the problem is embedded in the following two blocks. First of these exposes the SQL statements executed in the database: 

SELECT
    *
FROM
    Incident
WHERE
    sessionId = ?
AND transactionId = ?

This statement was executed 7,449 times and in total it took one minute and 26 seconds to complete all the roundtrips to the database.

The second SQL statement was executed 5,117 times and it took 43 seconds to complete all the 5,000+ calls 

SELECT
    ap.*
FROM
    AlertPolicy ap
    ...
WHERE
    ...
    AND ap.hidden = ?
    AND ap.type = ?

The block next to SQL statements exposes the call stacks in the source code, zooming in to the exact lines where the statements were executed. To keep overhead at bay, Plumbr has sampled the 20,000+ call stacks and is exposing the information from 38 of such callstacks: 

com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery():N/A
org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement():688
eu.plumbr.portal.incident.IncidentDao.findIncident():133
eu.plumbr.portal.incident.IncidentDao$$FastClassBySpringCGLIB$$.invoke():N/A
org.springframework.cglib.proxy.MethodProxy.invoke():204
...
java.util.concurrent.ThreadPoolExecutor$Worker.run():615
java.lang.Thread.run():745

Considering that the call stacks can be extracted from different locations in source code, Plumbr aggregates those call stacks into a tree­like structure. Call stacks occurring most frequently are ranked higher in a tree.

The Solution

Solving such problems often falls into following either of the paths

  • Batching the individual statements together and executing the statements in one go. In such case, the network latency related overhead will be removed and instead of thousands of individual network round trips only one remote call is carried out.
  • Reducing the amount of data requested. In situations where the retrieved results are not immediately consumed, the data might not need to be fetched all at once.

In the example above, the solution for us was to batch the calls together and instead of tens of thousands calls we ended up with only two calls reducing the impact transaction from three minutes to under one second.