SQLite - Möglichkeit Abfrage beschleunigen?

    • Offizieller Beitrag

    Hi,

    ich habe mir Free German Dictionary heruntergeladen und daraus eine SQLite Datenbank erstellt germandict.db (Tabelle: dictionary, Feld: word).

    Die Datenbank möchte ich zum Überprüfen von Texten bzw. bei Teileingaben für Wortvorschläge nutzen. Dazu habe ich 3 Funktionen erstellt die per SELECT Statement auf Übereinstimmung am Wortanfang oder Wortende prüfen oder auf im Wort enthalten. Die Datenbank enthält ~1,9 Millionen Einträge und auf meinem Rechner dauert eine Abfrage dann ca. 3,5 s.

    Lässt sich das evtl. noch beschleunigen?

    Hier der Code, DB ist zu groß für den Anhang, DL hier:

  • Du verwendest Wildcards am Anfang und am Ende des Suchstrings.
    Das führt zu einem Full Table Scan -> deshalb ist die Abfrage derart langsam.

    Da die Wildcards vor und nach dem Suchwort stehen, bringt ein Index hier nichts (und auch spezielle Key-Value-Datenbanken wie z.B. Redis hätten das gleiche Problem).

    Du kannst daher eigentlich nur versuchen die Ausführungsgeschwindigkeit zu erhöhen in dem du die Datenbank inmemory machst oder ähnliches.
    Auch die Aktivierung des Exclusive-Modus wäre anzuraten (PRAGMA locking_mode=EXCLUSIVE;).

    Alternativ gibt es dann noch Extensions welche diesen Sonderfall versuchen effektiver zu handeln: https://autoit.de

    Hierzu müsstest du dir aber erst noch dein eigenes SQLite mit der entsprechenden Erweiterung kompilieren.

    • Offizieller Beitrag

    in dem du die Datenbank inmemory machst oder ähnliches.

    Ja, das hats gebracht!

    Ich erstelle beim Skriptstart eine leere :memory: DB und binde die Wort-DB mit ATTACH an. Dann schaufele ich die Daten in die memory-DB (ca. 1,5 s). Das Abfragen aus der memory-DB dauert dann etwa 1 s oder weniger. :thumbup:

  • Sehr schön - gute Idee - Danke! ;)

    Ich habe mir das Script noch ein wenig umgeschrieben... jetzt mit nur einer Funktion und die Suche kann nun auch "case sensitiv" sein.

    PS: Den Fehler in der Funktion __TableClearAll hast du sicher schon bemerkt...

    Edit: Habe das Script noch ein wenig gekürzt.

    Einmal editiert, zuletzt von Bitnugger (24. April 2018 um 00:02)

    • Offizieller Beitrag

    PS: Den Fehler in der Funktion __TableClearAll hast du sicher schon bemerkt...

    War ein Aufmerksamkeitstest... ;)

    Den Gedanken alles in eine Funktion zu packen hatte ich am Anfang auch. Habe mich dann aber dagegen entschieden. Ist persönlicher Geschmack. Wenn möglich vermeide ich Funktionen mit einer Vielzahl Parameter und wrappe lieber in mehrere kurze Aufrufe.

    Da habe ich noch einen Vorschlag, um die Parameterübergabe zu minimieren. Da Tabellen- und Feldname (zumindest in dieser Anwendungsform) bei jedem Aufruf identisch sind, ist es sinnvoll diese Beim erstmaligen Aufruf der Funktion zu übergeben. Danach kann man stattdessen -1 verwenden.

  • War ein Aufmerksamkeitstest...

    Glaube ich dir aufs Wort... aber Vorsicht - denn nach dem 1001.ten Test musst du deinen Nick in @BugMaker ändern. :rofl:

    Den Gedanken alles in eine Funktion zu packen hatte ich am Anfang auch. Habe mich dann aber dagegen entschieden. Ist persönlicher Geschmack. Wenn möglich vermeide ich Funktionen mit einer Vielzahl Parameter und wrappe lieber in mehrere kurze Aufrufe.

    Solange das Script noch gut überschaubar ist, mache ich es auch so... aber mit zunehmender Größe verschmelze ich dann gerne die (Mini-)Funktionen, soweit es sinnvoll ist.

    Da habe ich noch einen Vorschlag, um die Parameterübergabe zu minimieren. Da Tabellen- und Feldname (zumindest in dieser Anwendungsform) bei jedem Aufruf identisch sind, ist es sinnvoll diese Beim erstmaligen Aufruf der Funktion zu übergeben. Danach kann man stattdessen -1 verwenden.

    Ja, das ist eine gute Idee... habe es fast 1:1 übernommen... noch zwei Parameter ($_sValue und $_iSelect) hinzugenommen und damit auch gleich (m)einen blöden Fehler ($_sValue in Zeile 8 konnte "" sein) beseitigt.

    Hier die korrigierte und erweiterte Version:

    germandict.au3

    PS: Da könnte man jetzt auch noch leicht weitere Dictionarys hinzufügen... z.B. Englisch, Klingonisch, ... 8o

    • Offizieller Beitrag

    EDIT:

    Ich muss nochmal über das Skript drüberschauen. Aus irgendeinem Grund wurde die DB nicht gespeichert. Melde mich wenn korrigiert.

    Möglicherweise sind in der aktuellen Version (habe ich eben zum ersten mal genutzt) Änderungen in den internen Funktionen, die die SQLite.au3 nicht kennt. Mal durchgraben.

    EDIT 2:

    Fehler gefunden: UNBEDINGT die 32-bit Version verwenden.

    Weißt Du noch, wie / mit welchem Programm Du damals die SQLite Datenbank erzeugt hast?

    Na mit AutoIt! :rofl:

    Habe das Skript zwar nicht mehr gehabt, aber schnell nochmal erstellt:

    Bei mir auf dem Laptop hat es rund 30 Sekunden gebraucht.

    • Offizieller Beitrag

    Ich erstelle beim Skriptstart eine leere :memory: DB und binde die Wort-DB mit ATTACH an. Dann schaufele ich die Daten in die memory-DB (ca. 1,5 s).

    So, mit neuen Funktionen in der sqlite3.dll kann man das deutlich beschleunigen: Jetzt nur noch 0,1 Sekunden!!

    Statt mit INSERT die DS rüberzuschaufeln kann man jetzt die "sqlite3_backup_..." Funktionen nutzen. Da in der SQLite.au3 noch nicht enthalten, habe ich diese mal erstellt.

    Das Abfangen der Return-SQL-Codes aus der Init-Funktion ist mir nicht klar. Aber bei sauber übergebenen Parametern ist das ohne Problem.  Gelöst, s. hier.

    Hier die Anwendung mit der "germandict.db":