java - batch - Manera eficiente de hacer INSERTOS por lotes con JDBC

insert batch mysql java

Question

En mi aplicación, necesito hacer muchos INSERTOS. Es una aplicación Java y estoy usando JDBC simple para ejecutar las consultas. El DB es Oracle. Sin embargo, he habilitado el procesamiento por lotes, por lo que me ahorra latencias de red para ejecutar consultas. Pero las consultas se ejecutan en serie como INSERT independientes:

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)

Me preguntaba si la siguiente forma de INSERT podría ser más eficiente:

insert into some_table (col1, col2) values (val1, val2), (val3, val4), (val5, val6)

es decir, colapsando múltiples INSERT en uno.

¿Algún otro consejo para hacer INSERTOS por lotes más rápido?

Answer 1

Inserción de lote usando declaración

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();
}
}

Answer 2

¿Qué hay de usar la instrucción INSERT ALL?

INSERT ALL
INTO table_name VALUES ()
INTO table_name VALUES ()
...
SELECT Statement;

Recuerdo que la última declaración seleccionada es obligatoria para que esta solicitud tenga éxito. No recuerdo por qué sin embargo. También podría considerar usar PreparedStatement . ¡muchas ventajas!

Farid

Answer 3

Aunque la pregunta es cómo insertar eficientemente en Oracle utilizando JDBC , actualmente estoy jugando con DB2 (en mainframe de IBM), la inserción conceptual sería similar, así que pensé que sería útil ver mis métricas entre

• insertando un registro a la vez

• insertar un lote de registros (muy eficiente)

Aquí ve las métricas

1) Insertar un registro a la vez

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();
}
}
}

Las métricas para 100 transacciones:

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

La primera transacción toma alrededor de 120-150ms que es para el análisis de la consulta y luego la ejecución, las transacciones posteriores solo toman alrededor de 50ms . (Que todavía es alto, pero mi base de datos está en un servidor diferente (necesito solucionar problemas de la red))

2) Con inserción en un lote (eficiente) - logrado por 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");
}
}

La métrica para un lote de 100 transacciones es

total time taken to insert the batch = 127 ms

y para 1000 transacciones

total time taken to insert the batch = 341 ms

Entonces, hacer 100 transacciones en ~5000ms (con un trxn a la vez) se reduce a ~150ms (con un lote de 100 registros).

NOTA: Ignore mi red, que es súper lenta, pero los valores de las medidas serían relativos.

Answer 4

Deberá comparar, obviamente, pero a través de JDBC la emisión de insertos múltiples será mucho más rápida si usa un Estado Preparado en lugar de un Estado.

Answer 5

Esta es una mezcla de las dos respuestas anteriores:

 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();

Answer 6

La Statement le da la siguiente opción:

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();

Answer 7

Puede usar addBatch y executeBatch para insertar por lotes en java Ver el ejemplo: Insertar por lotes en Java

Answer 8

Usar PreparedStatements será MUCHO más lento que las declaraciones si tiene bajas iteraciones. Para obtener un beneficio de rendimiento del uso de PrepareStatement en una instrucción, debe usarlo en un bucle donde las iteraciones sean al menos 50 o más.