MySQL Batch Insert oder MultiRow Insert, Performancevergleich.

Um viele Daten in eine MySQL Tabelle einzufügen, bedient man sich selten einzelner Statements. Selbst wenn diese als Prepared Statement bereits vorkompiliert werden durch den Datenbankserver, ist es dennoch ein enormer Performaneverlust, diese immer einzeln abzusetzen.

Die in diesem Falle gängigste Methode besteht darin, einen sogenannten Batch-Insert durchzuführen.

Dieser sieht im wesentlich so aus, dass einfach jeder Insert als eine separate Transaktion zu einem großen Batchlauf zugeordnet wird, welcher nachher in einer Transaktion abgesetzt wird.

Beispiel:

 for (int i=0; i < numberOfEntries; i++) {
     preparedStmt.setString (1, "Pizza" + i);
     preparedStmt.setString (2, "mit Kaese" + i);
     preparedStmt.setDate   (3, orderDatum);
     preparedStmt.setBoolean(4, false);
     preparedStmt.setInt    (5, 5000);
     preparedStmt.addBatch();
 }
preparedStmt.executeBatch();

Diese Methode gilt als schön und elegant, dazu noch ziemlich performant. Der weitere Vorteil ist die intergrierte Transaktionssicherheit: geht eines der Statement fehl, wird der gesamte Batch-Lauf zurückgerollt. (Was uns natürlich nicht von einer großen Transaktionsklammer über die gesamte Transation befreit!)

Wenig bekannt ist aber, dass es noch eine zweite Möglichkeit gibt. Weniger elegant und schön, dafür aber möglicherweise schneller. Und zwar das sogenannte Multirow-Insert.

Das Grundprinzip hier ist einfach: man erstellt ein Statement als String und fügt so viele der ? hinzu, wieviele Werte eingetragen werden müssen. Danach erstellt daraus ein Prepared Statement und führt dieses in einer einzelnen Transaktion aus.

Beispiel:

StringBuilder query = new StringBuilder("insert into additionaldata (gericht, details, date_ordered, is_paid, num_points) values");
    for (int i=1; i <= numberOfEntries; i++) {
        query.append("(?, ?, ?, ?, ?)");
        if (i != numberOfEntries)
            query.append(", ");
    }
    query.append(";");
    PreparedStatement preparedStmt = con.prepareStatement(query.toString());

 

Nun schauen wir uns am Codebeispiel, was schneller ist.

Dazu erstellen wir die folgende Klasse, welche unseren beiden Codesnippets von oben beinhaltet:

package net.digitalermaschinenraum.databaseperformance;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;

public class DatabasePerformanceExample {

       private static final int REPEAT = 38;

       public static void main(String[] args)
       {
           try
           {
             // create a mysql database connection
             Class.forName("com.mysql.jdbc.Driver").newInstance();
             String connectionCommand = "jdbc:mysql://localhost/PERFTEST?user=root&password=dbpassword";
             Connection conn = DriverManager.getConnection(connectionCommand);

             conn.setAutoCommit(false);

             long startInsertBatch = System.nanoTime();
             insertBatch(REPEAT, conn);
             conn.commit();
             long endInsertBatch = System.nanoTime();

             System.out.println("1. job: " + (endInsertBatch - startInsertBatch));

             long startInsertMultiRow = System.nanoTime();
             insertMultiline(REPEAT, conn);
             conn.commit();
             long endInsertMultiRow = System.nanoTime();

             System.out.println("2. job: " + (endInsertMultiRow - startInsertMultiRow));

             conn.close();
           }
           catch (Exception e)
           {
             System.err.println("Got an exception!");
             System.err.println(e.getMessage());
           }
         }

