I have created a program in java for entering 1000 random numbers into two different database one for odd numbers and one for even. The code is executing fine but it takes nearly a minute to execute. How can I minimize the Execution time?
Here is the code:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.Random;
public class Test1 extends Thread {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
long start = System.currentTimeMillis();
int evencount = 0;
int oddcount = 0;
int breakcon = 0;
int breakcon1 = 0;
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1" + "?useSSL=false", "root",
"1234");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2" + "?useSSL=false", "root",
"1234");
try {
Class.forName("com.mysql.jdbc.Driver");
for (int n = 1; n <= 1000; n++) {
Random r = new Random();
int val = r.nextInt(100000);
if (val % 2 == 0) {
PreparedStatement ps = con.prepareStatement(" insert into try values (?)");
ps.setInt(1, val);
ps.executeUpdate();
ps.addBatch();
breakcon = breakcon + 1;
if (breakcon % 500 == 0 || breakcon == val)
ps.executeBatch();
evencount++;
} else {
try {
Class.forName("com.mysql.jdbc.Driver");
PreparedStatement ps3 = conn.prepareStatement(" insert into try1 values (?)");
ps3.setInt(1, val);
ps3.executeUpdate();
ps3.addBatch();
breakcon1 = breakcon1 + 1;
if (breakcon1 % 500 == 0 || breakcon1 == val)
ps3.executeBatch();
oddcount++;
}
catch (Exception e2) {
System.out.println(e2);
}
}
}
}
catch (Exception e) {
System.out.println(e);
}
long end = System.currentTimeMillis();
NumberFormat formatter = new DecimalFormat("#0.00000");
System.out.println("Execution time is " + formatter.format((end - start) / 1000d) + " seconds");
System.out.println(oddcount + evencount);
}
}
Instead of calling
ps.executeUpdate();
(andps3.executeUpdate();
) after each time yousetInt
- do it only once after the for-loop. That's the whole point of usingaddBatch
(aggregate inserts/updates and executing them all at once).As Boris mentioned in the comment below, it would make the execution much faster if you also turn on
rewriteBatchedStatements
. See here how it can be achieved.