KulbirNijjer

CA Tuesday Tip:SQL Average Response Time vs Average Result Processing Time

Discussion created by KulbirNijjer Employee on Apr 5, 2011
Latest reply on Apr 11, 2011 by vrema01
CA Wily Tuesday Tip by Kulbir Nijjer, Sr. Support Engineer for 04/05/2011

Hi Everyone,

Today's tip covers an important topic which is too often questioned by customers and hopefully this will help clear up any confusion.

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 Java Agent Guide). It is a good indication of amount of time spent in business logic or with in application tier.

Average Response Time (ms): Represents time of actual executeQuery() method call and includes time for query or other SQL operation to executeand bring back first part of ResultSet.Average Response Time does not include the result processing time so both the 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 first time, even if the actual query matches a bigger data set.

Although for better application performance its recommended to override default Database Driver settings and instead specify a decent fetch size so that number of round trips to Database over the network could be reduced and usually it gives a significant performance improvement.
See these links for further details on optimizing fetch size and Java API calls used to set 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 see duration for Average Response Time() metric is quite less than the Average Result Processing Time() ,default fetch size used by the Database Driver could be a big factor in that and above metric can help you to get your dev. team look into it and optimize the code.


How it maps in code ?

Following is a skeletal structure of JDBC code you would typically use in Java to get data from Backend Database


Connection connection = DriverManager.getConnection();

Statement statement = connection.createStatement();

ResultSet resultSet = statement.executeQuery(); <-- Average Response Time() tracks this call

<-- Average Result Processing Time Timer starts
while(resultSet.next())
{
.....Do something
}

resultSet.close();<-- Average Result Processing 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(fetch size could be optimized ?)
2) The application performs a lot of inefficient processing of 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.

Regards,
Kulbir Nijjer.

Outcomes