SQL Agent Average Response Time vs. Average Result Processing Time.

Products

• CA Application Performance Management Agent (APM / Wily / Introscope)
• INTROSCOPE

Issue/Introduction

Description:

Explanation of how the APM SQL Agent calculates both metrics and the difference between the two.

Solution:

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

(http://webmoli.com/2009/02/01/jdbc-performance-tuning-with-optimal-fetch-size/)
(http://download.oracle.com/javase/1.5.0/docs/api/java/sql/Statement.html#setFetchSize%28int%29)

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 	
 	while(resultSet.next()) 	
 	{ 	
 	.....Do something 	
 	} 

 	resultSet.close();<-- Average Result Processing Time (ms) Timer ends 	
 	connection.close(); 

In general, when Average Result Processing time is high compared to Average Response time, it usually means one of the following:

1. 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.
   
   
2. 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.

Environment

Release:
Component: APMAGT