Explanation of how the APM SQL Agent calculates both metrics and the difference between the two.
Average Result Processing Time (ms):
The average processing time of a query. This metric represents the average time spent processing a ResultSet from the end of the executeQuery() call to the
invocation of the ResultSet's close() method (refer to the Java Agent Guide for details). It is a good indication of the amount of time spent in business
logic or within the application tier.
Average Response Time (ms):
Represents the time of the actual executeQuery() method call and includes the time for query or other SQL operations to execute and bring back the first
part of the ResultSet. Average Response Time does not include the result processing time, so, both of these metrics are mutually exclusive.
An important thing to note here is that based on the Database Driver being used and its implementation, it will only bring back a fixed number of rows
(usually 10) in the corresponding ResultSet object the first time, even if the actual query matches a bigger data set.
For better application performance, it is recommended to override the default Database Driver settings and specify a larger fetch size. This will minimize
the number of round trips to the Database over the network, which often results in significant performance improvement.
See these links for further details on optimizing fetch size and the Java API calls used to set a custom size
If you observe that the duration for Average Response Time (ms) metric is significantly less than the Average Result Processing Time (ms), it may indicate
that the default fetch size used by the Database Driver is negatively impacting performance. You can use the evidence provided by these two metrics to
assist your Development team with optimizing the code.
How these two metrics typically map into code
Following is a skeletal structure of JDBC code you would typically use in Java to get data from the Backend Database
Connection connection = DriverManager.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(); <-- Average Response Time (ms) tracks this call
<-- Average Result Processing Time (ms) Timer starts
resultSet.close();<-- Average Result Processing Time (ms) Timer ends
In general, when Average Result Processing time is high compared to Average Response time, it usually means one of the following:
The result set is too large and the application is taking a long time, bringing way too much data back from the database. In this case, fetch size
should be optimized.
- The application performs a lot of inefficient processing for each row of data in between fetching it, rather than fetching all of the data into a
ResultSet object, closing the ResultSet and then processing it.