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 Best, Suat On Mon, Sep 17, 2012 at 10:20 PM, david myers < [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]> 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 >> >> >
