Take the 2-minute tour ×

I am updating remote DB by JDBC Prepared statements. When i execute the same code to update local DB it takes Milliseconds but for remote DB it is taking too long for around 900 rows. Size of all rows combined is 160KB. Remote Host is Hostgator and DB Engine for table is InnoDB

Here is my Code:

String insertTableSQL = "INSERT INTO abc(zzz,c,d,e,timestamp) VALUES (?,?,?,?,?)";

String delSt = "DELETE FROM abc WHERE zzz= ?";

del = connection.prepareStatement(delSt);
preparedStatement = connection.prepareStatement(insertTableSQL);
Iterator<DataObject> itr = updationList.iterator();
    while (itr.hasNext()) {
        DataObject dO = itr.next();
        del.setString(1, dO.a + ":" + dO.b);
    for (SubObject sO : dO.getsO.values()) {
        try {
            preparedStatement.setString(1, sO.a + ":" + sO.b);
            preparedStatement.setString(2, sO.c);
            preparedStatement.setInt(3, sO.d);
            preparedStatement.setFloat(4, sO.e);
            preparedStatement.setTimestamp(5, getCurrentTimeStamp());
            } catch (SQLException e) {
    try {
    } catch (Exception e) {
    try {
share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

You should check that the indexes are the same on your local table and on the remote server. The delete is likely the culprit and EXPLAIN SELECT * FROM abc WHERE zzz=? will show you what's going on there. You can try adding compression to your connection by adding useCompression=true to your JDBC connection string. This will shrink that 160k down a bit and while it's sending commands.


share|improve this answer
I tried with 200 rows on another table .Time taken was 441ms to Prepare List, 79671ms to execute List, 390ms to commit. In above case of many rows(900) it doesnot insert anything. DB structure is same because i imported the same table in Remote DB. Can the problem be with Host ? –  Solution Jul 15 '12 at 18:41
I think you have about 400ms network latency and that's killing performance. Add rewriteBatchedStatements=true to enable batches to work properly. If you have a unique (or primary) index on "zzz" you can rewrite your DELETE/INSERT statements using INSERT ... ON DUPLICATE KEY UPDATE .... You could also do DELETE FROM abc WEHRE zzz IN (?, ?, ?, ...) if you don't have a suitable index. –  Joshua Martell Jul 15 '12 at 21:36
yes latency is high. Any other way i can update faster. I have good upload speed. –  Solution Jul 16 '12 at 5:10
Thanks a lot. Now Time for execution is ~4000ms for the list by using these ?useServerPrepStmts=false&rewriteBatchedStatements=true&useCompression=true' –  Solution Jul 16 '12 at 6:05
add comment

Your Answer


