Relying on java.sql.ResultSet.findColumn( String) which throws SQLException is slow

[GoogleCodeExporter]

What version of the MyBatis are you using?

mybatis-3.0.1

Please describe the problem.  Unit tests are best!

As seen here: 
http://groups.google.com/group/mybatis-user/browse_thread/thread/1577607d5034d9b
5

Some background info: after going through a bit of a nightmare trying 
to maintain many ResultMaps for various levels associations, etc we 
decided to maintain common ones and re-use them into larger "deep" 
result maps per Object.  This approach is much simpler for us to 
create and maintain, however we started to notice a performance 
problem (but we were unaware it was related to this). 
I spent my whole day today investigating what the performance problem 
is and finally found it, and created a workaround to restore our 
applications performance back to normal. 
In org.apache.ibatis.executor.resultset.NestedResultSetHandler, 
whenever ResultSet.getString(columnName) is invoked (there's a few 
places, one of which his layered through the TypeHandler abstraction) 
if the MyBatis ResultMap contains entries for columns which are not 
returned by the ResultSet, ResultSet.getString(columnName) is throwing 
SQLException. 
I can see that this was known, and the exception is ignored as seen in 
org.apache.ibatis.executor.resultset.NestedResultSetHandler.createRowKeyFor 
MappedProperties(ResultSet, 
CacheKey, List<ResultMapping>) 
Since we're using result maps which can have 4 .. 8 .. 12 levels of 
associations and a SQL query which may return anywhere from a single 
table, to joining all of its associations, this exception was being 
silently ignored (a lot [read: the mapper method I was invoking was 
resulting in this exception being thrown and silently ignored upwards 
of 100,000 times per mapper invocation]). 
Some basic profiling showed that a SQL query which utilized all of the 
elements of a ResultMap averaged about .0028 ms per invocation of 
org.apache.ibatis.executor.resultset.NestedResultSetHandler.createRowKeyFor 
MappedProperties(ResultSet, 
CacheKey, List<ResultMapping>), where a SQL query which utilized only 
a single table of a ResultMap with 7 associations (8 tables mapped 
total), this 
method averaged .312 ms per invocation, roughly 111 times slower, for 
a query with 1,000 rows and about 40 result map entries with no 
columns to  map to, this adds up to a significant amount of time very 
quickly. 
My workaround for now is to override the NestedResultSetHandler class, 
I added this method: 
  protected boolean resultSetHasColumn(final ResultSet rs, final 
String column) { 
    try { 
      final ResultSetMetaData rsmd = rs.getMetaData(); 
      final int columnCount = rsmd.getColumnCount(); 
      for (int i = 1; i <= columnCount; i++) { 
        final String label = rsmd.getColumnLabel(i); 
        if (column.equalsIgnoreCase(label)) { 
          return true; 
        } 
      } 
    } catch (final SQLException e) { 
      // ignore 
    } 
    return false; 
  } 
And I use it in: 
org.apache.ibatis.executor.resultset.NestedResultSetHandler.createRowKeyFor 
MappedProperties(ResultSet, 
CacheKey, List<ResultMapping>) 
org.apache.ibatis.executor.resultset.NestedResultSetHandler.createRowKeyFor 
UnmappedProperties(ResultMap, 
ResultSet, CacheKey) 
and 
org.apache.ibatis.executor.resultset.NestedResultSetHandler.createRowKeyFor 
Map(ResultSet, 
CacheKey) 
I simply check the new method at the beginning of each iteration, 
after the columName String is set, and continue to the next iteration 
if the column doesn't exist. 
Some basic profiling showed a practical speed improvement of about 
107x for this ResultMap situation (there is now no discernible 
difference between a ResultMap that is not being fully filled in vs. 
one that is).

Original issue reported on code.google.com by alex.she...@gmail.com on 22 Sep 2010 at 12:15

[GoogleCodeExporter]

fixed in r4573.

It seems it is only needed in createRowKeyForMappedProperties, not in the other 
methods. Please let me know if I am wrong.

Thanks for the report!

Original comment by eduardo.macarron on 15 Jan 2012 at 12:05

• Changed state: Fixed
• Added labels: Version-Release3.x, Target-Release3.1.0

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

refactor in r5141

Original comment by eduardo.macarron on 11 Apr 2012 at 4:22