db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag Wanvik <dag.wan...@oracle.com>
Subject Re: Exception while inserting a record
Date Wed, 19 Sep 2012 14:37:14 GMT


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 
> <mailto: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
>             <mailto: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
>
>
>

Mime
View raw message