java - Getting ExecuteBatch to execute faster

Question

I'm trying to read a table from a sybase server, process the rows, and output the results to another table. (Below is my code)

The code retrieves the table pretty fast and processes equally fast (get's to the part where it sends within 30 seconds). But When I run execute batch it sits there for 20 minutes before finish (fyi, I have a table which I'm testing with 8400 rows).

Is there a more efficient way to do this? I'm amenable as to how I can recieve or send the queries (I can create a new table, update a table, etc) -- I just don't know why this is so slow (I'm sure the data < 1 MB and I'm sure it doesn't take the SQL server 20 minutes to parse 8400 rows). Any ideas?

Note: The reason this is really bad for me is that I have to parse a table with 1.2 MM rows (this table I'm working with right now is a test table with 8400 rows)

    Connection conn = DriverManager.getConnection(conString, user, pass);


    String sql = "SELECT id,dateid,attr from user.fromtable";
    Statement st = conn.createStatement();
    ResultSet rs = st.executeQuery(sql);

    String sqlOut = "INSERT INTO user.mytabletest (id,attr,date,estEndtime) values (?,?,?,?)";
    PreparedStatement ps = conn.prepareStatement(sqlOut);

    int i=1;

    while(rs.next())
    {
        int date = rs.getInt("dateid");
        String attr = rs.getString("attr");
        String id = rs.getString("id");

        Time tt = getTime(date,attr);
        Timestamp ts = new Timestamp(tt.getTime());

        ps.setString(1, id);
        ps.setString(2, attr);
        ps.setInt(3, date);
        ps.setTimestamp(4, ts);
        ps.addBatch();

        if(i % 10000 == 0)
        {
            System.out.println(i);
            ps.executeBatch();
            conn.commit();
            ps.clearBatch();                
        }

        i++;
    }
    System.out.println("sending "+(new Date()));
    int[] results = ps.executeBatch();
    System.out.println("committing "+(new Date()));
    conn.commit();
    System.out.println("done "+(new Date()));

Answer 1

To work with batches effectively you should turn AutoCommit option off and turn it on after executing the batch (or alternatively use connection.commit() method)

connection.setAutoCommit(false);
while(rs.next())
    {
     .....
     ps.addBatch();     
    }
int[] results = ps.executeBatch();
connection.setAutoCommit(true);