Effizienter Weg, um sicherzustellen, dass eindeutige Zeilen in SQLite3

Ich bin mit SQLite3 in einem meiner Projekte und ich brauche, um sicherzustellen, dass die Zeilen in eine Tabelle eingefügt werden, sind einzigartig im Hinblick auf eine Kombination von einigen Ihrer Spalten. In den meisten Fällen, in die Zeilen eingefügt werden, unterscheiden sich in dieser Hinsicht, aber im Fall einer übereinstimmung die neue Zeile aktualisieren/ersetzen der vorhandenen.

Die offensichtliche Lösung war die Verwendung eines zusammengesetzten Primärschlüssel, mit einer Konflikt-Klausel zu verarbeiten Kollisionen. Thefore dies:

CREATE TABLE Event (Id INTEGER, Fld0 TEXT, Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT, Fld4 TEXT, Fld5 TEXT, Fld6 TEXT);

wurde diese:

CREATE TABLE Event (Id INTEGER, Fld0 TEXT, Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT, Fld4 TEXT, Fld5 TEXT, Fld6 TEXT, PRIMARY KEY (Fld0, Fld2, Fld3) ON CONFLICT REPLACE);

Bedeutet ja erzwingen der Eindeutigkeit, wie ich es brauche. Leider ist diese änderung auch für die Leistungseinbußen, die weit über das hinaus, was ich erwartet hatte. Ich habe
ein paar tests mit der sqlite3 Befehlszeilen-Dienstprogramm, um sicherzustellen, dass es nicht einen Fehler in meinem restlichen code. Die tests betreffen die Eingabe 100.000 Zeilen, entweder in einer einzigen
Transaktion oder in 100 Transaktionen von 1.000 Zeilen jeweils. Ich erhielt die folgenden Ergebnisse:

                                | 1 * 100,000   | 10 * 10,000   | 100 * 1,000   |
                                |--------------- | --------------- | ---------------|
                                | Time  | CPU   | Time  | CPU   | Time  | CPU   |
                                | (sec) | (%)   | (sec) | (%)   | (sec) | (%)   |
-------------------------------- | ------- | ------- | ------- | ------- | ------- | -------|
No primary key                  | 2.33  | 80    | 3.73  | 50    | 15.1  | 15    |
-------------------------------- | ------- | ------- | ------- | ------- | ------- | -------|
Primary key: Fld3               | 5.19  | 84    | 23.6  | 21    | 226.2 | 3     |
-------------------------------- | ------- | ------- | ------- | ------- | ------- | -------|
Primary key: Fld2, Fld3         | 5.11  | 88    | 24.6  | 22    | 258.8 | 3     |
-------------------------------- | ------- | ------- | ------- | ------- | ------- | -------|
Primary key: Fld0, Fld2, Fld3   | 5.38  | 87    | 23.8  | 23    | 232.3 | 3     |

Meine Anwendung derzeit führt die Geschäfte von höchstens 1000 Zeilen und ich war überrascht von der 15-Fach Tropfen in der Leistung. Ich erwartete höchstens ein 3-Fach Tropfen in Durchsatz und einem Anstieg der CPU-Auslastung, wie in den 100k-Transaktion Fall. Ich denke, die Indizierung beteiligt bei der Aufrechterhaltung der primary key-Einschränkungen erfordert einen erheblich größeren Zahl von synchronen DB-Operationen, so dass meine Festplatten der Engpass in diesem Fall.

Mit WAL-Modus hat eine gewisse Wirkung haben - eine performance-Steigerung von über 15%. Das ist leider nicht genug auf seine eigene. PRAGMA synchronous = NORMAL nicht scheinen, um eine Wirkung.

Ich könnte in der Lage sich zu erholen einige performance durch die Erhöhung des Transaktionsvolumens, aber ich würde lieber nicht tun, dass, aufgrund der erhöhten memory-Nutzung und der Sorge um die Reaktionsfähigkeit und die
Zuverlässigkeit.

Die text-Felder in jeder Zeile haben variable Längen von 250 bytes im Durchschnitt. Die query-performance spielt keine Rolle, zu viel, aber die insert-performance ist sehr wichtig. Mein code für die Anwendung ist in C und ist (sein soll) portabel, mindestens Windows und Linux.

Gibt es eine Möglichkeit zur Verbesserung der performance von Gewindeeinsätzen ohne Erhöhung der Transaktionsgröße? Entweder ist eine Einstellung in SQLite (was aber dauerhaft zwingt die DB in den asynchronen Betrieb, das ist) oder programmgesteuert in meiner Anwendung code? Zum Beispiel ist es ein Weg, um sicherzustellen, Zeile Einzigartigkeit, ohne die Verwendung eines index?

BOUNTY:

Durch die Verwendung der hashing/Indizierung Methode beschrieben, in meine eigene Antwort, ich habe es etwas Moderat den Leistungsabfall zu einem Punkt, wo es wahrscheinlich akzeptabel für meine Anwendung.
Es scheint aber, dass die Anzahl der Zeilen in der Tabelle erhöht, die Präsenz der index macht die inserts langsamer und langsamer.

Ich bin daran interessiert, jede Technik oder fine-tuning-Einstellung, die die Leistung verbessern in diesem speziellen Fall verwenden, solange es sich nicht um das hacken der SQLite3-code oder sonst nach dem Projekt zu wartbaren.

InformationsquelleAutor thkala | 2011-03-03
Schreibe einen Kommentar