Эффективный способ выполнять пакетные ВСТАВКИ с помощью JDBC
В моем приложении мне нужно выполнять много вставок. Это Java-приложение, и я использую обычный JDBC для выполнения запросов. Базой данных является Oracle. Я включил пакетную обработку, поэтому это экономит время ожидания в сети при выполнении запросов. Но запросы выполняются последовательно как отдельные вставки:
insert into some_table (col1, col2) values (val1, val2)
insert into some_table (col1, col2) values (val3, val4)
insert into some_table (col1, col2) values (val5, val6)
Мне было интересно, может ли следующая форма ВСТАВКИ быть более эффективной:
insert into some_table (col1, col2) values (val1, val2), (val3, val4), (val5, val6)
т.е. сворачивание нескольких вставок в одну.
Есть еще советы по ускорению пакетных вставок?
Переведено автоматически
Ответ 1
Это сочетание двух предыдущих ответов:
PreparedStatement ps = c.prepareStatement("INSERT INTO employees VALUES (?, ?)");
ps.setString(1, "John");
ps.setString(2,"Doe");
ps.addBatch();
ps.clearParameters();
ps.setString(1, "Dave");
ps.setString(2,"Smith");
ps.addBatch();
ps.clearParameters();
int[] results = ps.executeBatch();
Ответ 2
Хотя вопрос требует эффективной вставки в Oracle с использованием JDBC, в настоящее время я играю с DB2 (на мэйнфрейме IBM), концептуально вставка будет аналогичной, поэтому подумал, что было бы полезно просмотреть мои показатели между
вставка по одной записи за раз
вставка пакета записей (очень эффективно)
Здесь приведены показатели
1) Вставка по одной записи за раз
public void writeWithCompileQuery(int records) {
PreparedStatement statement;
try {
Connection connection = getDatabaseConnection();
connection.setAutoCommit(true);
String compiledQuery = "INSERT INTO TESTDB.EMPLOYEE(EMPNO, EMPNM, DEPT, RANK, USERNAME)" +
" VALUES" + "(?, ?, ?, ?, ?)";
statement = connection.prepareStatement(compiledQuery);
long start = System.currentTimeMillis();
for(int index = 1; index < records; index++) {
statement.setInt(1, index);
statement.setString(2, "emp number-"+index);
statement.setInt(3, index);
statement.setInt(4, index);
statement.setString(5, "username");
long startInternal = System.currentTimeMillis();
statement.executeUpdate();
System.out.println("each transaction time taken = " + (System.currentTimeMillis() - startInternal) + " ms");
}
long end = System.currentTimeMillis();
System.out.println("total time taken = " + (end - start) + " ms");
System.out.println("avg total time taken = " + (end - start)/ records + " ms");
statement.close();
connection.close();
} catch (SQLException ex) {
System.err.println("SQLException information");
while (ex != null) {
System.err.println("Error msg: " + ex.getMessage());
ex = ex.getNextException();
}
}
}
Метрики для 100 транзакций :
each transaction time taken = 123 ms
each transaction time taken = 53 ms
each transaction time taken = 48 ms
each transaction time taken = 48 ms
each transaction time taken = 49 ms
each transaction time taken = 49 ms
...
..
.
each transaction time taken = 49 ms
each transaction time taken = 49 ms
total time taken = 4935 ms
avg total time taken = 49 ms
Выполняется первая транзакция, 120-150ms
которая предназначена для анализа запроса, а затем выполнения, последующие транзакции только выполняются 50ms
. (Который по-прежнему высок, но моя база данных находится на другом сервере (мне нужно устранить неполадки в сети))
2) Со вставкой в пакет (эффективной) - достигается за счет preparedStatement.executeBatch()
public int[] writeInABatchWithCompiledQuery(int records) {
PreparedStatement preparedStatement;
try {
Connection connection = getDatabaseConnection();
connection.setAutoCommit(true);
String compiledQuery = "INSERT INTO TESTDB.EMPLOYEE(EMPNO, EMPNM, DEPT, RANK, USERNAME)" +
" VALUES" + "(?, ?, ?, ?, ?)";
preparedStatement = connection.prepareStatement(compiledQuery);
for(int index = 1; index <= records; index++) {
preparedStatement.setInt(1, index);
preparedStatement.setString(2, "empo number-"+index);
preparedStatement.setInt(3, index+100);
preparedStatement.setInt(4, index+200);
preparedStatement.setString(5, "usernames");
preparedStatement.addBatch();
}
long start = System.currentTimeMillis();
int[] inserted = preparedStatement.executeBatch();
long end = System.currentTimeMillis();
System.out.println("total time taken to insert the batch = " + (end - start) + " ms");
System.out.println("total time taken = " + (end - start)/records + " s");
preparedStatement.close();
connection.close();
return inserted;
} catch (SQLException ex) {
System.err.println("SQLException information");
while (ex != null) {
System.err.println("Error msg: " + ex.getMessage());
ex = ex.getNextException();
}
throw new RuntimeException("Error");
}
}
Метрики для пакета из 100 транзакций следующие
total time taken to insert the batch = 127 ms
и для 1000 транзакций
total time taken to insert the batch = 341 ms
Таким образом, выполнение 100 транзакций в ~5000ms
(с одним trxn за раз) сокращается до ~150ms
(с пакетом из 100 записей).
ПРИМЕЧАНИЕ - Игнорируйте мою сеть, которая работает очень медленно, но значения показателей будут относительными.
Ответ 3
Statement
предоставляет вам следующую опцию:
Statement stmt = con.createStatement();
stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1000, 260)");
// submit a batch of update commands for execution
int[] updateCounts = stmt.executeBatch();
Ответ 4
Очевидно, что вам придется выполнить бенчмарк, но через JDBC выполнение нескольких вставок будет намного быстрее, если вы используете PreparedStatement, а не Statement .