SQLite, Optimierungen (PRAGMA)

Im Zuge der Organisation der Twitter-Datenbanken (SQLite) hat sich hier ein Volumen von ~150GB angesammelt und das verlangt in der nachträglichen Optimierung etwas Beachtung, wenn bspw. die Spalten optimiert werden müssen.

Folgende Befehle sind relevant:

  • PRAGMA temp_store = 1;
  • pragma temp_store_directory = ‚f:\‘

Der erste legt die Temp-Dateien auf die Festplatte und der 2. Befehl definiert den konkreten Ordner.

SQLite: Distinct auf mehrere Spalten?

Im Laufe der Arbeiten am Konzept des Projektes „InstaLOC 2.0“ suchte ich nach einer Distinctausgabe über >1 Spalten. Konkret ging es hier um eine nachträgliche Ausgabe von Location-IDs zwecks Nachcrawling kompletter Länder.

Die Abfrage auf die Datenbankdatei „locplain_big.db“ lautet:

select distinct name, lat, long from locations where country_code = 'cn';

„cn“ lässt sich beliebig austauschen.

SQLite, Delphi und Unicode

Gestern bereinigte ich einen Bug in den Systemen von „InstaLOC“. Hierbei ging es um eine Aufbereitung der Locationdatensätze (1.7Mio) in eine leichter lesbare Form. Aufgefallen war mir, dass bspw. chinesische Zeichen in Form von „???“ beim Auslesen und Übertragen dargestellt wurden. Dies ist das Unicode-Problem und meine Lösung war simpel: Wechsel der Spaltenvariable von „VARCHAR“ auf „STRING“.

SQLite: schnell die Datenbankeinträge zusammen zählen

Diverse Datenbankmonitoringprobleme erforderten das Aufzählen der Einträge. Hierfür nehme ich unter der SDK „Delphi“ folgenden Befehl:

select MAX(_ROWID_) from tags;

Unter Windows 10 und mit einem relativ modernen Computer verlangt diese Anweisung ca. 25 Sekunden bei Abfrage(n) aus der ca. 32GB großen Hashtagdatenbankdatei.

SQLite, Performance steigern

Die Ausgangslage ist hier eine 10GB große SQLitedatenbankdatei, welche aus dem Interaktionen-Analyse-Projekt stammt.
Testquery ist:
select count (*) from interaktionen.interakt;
Abfragedauer: 30 Sekunden

Anpassung der Datei auf
PRAGMA synchronous=off

Abfragedauer nach Anpassung: 3 Sekunden.

Weitere Infos und Links:
http://blog.devart.com/increasing-sqlite-performance.html
https://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning
https://sqlite.org/optoverview.htmlhttp://codificar.com.br/blog/sqlite-optimization-faq/
https://dba.stackexchange.com/questions/153876/necessary-tuning-settings-for-sqlite-databases
https://stackoverflow.com/questions/784173/what-are-the-performance-characteristics-of-sqlite-with-very-large-database-file

Tabellen, Daten etc. aus einer SQLite-Datenbank exportieren

Auf der Suche nach entsprechenden Möglichkeiten entdeckte ich das folgende Tool:
SQLite Export
Diese interessante Anwendung exportiert aus unserer Hashtagdatenbank ca. 70 Mio Datensätze in die Formate xls, html, txt und csv innerhalb von maximal 10 Minuten.

Eine weitere und freie Anwendung ist das „SQLiteStudio„. Diese erlaubt, neben den Exportfunktionen, auch das komplette Management einer SQLite-Datei, sowie auch den Import via CSV.