Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    37

    Question Unanswered: performans question : should i use prepared statement or not?

    hello,
    our web server and db2 server are on different servers (win2k).
    in my web application (jsp) i have to do insertions into a db2 table.
    now, i could use:
    st.executeUpdate("insert into mytable (columnA, columnB, columC) values (45, 3, 'dummyString1'), (45, 3, 'dummyString2'), (35, 2, 'dummyString3'), .... , (51, 6, 'dummyString98'), (51, 7, 'dummyString99), (51, 7, 'dummyString100')");

    using this method, i had only to pass one 'big' insertion statement to db2 and all is done.

    but i could use this :
    ps = cn.prepareStatemnt("insert into mytable (columnA, columnB, columnC) values (?, ?, ?)");

    for (int x = 0; x < 100; ++x) {
    ps.setInt(1, array1[x]); ps.setInt(2, array2[x]); ps.setString(3, array3[x]);
    ps.executeUpdate();
    }

    what is 'better' here, going to the db2 server 100 times with a prepared statement and increase network traffic , or concatenate a big string and send it once using a statement ?

    your comments are appreciated.
    thanks in advance.

    Mehmet Gunacti
    mserkan@gmx.net

  2. #2
    Join Date
    Dec 2002
    Posts
    134
    you should combine both approaches

    ps = cn.prepareStatemnt("insert into mytable (columnA, columnB, columnC) values (?, ?, ?)");
    for (int x = 0; x < 100; ++x) {
    ps.setInt(1, array1[x]); ps.setInt(2, array2[x]); ps.setString(3, array3[x]);
    ps.addBatch();
    }
    ps.executeBatch();

    Based on your setup you can find out the optimal size for the batch
    (usually some number between 50-100)

    P.S.
    In odbc you can express this task more elegantly

  3. #3
    Join Date
    Oct 2002
    Posts
    37

    Smile re:performans question : should i use prepared statement or not?

    Thanks for the tip !
    Regards

    Mehmet Gunacti
    mserkan@gmx.net

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •