From derby-user-return-14628-apmail-db-derby-user-archive=db.apache.org@db.apache.org Wed Sep 19 14:37:47 2012 Return-Path: X-Original-To: apmail-db-derby-user-archive@www.apache.org Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id BFC07DF2A for ; Wed, 19 Sep 2012 14:37:47 +0000 (UTC) Received: (qmail 3580 invoked by uid 500); 19 Sep 2012 14:37:47 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 3552 invoked by uid 500); 19 Sep 2012 14:37:47 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 3545 invoked by uid 99); 19 Sep 2012 14:37:47 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 19 Sep 2012 14:37:47 +0000 X-ASF-Spam-Status: No, hits=-2.8 required=5.0 tests=FSL_RCVD_USER,HTML_MESSAGE,RCVD_IN_DNSWL_HI,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of dag.wanvik@oracle.com designates 148.87.113.117 as permitted sender) Received: from [148.87.113.117] (HELO rcsinet15.oracle.com) (148.87.113.117) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 19 Sep 2012 14:37:38 +0000 Received: from acsinet21.oracle.com (acsinet21.oracle.com [141.146.126.237]) by rcsinet15.oracle.com (Sentrion-MTA-4.2.2/Sentrion-MTA-4.2.2) with ESMTP id q8JEbFqS015682 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK) for ; Wed, 19 Sep 2012 14:37:16 GMT Received: from acsmt358.oracle.com (acsmt358.oracle.com [141.146.40.158]) by acsinet21.oracle.com (8.14.4+Sun/8.14.4) with ESMTP id q8JEbFUg014679 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Wed, 19 Sep 2012 14:37:15 GMT Received: from abhmt103.oracle.com (abhmt103.oracle.com [141.146.116.55]) by acsmt358.oracle.com (8.12.11.20060308/8.12.11) with ESMTP id q8JEbEXm024069 for ; Wed, 19 Sep 2012 09:37:14 -0500 Received: from [10.175.18.65] (/10.175.18.65) by default (Oracle Beehive Gateway v4.0) with ESMTP ; Wed, 19 Sep 2012 07:37:14 -0700 Message-ID: <5059D89A.9080206@oracle.com> Date: Wed, 19 Sep 2012 16:37:14 +0200 From: Dag Wanvik Organization: Oracle Corporation User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:12.0) Gecko/20120428 Thunderbird/12.0.1 MIME-Version: 1.0 To: Derby Discussion Subject: Re: Exception while inserting a record References: <505199C6.80107@gmail.com> <5051B325.2050209@gmail.com> <5051C02A.7090403@gmail.com> <50577812.20407@gmail.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------030607000708040701000701" X-Source-IP: acsinet21.oracle.com [141.146.126.237] X-Virus-Checked: Checked by ClamAV on apache.org This is a multi-part message in MIME format. --------------030607000708040701000701 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit On 18.09.2012 12:08, Suat Gönül wrote: > Hi David, > > I had inserted 860K items to the database in total. There was an index > over the revision field. Here are the elapsed times for the whole process: > > Insertion 1 by 1 with PreparedStatement: ~8121 s > Insertion 1000 by 1000 with Statement: ~886 s > Insertion 1000 by 1000 with PreparedStament: ~532 s If possible with the app logic, it would speed things up to first add the index *after* the inserts are done. Another approach is to use the SYSCS_UTIL.SYSCS_IMPORT_* stored procedures if data is available on a file format. Dag > > Best, > Suat > > > On Mon, Sep 17, 2012 at 10:20 PM, david myers > > wrote: > > Suat > > If you are doing insertions that use the exact same statement that > many times in succession you should see a performance improvement > using a prepared statement. > > Also the prepared statement will negate the need to worry about > escaping any other 'special characters' as part of the idea is > that the prepared statement will deal with them at the level of > the DBMS. Rather than forcing you to deal with it in your code. > The same is also true with Callable statements. > > That said I would be interested to see how much of a difference it > makes using a normal or prepared statement, mainly to see where > the break point is in such things. > > David > > > > > > On 13/09/12 13:14, Suat Gonul wrote: > > On 09/13/2012 01:54 PM, Knut Anders Hatlen wrote: > > Suat Gonul > writes: > > Hi Knut, > > It seems that is the problem, thanks. But, then I > think I should escape > special characters contained the values. Is there > standard procedure for > this? Is there a list of of special characters? What > do you suggest? > > I'd suggest that you use prepared statements with > parameter markers > > INSERT INTO t (id, revision) VALUES (?, 1) > > and use ps.setString(1, "string value") to set the value. > Then you don't > need to worry about special characters in the string. > > If you want to specify the string literally in your SQL > statement, only > the single-quote character is a special character, as far > as I know, and > it can be escaped with an extra single-quote character. > For example, to > insert the string «It's safer with PreparedStatement», you > would have to > do something like this: > > INSERT INTO t (id, revision) VALUES ('It''s safer with > PreparedStatement', 1) > > Thank you very much. Escaping the ' character with another ' > has solved > my problem. > > Indeed I am doing a bulk insertion operation (1000 insertion > at a time > (Values > 1000 causes stackoverflow exception)). So I prepare > the query > in advance and execute it in one step. In total, I have ~1M > records. > However, I could not decide on which one would be more > efficient. So, > I'm trying both options now. > > Thanks again, > Best, > Suat > > > --------------030607000708040701000701 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit

