Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
JDBC Benchmark Row-by-row Update (with and without PreparedStatement reuse) vs Batch vs Bulk Update
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Duration;
import java.time.Instant;
import java.util.Properties;
public class OracleUpdate {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@192.168.99.100:1521:ORCLCDB";
String user = "TEST";
String password = "TEST";
Properties properties = new Properties();
properties.setProperty("user", user);
properties.setProperty("password", password);
try (Connection c = DriverManager.getConnection(url, properties)) {
for (int i = 0; i < 5; i++) {
Instant ts;
resetPost(c);
ts = Instant.now();
try (Statement s = c.createStatement();
ResultSet rs = s.executeQuery(
"SELECT id FROM post WHERE archived = 0 AND creation_date < DATE '2018-01-01'"
)) {
while (rs.next()) {
try (PreparedStatement u = c.prepareStatement(
"UPDATE post SET archived = 1 WHERE id = ?"
)) {
u.setInt(1, rs.getInt(1));
u.executeUpdate();
}
}
}
System.out.println("Run " + i + ", Statement 1: " + Duration.between(ts, Instant.now()));
resetPost(c);
ts = Instant.now();
try (Statement s = c.createStatement();
ResultSet rs = s.executeQuery(
"SELECT id FROM post WHERE archived = 0 AND creation_date < DATE '2018-01-01'"
);
PreparedStatement u = c.prepareStatement(
"UPDATE post SET archived = 1 WHERE id = ?"
)) {
while (rs.next()) {
u.setInt(1, rs.getInt(1));
u.executeUpdate();
}
}
System.out.println("Run " + i + ", Statement 2: " + Duration.between(ts, Instant.now()));
resetPost(c);
ts = Instant.now();
try (Statement s = c.createStatement();
ResultSet rs = s.executeQuery(
"SELECT id FROM post WHERE archived = 0 AND creation_date < DATE '2018-01-01'"
);
PreparedStatement u = c.prepareStatement(
"UPDATE post SET archived = 1 WHERE id = ?"
)) {
while (rs.next()) {
u.setInt(1, rs.getInt(1));
u.addBatch();
}
u.executeBatch();
}
System.out.println("Run " + i + ", Statement 3: " + Duration.between(ts, Instant.now()));
resetPost(c);
ts = Instant.now();
try (Statement s = c.createStatement()) {
s.executeUpdate("UPDATE post\n" +
"SET archived = 1\n" +
"WHERE archived = 0 AND creation_date < DATE '2018-01-01'\n");
}
System.out.println("Run " + i + ", Statement 4: " + Duration.between(ts, Instant.now()));
}
}
}
static void resetPost(Connection c) throws SQLException {
try (Statement s = c.createStatement()) {
s.executeUpdate("TRUNCATE TABLE post");
s.executeUpdate("INSERT INTO post\n" +
" SELECT \n" +
" level,\n" +
" lpad('a', 1000, 'a'),\n" +
" 0,\n" +
" DATE '2017-01-01' + (level / 10)\n" +
" FROM dual\n" +
" CONNECT BY level <= 10000");
s.executeUpdate("BEGIN dbms_stats.gather_table_stats('TEST', 'POST'); END;");
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment