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
<[email protected]
<mailto:[email protected]>> 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 <[email protected]
<mailto:[email protected]>> 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
