Re: Performance improvement proposal. Removal of toLowerCase calls.

Well postgresql folds to lower case by default so the case of the request
SQL is irrelevant unless the column is double quoted or the column really
does have mixed case.

Please do prepare PR and test case

Thanks

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Thu, Jan 16, 2014 at 12:50 PM, Jeremy Whiting <jwhiting(at)redhat(dot)com>wrote:

> Hi Dave,
>
>
> On 16/01/14 16:12, Dave Cramer wrote:
>
> Jeremy.
>
> I am even wondering why we would be doing that and even worse using a
> locale to do it.
>
> I suspect it's caters for when the request SQL is mixed case. To get a
> match from the db server the response SQL (as you say is lower case) you'd
> need to make sure the .put(...) on the registry is either all upper or
> lower.
>
> AFAICT, the db would return everything in lower case anyway, and if the
> column really had mixed case it should be returned with it's original case
> intact.
>
> Shall I prepare a PR with a test case ?
>
> Jeremy
>
>
> Dave
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Thu, Jan 16, 2014 at 10:44 AM, Jeremy Whiting <jwhiting(at)redhat(dot)com>wrote:
>
>> 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.
>>
>> Are there other factors that might affect this optimization that need
>> to be taken into consideration ?
>>
>> Regards,
>> Jeremy Whiting
>>
>> Senior Software Engineer
>> Red Hat
>>
>> [1]
>>
>> https://github.com/pgjdbc/pgjdbc/blob/REL9_3_STABLE/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java?#L2742
>> [2]
>>
>> https://github.com/pgjdbc/pgjdbc/blob/REL9_3_STABLE/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java?#L2752
>>
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>
>
>