Return-Path: <issues-return-58700-archive-asf-public=cust-asf.ponee.io@commons.apache.org>
X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io
Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io
Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183])
	by cust-asf2.ponee.io (Postfix) with ESMTP id 94FB8200BEA
	for <archive-asf-public-internal@cust-asf2.ponee.io>; Tue, 27 Dec 2016 17:34:00 +0100 (CET)
Received: by cust-asf.ponee.io (Postfix)
	id 9395E160B31; Tue, 27 Dec 2016 16:34:00 +0000 (UTC)
Delivered-To: archive-asf-public@cust-asf.ponee.io
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by cust-asf.ponee.io (Postfix) with SMTP id DC553160B1D
	for <archive-asf-public@cust-asf.ponee.io>; Tue, 27 Dec 2016 17:33:59 +0100 (CET)
Received: (qmail 93687 invoked by uid 500); 27 Dec 2016 16:33:58 -0000
Mailing-List: contact issues-help@commons.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:issues-help@commons.apache.org>
List-Unsubscribe: <mailto:issues-unsubscribe@commons.apache.org>
List-Post: <mailto:issues@commons.apache.org>
List-Id: <issues.commons.apache.org>
Reply-To: issues@commons.apache.org
Delivered-To: mailing list issues@commons.apache.org
Received: (qmail 93675 invoked by uid 99); 27 Dec 2016 16:33:58 -0000
Received: from arcas.apache.org (HELO arcas) (140.211.11.28)
    by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 27 Dec 2016 16:33:58 +0000
Received: from arcas.apache.org (localhost [127.0.0.1])
	by arcas (Postfix) with ESMTP id 732222C03DE
	for <issues@commons.apache.org>; Tue, 27 Dec 2016 16:33:58 +0000 (UTC)
Date: Tue, 27 Dec 2016 16:33:58 +0000 (UTC)
From: "Yair Lenga (JIRA)" <jira@apache.org>
To: issues@commons.apache.org
Message-ID: <JIRA.13027215.1481405663000.600697.1482856438467@Atlassian.JIRA>
In-Reply-To: <JIRA.13027215.1481405663000@Atlassian.JIRA>
References: <JIRA.13027215.1481405663000@Atlassian.JIRA> <JIRA.13027215.1481405663105@arcas>
Subject: [jira] [Commented] (DBUTILS-131) Speedup query calls without
 parameters
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394
archived-at: Tue, 27 Dec 2016 16:34:00 -0000


    [ https://issues.apache.org/jira/browse/DBUTILS-131?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15780731#comment-15780731 ] 

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)/

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

Yair

> 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
>         Attachments: NewQueryRunner.java, QueryRunner-query.java
>
>
> 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)