On 18.09.2012 12:08, Suat Gönül wrote:
Hi David,

I had inserted 860K items to the database in total. There was an index over the revision field. Here are the elapsed times for the whole process:

Insertion 1 by 1 with PreparedStatement: ~8121 s
Insertion 1000 by 1000 with Statement: ~886 s
Insertion 1000 by 1000 with PreparedStament: ~532 s

If possible with the app logic, it would speed things up to first add the index *after* the inserts are done. Another approach is to use the SYSCS_UTIL.SYSCS_IMPORT_* stored procedures if data is available on a file format.

Dag


Best,
Suat


On Mon, Sep 17, 2012 at 10:20 PM, david myers <david.myers.scibearspace@gmail.com> wrote:
Suat

If you are doing insertions that use the exact same statement that many times in succession you should see a performance improvement using a prepared statement.

Also the prepared statement will negate the need to worry about escaping any other 'special characters' as part of the idea is that the prepared statement will deal with them at the level of the DBMS. Rather than forcing you to deal with it in your code. The same is also true with Callable statements.

That said I would be interested to see how much of a difference it makes using a normal or prepared statement, mainly to see where the break point is in such things.

David





On 13/09/12 13:14, Suat Gonul wrote:
On 09/13/2012 01:54 PM, Knut Anders Hatlen wrote:
Suat Gonul <suatgonul@gmail.com> writes:

Hi Knut,

It seems that is the problem, thanks. But, then I think I should escape
special characters contained the values. Is there standard procedure for
this? Is there a list of of special characters? What do you suggest?
I'd suggest that you use prepared statements with parameter markers

INSERT INTO t (id, revision) VALUES (?, 1)

and use ps.setString(1, "string value") to set the value. Then you don't
need to worry about special characters in the string.

If you want to specify the string literally in your SQL statement, only
the single-quote character is a special character, as far as I know, and
it can be escaped with an extra single-quote character. For example, to
insert the string «It's safer with PreparedStatement», you would have to
do something like this:

INSERT INTO t (id, revision) VALUES ('It''s safer with PreparedStatement', 1)

Thank you very much. Escaping the ' character with another ' has solved
my problem.

Indeed I am doing a bulk insertion operation (1000 insertion at a time
(Values > 1000 causes stackoverflow exception)). So I prepare the query
in advance and execute it in one step. In total, I have ~1M records.
However, I could not decide on which one would be more efficient. So,
I'm trying both options now.

Thanks again,
Best,
Suat



--------------030607000708040701000701--