Bug #39962 ResultSet.findColumn performance enhancement
Submitted: 9 Oct 2008 20:08 Modified: 14 Oct 2008 14:48
Reporter: Alex Burgel Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S5 (Performance)
Version:5.1.7 OS:Any
Assigned to: Mark Matthews CPU Architecture:Any

[9 Oct 2008 20:08] Alex Burgel 
Description:
in a hibernate (3.2.6) app, with a large ResultSet (150k+ rows), ResultSet.findColumn becomes a bottleneck.

looking up the column name in the TreeSet takes too long, especially since its being done over and over again.

i tried two different approaches to speed this up.

first i tried to use HashMaps instead of a TreeMaps. I populated the columnNameToIndex, fullColumnNameToIndex, and columnLabelToIndex maps with lower case versions of their respective column names. then in findColumn, i used a lower case version of the column name as the lookup in the maps. this helped a bit, but it was still taking too long to lower case the column names each time.

the next thing i tried was, keeping the original implementations of columnNameToIndex etc, i cached the results of findColumn in a HashMap. this helped a lot. the time it took hibernate to load this ResultSet dropped by half.

i will attach a patch.

How to repeat:
to repeat, apply the patch and see if getting all the columns by name from each row in the ResultSet is faster.
[9 Oct 2008 20:10] Alex Burgel 
patch to speed up findColumn based off svn revision 6775

Attachment: findColumn.patch (application/octet-stream, text), 1.22 KiB.
[10 Oct 2008 8:32] Tonci Grgin 
Hi Alex and thanks for patch provided. I will set the report to "Verified" now to speed up process.

Mark, can you please take a look at patch provided and give your ruling?

Alex, does your patch also reduce the number of "SHOW..." statements issued by Hibernate/cJ? It would be nice to know.
[10 Oct 2008 14:52] Alex Burgel 
no, it doesn't affect the number of 'SHOW' statements. it only speeds up the ResultSetImpl.findColumn method through some additional caching.
[13 Oct 2008 14:43] Mark Matthews 
Patch tested and applied.
[14 Oct 2008 14:48] Tony Bedford 
An entry was added to the 5.1.7 changelog:

With large result sets ResultSet.findColumn became a performance bottleneck.