Hintergrund
Unsere Herausforderung war es, Daten so schnell wie möglich in eine SQLite-Datenbank einzufügen. Im Internet findet man viele Informationen zur Insert-Performance. Die meisten Informationen sind bereits einige Jahre alt. SQLite hat sich verbessert. Die Treiber haben sich verbessert. Java hat sich verbessert.
Viele Informationen, die wir fanden, waren nicht mehr zutreffend oder waren langsamer, als gar nichts zu optimieren.
Deshalb haben wir die Insert-Performance selbst benchmarkt. Unsere Ergebnisse sind etwas überraschend und unterscheiden sich von dem, was derzeit z.B. auf Stack Overflow empfohlen wird (https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite).
Setup
- Künstliche Daten. 5 Spalten mit Text sollen 5 Millionen Mal eingefügt werden.
- Wir haben den Benchmark auf einem Mac M1 Laptop durchgeführt.
- Ergebnis ist eine SQLite-Datenbank mit 160MB.
- sqlite-jdbc-3.41.0.1.
- Java OpenJDK 19.
Den Code finden Sie unter https://github.com/raphaelbauer/java-sqlite-insert-benchmark . Klonen Sie einfach das Repository und führen Sie die Datei BenchmarkOptionsToInsertIntoSqlite.java aus. Der gesamte Code befindet sich in dieser einen Datei.
Einschränkungen
- Das ist kein echter Benchmark wie JMH. Aber als Faustregel sollte es gut funktionieren.
- Sie könnten mit anderen Betriebssystemen und Dateisystemen andere Ergebnisse erhalten.
Fazit
- Verwenden Sie eine große Transaktion.
- Statements sind etwas schneller als Prepared Statements - aber für alle praktischen Zwecke sollten Sie Prepared Statements verwenden, um SQL Injections zu vermeiden.
- Konfiguration von SQLite (Synchronisierungsmodus, Speicher etc.) brachte keinen messbaren Vorteil.
- Batching brachte KEINEN Vorteil.
Ergebnisse und Insert-Performance
- Keine Transaktion war 1000 Mal langsamer als eine einzelne Transaktion.
- Ein einfaches Statement in einer großen Transaktion kann 833.333 Zeilen pro Sekunde auf meinem Rechner schreiben.
- Ein Prepared Statement in einer großen Transaktion kann 714.285 Zeilen pro Sekunde auf meinem Rechner schreiben.
- Ein Prepared Statement in einer großen Transaktion mit Batches von 1000 kann 500.000 Zeilen pro Sekunde auf meinem Rechner schreiben.
Einige Anmerkungen:
- Es ist überraschend, dass Batching langsamer ist als ohne Batching. Wir haben verschiedene Batch-Größen ausprobiert (1000, 10000, 100000) - es ändert sich nicht viel.
- Wir haben viele PRAGMA-Konfigurationen auf SQLite-Ebene ausprobiert. Wir konnten keinen großen Unterschied mit irgendwelchen PRAGMA-Konfigurationen feststellen.
Fragen
Frage: Was ist der schnellste Weg, in eine SQLite-Datenbank zu schreiben?
- Einfache Statements, eine Transaktion, kein Batching.
- Aus praktischen Gründen - weil Sie keine SQL Injections haben möchten - sollten Sie einfache Prepared Statements verwenden, eine Transaktion, kein Batching.
Frage: Bringen SQLite-Konfigurationsoptionen einen Vorteil?
Kurz gesagt: Nein.
Wir haben verschiedene Variationen des Folgenden ausprobiert:
SQLiteConfig config = new SQLiteConfig();
config.setJournalMode(SQLiteConfig.JournalMode.OFF);
config.setSynchronous(SQLiteConfig.SynchronousMode.OFF);
config.setLockingMode(SQLiteConfig.LockingMode.EXCLUSIVE);
config.setTempStore(SQLiteConfig.TempStore.MEMORY);
config.setPragma(SQLiteConfig.Pragma.MMAP_SIZE, "30000000000");
Es hat sich in Bezug auf die Schreibperformance nichts Wesentliches geändert.
Frage: Können wir die Arbeit nicht einfach parallelisieren?
Nein. Ein Insert sperrt die Tabelle. Sie können nur eine Verbindung zum Schreiben in die Datenbank zu einem bestimmten Zeitpunkt verwenden. Mehrere Schreibvorgänge blockieren sich gegenseitig.
Frage: Was hat den größten Einfluss auf die Performance?
Die Verwendung einer Transaktion.
Führen Sie alle Insert-Befehle innerhalb einer großen Transaktion aus. Keine Transaktion zu verwenden ist 1000x langsamer als umgekehrt. Verwenden Sie unbedingt etwas wie connection.setAutocommit(false) - und dann connection.commit() am Ende.
Mehr
- Bild oben vom großartigen Marc Sendra Martorell
