webentwicklung-frage-antwort-db.com.de

Batch INSERTS mit JDBC auf effiziente Weise

In meiner App muss ich viele INSERTS machen. Es ist eine Java-App und ich verwende einfaches JDBC, um die Abfragen auszuführen. Die DB ist Oracle. Ich habe das Batching jedoch aktiviert, so dass mir Netzwerklatenzen beim Ausführen von Abfragen erspart werden. Die Abfragen werden jedoch seriell als separate INSERTs ausgeführt:

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)

Ich habe mich gefragt, ob die folgende Form von INSERT möglicherweise effizienter ist:

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

d. h. mehrere INSERTs zu einem zusammenfassen.

Gibt es noch andere Tipps, um Batch-INSERTs schneller zu machen?

53
Aayush Puri

Dies ist eine Mischung aus den beiden vorherigen Antworten:

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

Obwohl die Frage mit Oracle effizient mit JDBC einfügt, spiele ich derzeit mit DB2 (auf IBM-Mainframe). Das Einfügen von Konzepten wäre jedoch ähnlich

  • einfügen eines Datensatzes zu einem Zeitpunkt

  • einfügen von Datensätzen (sehr effizient)

Hier gehen die Metriken

1) Ein Datensatz zu einem Zeitpunkt einfügen

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

Die Metriken für 100 Transaktionen: 

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

Die erste Transaktion umfasst 120-150ms, die für die Abfrageanalyse ist, und dann die Ausführung. Die nachfolgenden Transaktionen nehmen nur 50ms auf. (Was immer noch hoch ist, aber meine Datenbank befindet sich auf einem anderen Server (ich muss das Netzwerk beheben))

2) Beim Einfügen in eine Charge (effizienter) - erreicht durch 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");
    }
}

Die Metriken für einen Stapel von 100 Transaktionen sind 

total time taken to insert the batch = 127 ms

und für 1000 Transaktionen

total time taken to insert the batch = 341 ms

Die Ausführung von 100 Transaktionen in ~5000ms (mit jeweils einem Trxn) wird auf ~150ms (mit einem Stapel von 100 Datensätzen) verringert.

HINWEIS - Ignoriere mein Netzwerk, das extrem langsam ist, aber die Metrikwerte wären relativ.

17
prayagupd

Die Variable Statement gibt Ihnen die folgende Option:

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

Sie müssen natürlich einen Benchmark erstellen, aber wenn JDBC mehrere Einfügungen herausgibt, ist dies viel schneller, wenn Sie ein PreparedStatement anstelle einer Anweisung verwenden.

4
Burleigh Bear

Mit diesem rewriteBatchedStatements-Parameter können Sie die Stapeleinfügung noch beschleunigen.

sie können hier über den Parameter lesen: MySQL und JDBC mit rewriteBatchedStatements = true

1
Alex Stanovsky

Sie können addBatch und executeBatch für Batch-Insert in Java verwenden. Siehe Beispiel: Batch-Insert in Java

0
user1454294

Wie wäre es mit der INSERT ALL-Anweisung?

INSERT ALL

INTO table_name VALUES ()

INTO table_name VALUES ()

...

SELECT Statement;

Ich erinnere mich, dass die letzte Auswahlanweisung obligatorisch ist, damit diese Anforderung erfolgreich ist. Erinnern Sie sich nicht an den Grund dafür . Sie könnten stattdessen auch PreparedStatement verwenden. viele vorteile! 

Farid

0
Farid

In meinem Code habe ich keinen direkten Zugriff auf das 'preparStatement', daher kann ich batch nicht verwenden. Ich übergebe nur die Abfrage und eine Liste von Parametern. Der Trick besteht jedoch darin, eine Einfügeanweisung mit variabler Länge und eine LinkedList von Parametern zu erstellen. Der Effekt ist derselbe wie im oberen Beispiel mit variabler Parametereingangslänge. Siehe unten (Fehlerprüfung ausgelassen) . Angenommen, 'myTable' hat 3 aktualisierbare Felder: f1, f2 und f3

String []args={"A","B","C", "X","Y","Z" }; // etc, input list of triplets
final String QUERY="INSERT INTO [myTable] (f1,f2,f3) values ";
LinkedList params=new LinkedList();
String comma="";
StringBuilder q=QUERY;
for(int nl=0; nl< args.length; nl+=3 ) { // args is a list of triplets values
    params.add(args[nl]);
    params.add(args[nl+1]);
    params.add(args[nl+2]);
    q.append(comma+"(?,?,?)");
    comma=",";
}      
int nr=insertIntoDB(q, params);

in meiner DBInterface-Klasse habe ich:

int insertIntoDB(String query, LinkedList <String>params) {
    preparedUPDStmt = connectionSQL.prepareStatement(query);
    int n=1;
    for(String x:params) {
        preparedUPDStmt.setString(n++, x);
    }
    int updates=preparedUPDStmt.executeUpdate();
    return updates;
}
0
user3211098