SQLite - Möglichkeit Abfrage beschleunigen?

  • 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://www.sqlite.org/fts5.html

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

  • 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.

  • 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: