You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@commons.apache.org by "Yair Lenga (JIRA)" <ji...@apache.org> on 2016/12/10 21:34:58 UTC
[jira] [Created] (DBUTILS-131) Speedup query calls without
parameters
Yair Lenga created DBUTILS-131:
----------------------------------
Summary: Speedup query calls without parameters
Key: DBUTILS-131
URL: https://issues.apache.org/jira/browse/DBUTILS-131
Project: Commons DbUtils
Issue Type: Improvement
Affects Versions: 1.6
Reporter: Yair Lenga
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
(v6.3.4#6332)
Re: (DBUTILS-131) Speedup query calls without parameters
Posted by Matt Sicker <bo...@gmail.com>.
The ticket mentioned Sybase, so I'm going to assume that. Also, I think you
meant to comment on the ticket, not the email.
On 10 December 2016 at 16:36, Bernd Eckenfels <ec...@zusammenkunft.net>
wrote:
> Hello,
> What database system/driver did you test? Did you test repeating
> statements or not. Because some drivers optimize statement caches only for
> prepared statements. This also means it should be configurable.
>
> Gruss
> Bernd
> --
> http://bernd.eckenfels.net
>
>
>
>
> On Sat, Dec 10, 2016 at 10:34 PM +0100, "Yair Lenga (JIRA)" <
> jira@apache.org> wrote:
>
>
>
>
>
>
>
>
>
>
> Yair Lenga created DBUTILS-131:
> ----------------------------------
>
> Summary: Speedup query calls without parameters
> Key: DBUTILS-131
> URL: https://issues.apache.org/jira/browse/DBUTILS-131
> Project: Commons DbUtils
> Issue Type: Improvement
> Affects Versions: 1.6
> Reporter: Yair Lenga
>
>
> 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 [More ...] query(Connection conn, boolean closeConn, String
> sql, ResultSetHandler 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
> (v6.3.4#6332)
>
>
>
>
>
>
--
Matt Sicker <bo...@gmail.com>
Re: (DBUTILS-131) Speedup query calls without parameters
Posted by Bernd Eckenfels <ec...@zusammenkunft.net>.
Hello,
What database system/driver did you test? Did you test repeating statements or not. Because some drivers optimize statement caches only for prepared statements. This also means it should be configurable.
Gruss
Bernd
--
http://bernd.eckenfels.net
On Sat, Dec 10, 2016 at 10:34 PM +0100, "Yair Lenga (JIRA)" <ji...@apache.org> wrote:
Yair Lenga created DBUTILS-131:
----------------------------------
Summary: Speedup query calls without parameters
Key: DBUTILS-131
URL: https://issues.apache.org/jira/browse/DBUTILS-131
Project: Commons DbUtils
Issue Type: Improvement
Affects Versions: 1.6
Reporter: Yair Lenga
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 [More ...] query(Connection conn, boolean closeConn, String sql, ResultSetHandler 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
(v6.3.4#6332)