      private static void insertBatch(int numberOfEntries, Connection con) throws SQLException {

    	  String query = " insert into orders(gericht, details, ordered, paid, num_points)"
                      + " values (?, ?, ?, ?, ?)";
          PreparedStatement preparedStmt = con.prepareStatement(query);
          Calendar calendar = Calendar.getInstance();

          java.sql.Date orderDatum = new java.sql.Date(calendar.getTime().getTime());
            

              for (int i=0; i < numberOfEntries; i++) {
            	  preparedStmt.setString (1, "Pizza" + i);
            	     preparedStmt.setString (2, "mit Kaese" + i);
            	     preparedStmt.setDate   (3, orderDatum);
            	     preparedStmt.setBoolean(4, false);
            	     preparedStmt.setInt    (5, 5000);
                   
                    preparedStmt.addBatch();
              }

              preparedStmt.executeBatch();

       }

      private static void insertMultiline(int numberOfEntries, Connection con) throws SQLException {

    	  StringBuilder query = new StringBuilder("insert into orders(gericht, details, ordered, paid, num_points) values");
          for (int i=1; i <= numberOfEntries; i++) {

        	  query.append("(?, ?, ?, ?, ?)");
              if (i != numberOfEntries)
                    query.append(", ");
           }
           query.append(";");

           PreparedStatement preparedStmt = con.prepareStatement(query.toString());
           Calendar calendar = Calendar.getInstance();
      
           java.sql.Date orderDatum = new java.sql.Date(calendar.getTime().getTime());

            

           for (int i=0; i < numberOfEntries; i++)  {
              int pos = 5*i;
              preparedStmt.setString (pos+1, "Spaghetti" + i);
              preparedStmt.setString (pos+2, "mit Parmesan" + i);
              preparedStmt.setDate   (pos+3, orderDatum);
              preparedStmt.setBoolean(pos+4, false);
              preparedStmt.setInt    (pos+5, 5000);
          }
         preparedStmt.executeUpdate();

             

       }

}

Kurze Erklärung:

1. Erst einmal bauen wir ganz klassisch eine Datenbankverbindung auf, die wir für beide Fälle verwenden werden. Um eine Verzerrung durch Zwischencommits auszuschalten, wird der Autocommit für diese Connection ausgeschaltet.

2. Die Funktion insertBatch fügt nun insgesamt 38 Datensätze als Batch hinzu und commited die Transaktion.

3. Die Funktion insertMultiRow fügt dieselben Datensätze in Form eines einzelnen MultiRow-Insert und commited diese ebenfalls.

Für beide Transaktionen wird ein nano-Zeitstempel zwecks besserer Vergleichbarkeit genommen.

Jetzt müssen wir nur noch die Datenbanktabelle erstellen:

create table orders (
  id int unsigned auto_increment not null,
  gericht varchar(32) not null,
  details varchar(32) not null,
  ordered timestamp default now(),
  paid boolean,
  num_points int,
  primary key (id)
);

Wie sieht das Ergebnis aus?

Der Batch-Insert dauerte 684312738 ns, während der MultiRow-Insert lediglich 28274453 ns in Anspruch genommen hatte, also beträchtlich schneller war! Der Batch-Insert hatte satte 24 mal so lange gedauert, wie der MultiRow!

Dennoch hat ein MultiRow auch ein paar Nachteile, die nicht verschwiegen werden dürfen.

Da ein Batch-Insert eine eigene Transaktion ist, ist es auch deutlich besser lesbar und einfacher auszuwerten, als der sehr lange String eines MultiRow-Inserts.

Ein weiterer Nachteil besteht darin, dass Batch-Insert durchaus als bereits vorab vorbereitetes PreparedStatement abgelegt werden kann und lediglich mit Daten zu füllen ist. MultiRow muss hingegen, falls die Anzahl der Datensätze nicht bekannt ist, jedesmal mühevoll zusammengebaut werden. Ob dies den Performancevorteil wettmacht, entscheidet der Anwendungsfall.

Was ist also besser? Beides sind sehr gute Möglichkeiten, die, je nach Einsatzzweck abgewogen werden sollten. In hochperformantem Umfeld sollte der wenig bekannte MultiRow durchaus in Betracht gezogen werden. In Low-Frequency Programmierung ist m.E. Batch als besser lesbar den Vorteil zu geben.

 

Leave a Reply

Your email address will not be published. Required fields are marked *