Hi, I would like to propose an optimization to improve performance in the jdbc driver. The performance improvement has been tested on commodity hardware using an industry standard Java benchmark. The overall benchmark metric reports an improvement in performance. Profiling using sampling showed calls reduced from 1100 to 0 when the benchmark workload is running.
The optimization will eliminate calls to the method toLowerCase(java.util.Locale). This in the pg-jdbc org.postgresql.jdbc2.AbstractJdbc2ResultSet.findColumnIndex(java.lang.String) method when setting up the column index registry.
For the optimization to be enabled I suggest relying on a new system property. Making the existing functionality the default behaviour to ensure existing applications do not break when the driver is upgraded.
The change removes the call toLowerCase when putting items in the registry [1]. Essentially what's being proposed is removing sanitizing the key names. For best performance application code should pass SQL to the driver with column names already folded to lower case. But upper case names will still be matched in the second lookup [2] in the method.
****************
For this optimization to work this feature introduces a requirement on applications. To use all lower or upper case column names.
****************
This is going to break existing applications, if the requirement is to have either upper or lowercase. I test explicitly with the MyJSQLView application the use of mixed case column, key index names because there are applications that used mixed case. The proper way to handle is quote to handle the mixed case so it is store as such.
Perhaps the optimization could check for quoting then do no addition process and store directly intact. Other wise the optimization could initiate as proposed.
danap.
Are you sure ? This is in the resultset, so any column names should have come back from the db.
Which means that they should come back in lower case anyway.
I would think the current code would break apps that having real mixed case columns in the database ?
Jeremy's proposal would leave the case alone and store it in the map with mixed case.