Created
April 19, 2018 13:58
-
-
Save lukaseder/b101ec4402d8a1913e107eab3258e7a1 to your computer and use it in GitHub Desktop.
JDBC Benchmark Row-by-row Update (with and without PreparedStatement reuse) vs Batch vs Bulk Update
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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