В моем приложении мне нужно сделать много INSERTS. Это приложение Java, и я использую простой JDBC для выполнения запросов. БД является Oracle. Тем не менее, я включил пакетную обработку, поэтому он сохраняет мне сетевые задержки для выполнения запросов. Но запросы выполняются последовательно как отдельные INSERT:
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:
insert into some_table (col1, col2) values (val1, val2), (val3, val4), (val5, val6)
то есть. сворачивая несколько INSERT в один.
Какие-нибудь другие советы для ускорения запуска пакетов INSERT?
Это сочетание двух предыдущих ответов:
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();
Несмотря на то, что в вопросе спрашивается, что эффективно внедряется в 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 записей).
ПРИМЕЧАНИЕ. - Игнорируйте мою сеть, которая очень медленная, но значения показателей будут относительными.
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();
Вам, конечно, придется ориентироваться, но над JDBC, выпускающим несколько вложений, будет намного быстрее, если вы используете PreparedStatement, а не выражение.
Как насчет использования инструкции INSERT ALL?
INSERT ALL
INTO table_name VALUES ()
INTO table_name VALUES ()
...
SELECT Statement;
Я помню, что последний оператор select является обязательным, чтобы сделать этот запрос успешным. Не помню, почему. Возможно, вам стоит использовать PreparedStatement. много преимуществ!
Фарид
Вы можете использовать addBatch и executeBatch для пакетной вставки в java См. пример: Пакетная вставка в Java
Использование PreparedStatements будет МНОГО медленнее, чем Statement, если у вас низкие итерации. Чтобы получить преимущество в производительности от использования PrepareStatement над оператором, вам нужно использовать его в цикле, где итерации не менее 50 или выше.
int a= 100;
try {
for (int i = 0; i < 10; i++) {
String insert = "insert into usermaster"
+ "("
+ "userid"
+ ")"
+ "values("
+ "'" + a + "'"
+ ");";
statement.addBatch(insert);
System.out.println(insert);
a++;
}
dbConnection.commit();
} catch (SQLException e) {
System.out.println(" Insert Failed");
System.out.println(e.getMessage());
} finally {
if (statement != null) {
statement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}