commons-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yair Lenga (JIRA)" <>
Subject [jira] [Commented] (DBUTILS-131) Speedup query calls without parameters
Date Tue, 27 Dec 2016 16:33:58 GMT


Yair Lenga commented on DBUTILS-131:

On my benchmark, Sybase ASE 15.7, I've noticed significant improvements on the DB calls: query
takes 10 millisecond, instead of 25 millisecond (fully index query to locate single item in
a large table using PK). Big improvement for data monitoring job. We turned on Sybase statement
cache and auto param, which gives us the benefits of prepared statement (cached query plans)/

I will appreciate if you can test the code against Oracle, and let us know the impact.


> Speedup query calls without parameters
> --------------------------------------
>                 Key: DBUTILS-131
>                 URL:
>             Project: Commons DbUtils
>          Issue Type: Improvement
>    Affects Versions: 1.6
>            Reporter: Yair Lenga
>         Attachments:,
> The current 'query' methods takes sql (with ? placeholders), and list of parameters to
fill the place holders. The implementation is using a prepared statement to speed up the call.
> I've observed that for statements without any ? placeholders, performance can be improved
by using the statement interface. Using this path eliminate extra roundtrip and work that
is needed to create the prepared statement (I believe it's implemented - on Sybase - as light
weight stored proc). 
> My proposal: modify the code in the method that implement the query:
> private <T> T  [More ...] query(Connection conn, boolean closeConn, String sql,
ResultSetHandler<T> rsh, Object... params)
> and use 'Statement', instead of preparedStatement if there are no params (params == null
|| params.size() == 0 )
> In my application, large number of (parameter-less) SQL statement are generated on-the-fly,
and the result set is converted to BeanList. We measured 50% speedup using statements, instead
of prepared statements for this case.
> We will be happy to submit a fix or help with testing.

This message was sent by Atlassian JIRA

View raw message