Insert rows vs. dbload

I used Informix JDBC drive to insert thousands of rows into different Informix tables. I am wonder if I create a text file and use dbload command, will be more efficient than used SQL INSERT statement? Thank you!

Yes, by far! Freelancer <rhchui@yahoo.com> wrote in message news:3916CD46.91294534@yahoo.com... > I used Informix JDBC drive to insert thousands of rows into different > Informix > tables. I am wonder if I create a text file and use dbload command, will > be > more efficient than used SQL INSERT statement? > > Thank you! >

Freelancer wrote: > I used Informix JDBC drive to insert thousands of rows into different > Informix > tables. I am wonder if I create a text file and use dbload command, will > be > more efficient than used SQL INSERT statement? > > Thank you! Not necessarily. For example, if you still need to run your Java program to create your text files row by row before doing the dbload, you may be better off using prepared statements (or even PUT cursors if JDBC allows it) in your program. Why don't you carry out a test (and post your results)? In terms of integrity, of course, the two do not compare. Rudy

Rudy Fernandes wrote: > Freelancer wrote: > > > I used Informix JDBC drive to insert thousands of rows into different > > Informix > > tables. I am wonder if I create a text file and use dbload command, will > > be > > more efficient than used SQL INSERT statement? > > > > Thank you! > > Not necessarily. For example, if you still need to run your Java program to > create your text files row by row before doing the dbload, you may be > better off using prepared statements (or even PUT cursors if JDBC allows > it) in your program. Why don't you carry out a test (and post your > results)? > Yes, I did the test for PreparedStatement yesterday. But I came up a problem due to HP-UX 10.20, with Java 1.1.7 (only HP-UX 11.x support Java 1.2.x). There is a table column is Timestamp data type. In my Java program I sql = "INSERT INTO aTable (time) VALUE (?)"; PrepareStatemnet prepare = connection.prepareStatement(sql); prepare.clearParamenters(); setTimestamp(3, new Timestamp(timestamp)); prepare.executeUpdate(); which just gives me incorrect time. I tried use setTimestamp(3, new Timestamp(timestamp), calendar); But it not support in JDK 1.1.7.

Freelancer wrote: > Yes, I did the test for PreparedStatement yesterday. But I came up a problem > due to HP-UX 10.20, with Java 1.1.7 (only HP-UX 11.x support Java 1.2.x). > There is a table column is Timestamp data type. In my Java program I > > sql = "INSERT INTO aTable (time) VALUE (?)"; > PrepareStatemnet prepare = connection.prepareStatement(sql); > prepare.clearParamenters(); > setTimestamp(3, new Timestamp(timestamp)); > prepare.executeUpdate(); > > which just gives me incorrect time. > I tried use setTimestamp(3, new Timestamp(timestamp), calendar); > But it not support in JDK 1.1.7. You could use the Informix keyword CURRENT to get over the problem (if you don't mind getting database specific). Your code will look like this : sql = "INSERT INTO aTable (time) VALUE (CURRENT)"; PrepareStatement prepare = connection.prepareStatement(sql); prepare.executeUpdate(); Keep in mind, though, that the real value of a Prepared statement is in its reuse. That is, you prepare once, and use the prepared statement many times. For example, sql = "INSERT INTO aTable (time) VALUE (CURRENT)"; PrepareStatement prepare = connection.prepareStatement(sql); for (int i=1; i<= 10000; i++ ) { try { prepare.executeUpdate(); } catch (SQLException sqle) { throw sqle; } } Rudy