db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Suat Gönül <suatgo...@gmail.com>
Subject Re: Exception while inserting a record
Date Tue, 18 Sep 2012 10:08:41 GMT
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


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

View raw message