Announcement Announcement Module
Collapse
No announcement yet.
jdbc Performance: Profiler inconsistencies Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • jdbc Performance: Profiler inconsistencies

    I'm profiling an application, and finding that significant time is being spent processing the result set from a stored procedure.

    The problem is that JProbe gives one set of statistics and IBM's RAD6 profiler gives a different set.

    They both agree that the time is spent inside org.springframework.jdbc.core.ResultSetExtractor.e xtractData(ResultSet rs), which iterates over the result set, invoking the callback, mapRow, on each iteration.

    However, JProbe states that the majority of the time is spent invoking rs.next(), while RAD6 states that the majority of the time is in the mapRow() callback, specifically, calls to rs.getObject() and rs.getDouble()

    The implementation of mapRow() in this case, contains 22 calls in the form:

    colleague.setAttr_XXX(rs.getObject("COL_NAME_XXX") !=null ? new Double(rs.getDouble("COL_NAME_XXX")):null);

    for 22 attributes of type Double, where a null value is significant.

    If JProbe is correct, then the effort should be spent examining the execution plans of the query statements within the stored procedure.

    If RAD6 is correct, then the getObject() / getDouble() pairs are the culprit, though I'm not sure what could be done to optimize this code.

    Does anyone have any experience with this, and can suggest how to determine the true bottleneck?

    TIA,
    Ed

  • #2
    When I changed the filtering on both profilers to provide more detailed information, they both showed the majority of the time being spent in the mapRow method, specifically, in the Oracle calls underlying rs.getObject(String columnName) and rs.getDouble(columnName). A large part of this time was spent in get_column_index, which I addressed by switching to the index-based versions, rs.getObject(int columnIdx) and rs.getDouble(columnIdx). This yielded a modest performance improvement, but getObject() now was accounting for a big chunk of time, so I rewrote

    Code:
    colleague.setAttr_XXX(rs.getObject("COL_NAME_XXX")!=null ? new Double(rs.getDouble("COL_NAME_XXX")):null);
    as

    Code:
    colleague.setAttr_XXX(computeDouble(rs, colIdx));
    adding a private method

    Code:
    				private Double computeDouble(ResultSet rs, int colIdx) throws SQLException {
    					double tmp = rs.getDouble(colIdx);
    					if (tmp == 0.0 && rs.getObject(colIdx) == null) {
    						return null;
    					}
    					return new Double(tmp);
    				}
    to only invoke getObject when a test for null is truly needed, which it turns out for this app is much less than 50% of the time.
    This greatly reduced the number of calls to getObject(), and yielded a decent performance gain.

    Originally posted by eguy66 View Post
    I'm profiling an application, and finding that significant time is being spent processing the result set from a stored procedure.

    The problem is that JProbe gives one set of statistics and IBM's RAD6 profiler gives a different set.

    They both agree that the time is spent inside org.springframework.jdbc.core.ResultSetExtractor.e xtractData(ResultSet rs), which iterates over the result set, invoking the callback, mapRow, on each iteration.

    However, JProbe states that the majority of the time is spent invoking rs.next(), while RAD6 states that the majority of the time is in the mapRow() callback, specifically, calls to rs.getObject() and rs.getDouble()

    The implementation of mapRow() in this case, contains 22 calls in the form:

    colleague.setAttr_XXX(rs.getObject("COL_NAME_XXX") !=null ? new Double(rs.getDouble("COL_NAME_XXX")):null);

    for 22 attributes of type Double, where a null value is significant.

    If JProbe is correct, then the effort should be spent examining the execution plans of the query statements within the stored procedure.

    If RAD6 is correct, then the getObject() / getDouble() pairs are the culprit, though I'm not sure what could be done to optimize this code.

    Does anyone have any experience with this, and can suggest how to determine the true bottleneck?

    TIA,
    Ed

    Comment

    Working...
    X