Performance von SQLite optimieren?

  • Ich versuche mich gerade daran eine CSV Datenstruktur in eine SQLite DB zu wandeln. Das funktioniert auch bestens und gerade was das Durchsuchen und Filtern angeht sind die Geschwindigkeitsvorteile enorm, allerdings ist das initiale Einlesen aller Datensätze zum Befüllen eines Listviews extrem langsam verglichen mit meiner alten Variante die CSV Struktur in ein 2D Array einzulesen und darüber zeitgleich das Listview zu befüllen.


    Die Daten:
    Ich habe 6 Spalten und ungefähr 3000 Zeilen, also eine CSV Datei mit 3000 solcher Zeilen

    Code
    Spalte1,Spalte2,...,Spalte6


    Die entsprechende SQL DB hat dementsprechend ebenfalls eine Tabelle mit 6 Spalten und 3000 Zeilen.


    Die Dauer:
    Das einlesen der CSV Datei inklusive der Listview Befüllung benötigt etwa 300ms.
    Das einlesen der SQL Datei inklusive der Listview Befüllung benötigt etwa 900ms, also dreimal solange.


    Mein Bench Script:
    Anmerkung: Bei der ersten Ausführung werden die CSV und DB Datei erzeugt, was leider auf meinem System 2 Minuten dauert. Das ist letzlich aber egal, denn die CSV->DB Transformation muss ich ohnehin nur einmalig beim finalen Umstieg erledigen. thx BugFix


    Die Frage:
    Woran liegts? Kann man das in irgendeinerweise noch beschleunigen? Eine Sekunde ist mir eigentlich zu lange, zumal es in den nächsten Jahren eher mehr Daten werden und die Zeit vermutlich nochmals entsprechend ansteigen dürfte. Je nach Filterung muss das Listview immer mal wieder komplett erstellt werden, was dann jeweils in einer 1-Sekunden Pause resultieren dürfte.

  • Du schreibst jeden Datensatz einzeln in die DB, das ist schonmal eine Bremse. Und dann fehlt dir noch der "Beschleuniger": BEGIN TRANSACTION; --- COMMIT;
    Dein Skript sollte etwa so gestaltet werden:


    $sSQL = "BEGIN TRANSACTION;"
    For .....
    $sSQL &= "INSERT.... ;"
    Next
    $sSQL &= "COMMIT;"


    _SQLite_Exec(-1, $sSQL)

    Dann benötigt dein Skript nur noch einige Millisekunden.


    Edit:
    Wenn ich mich recht erinnere, ist _SQLite_FetchData() auch nicht gerade der Renner. Lies dir die Daten gleich in ein Array ( _SQLite_GetTable2d ), das dürfte die Erstellung des Listview beschleunigen.

  • Ahja stimmt das macht Sinn die Inserts zu einem SQL String zusammenzufassen und als eine Transaktion anzusehen, war schon etwas verwundert weil es wirklich lange dauert die Tabelle zu erzeugen. Letztlich aber wie gesagt eh nur ein einmaliger Import Vorgang, spätere Schreibvorgänge werden ohnehin immer nur eine Zeile betreffen. Ich habs oben mal eingebaut, ist wirklich super schnell nun, danke für den Tipp.


    Was die Funktion _SQLite_GetTable2d anbelangt. Ich hatte ursprünglich genau diese Funktion verwendet, allerdings ist diese nochmal etwas langsamer. Grund ist, dass dort im Prinzip auch nur fetchdata in einer Schleife für alle Zeilen genutzt wird, außerdem muss ich nachdem ich das Array habe nochmals komplett durch das Array laufen um letzlich das Listview zu befüllen. Dadurch dauert das ganze etwa 250ms bzw. 25% länger als wenn ich es direkt selbst mit Fetchdata realisiere und mir dadurch einen Schleifendurchlauf erspare. Ich habe auch mal diese Variante oben eingebaut.


    Vielleicht hat ja noch jemand eine Idee?

  • Hab mal noch ein wenig rumgespielt und es tatsächlich geschafft die Geschwindigkeit zu verdoppeln, trotzdem ist die CSV Variante immernoch doppelt so schnell als die SQL Lösung.


    Der Trick:


    _SQLite_Query ( $hDB, "SELECT " & StringReplace($allColumNames,"|"," || '|' || ") & " FROM " & $tablename, $hQuery )


    Durch das modifizierte Select macht SQLite bereits die Stringverkettung der Spalten. dadurch muss ich die einzelnen Zeilen Ergebnisse nur noch direkt an GUICtrlCreateListViewItem(...) übergeben und spare mir das vorherige Verketten. Wie gesagt wer noch Ideen hat immer her damit, aber langsam wird die Geschwindigkeit akzeptabel.

  • Habs mal oben eingebaut, aber wie erwartet auch keine besonders flotte Variante. Es ist zwar ein wenig schneller als meine "2DArray" Lösung mit manueller Befüllung, aber etwas langsamer als das reine "Fetch" und deutlich langsamer als "LWjoin" und "CSV".


    Code
    CSV-Methode Time: 320.907018040784
    SQL-LWjoin-Methode Time: 617.583219529994
    SQL-Fetch-Methode Time: 1058.91033191224
    SQL-2DArray_LWArrAdd-Methode Time: 1172.8774607297
    SQL-2DArray-Methode Time: 1325.62754093183
  • JSON ist doch eher aufwendiger zu parsen als eine CSV Datei, kann mir nicht vorstellen, dass das konkurrenzfähig wäre.


    Und nein muss natürlich keine SQL DB sein. Prinzipiell bin ich mit meiner CSV Struktur glücklich, aber eine SQL DB würde vieles vereinfachen und beschleunigen. In meinen realen Daten habe ich z.B. auch Integer Werte, deren Durchschnitt berechnet werden muss. Das ganze dann noch für jeden Benutzer separat, welcher in einer weiteren Spalte gespeichert ist. Außerdem soll gezählt werden wieviele Objekte einer bestimmten Art (es gibt mehrere) von einem bestimmten Benutzer stammen und noch einige andere Dinge, wie Sortierung und Filterung anhand von Monat/Jahr plus die zugehörigen Durchschnittswerte und viele weitere Filter...


    Bislang durchlaufe ich dazu das 2D-Array, welches aus der CSV erstellt wurde, teilweise muss dies zur Laufzeit mehrfach gemacht und gefilterte Kopien des Arrays erzeugt werden, was performance mässig nicht unbedingt optimal ist. Da ich das aber schon sehr weit optimiert habe läuft das so ganz ordentlich wie es ist. (viele Dinge werden im selben Schleifendurchlauf berechnet/gezählt)


    Warum dann nun SQL?


    Damit wären diese Berechnungen und Filterungen deutlich einfachher, flexibler und vorallem schneller. In meinen Tests wird der Durchschnitt aller Werte etwa 6 mal so schnell durch ein "SELECT AVG(...)" berechnet als ich das mit einem gleichwertigen Autoit Arraydurchlauf hinbekommen könnte. Die Frage ist nun natürlich ob die SQL Variante insgesamt schneller oder vielleicht doch langsamer ist, denn wie es scheint ist gerade bei großen Ergebnismengen die CSV/Array Struktur im Vorteil. Mit SQL wäre aber mein Quellcode wesentlich übersichtlicher und ich bräuchte keine komplexe Filterfunktion mehr, sondern nur noch eine handvoll abgestimmter oder flexibler SQL Querys, ggf. auch in der DB hinterlegte "Views", die dann nur noch angezeigt werden und mir die SELECT Programmierung im jeweiligen Programm fast komplett ersparen. Ich denke SQL ist wirklich optimal wenn es darum geht solche Datenmengen auszuwerten und zu verarbeiten.


    Nachdem ich nun aber etwas enttäuscht von der Performance bei der LW Erstellung war/bin überlege ich noch ob ich wirklich auf SQLite umsteigen sollte. Nutzt letztlich ja nix, wenn es dadurch insgesamt nicht schneller sondern langsamer wird.

  • ich glaube ab Version 3 unterstützt sqlite auch prepared Statements. In wiefern man diese von autoit aus nutzen kann kann ich aber nicht sagen. Wenn es klappt könnte man vor allem wiederkehrende Abfragen ziemlich beschleunigen.

  • Vielleicht kannste mir auch den Unterschied zwischen einem "prepared Statement" und einem gewöhnlichem "Statement" erklären. Ich weiß zwar, dass es das gibt (habe SQL in Verbindung mit JDBC und einer Oracle DB gelernt), aber so ganz den Vorteil eines "prepared Statements" hab ich glaube nicht verstanden. Ich habe mir das seither so vorgestellt, dass bei einem "prepared Statement" die Ergebnisstabelle vom RDBMS auch nach dem letzten Zugriff auf die Ergebnistabelle noch temporär gespeichert wird um beim erneuten Aufruf des selben Statements das Ergebnis gleich parat zu haben. Nun wenn dem so sein sollte nutzt mir das beim allerersten Zugriff nur dann was, wenn das RDBMS schon vorher lief und jemand anderes diese Anfrage gestellt hat. Das ist bei SQLite aber ja nicht der Fall, da das RDBMS bei Programmende heruntergefahren wird (kein permanent laufender zentraler Server). Ich hätte also beim Initialisieren des LW keinen Vorteil, höchstens später im Programmverlauf bei erneuter Anzeige des ungefilterten LW.


    Nehmen wir mal an ich hab das doch richtig verstanden und es ist so wie ich das beschrieben habe. Würde mir das wirklich nutzen? Geht die meiste Zeit wirklich durch die Erstellung der Ergebnistabelle verloren, oder ist es nicht viel wahrscheinlicher, dass die meiste Zeit beim Auslesen der Ergebnistabelle mit "fetchdata", also der Datenübertragung zwischen RDBMS und Autoit verloren geht? Wenn letzteres zutrifft nutzt auch ein "prepared statement" nichts.

  • Ein großer Teil an Zeit geht dadurch verloren weil der SQL-Befehlsstring geparst und interpretiert werden muss.
    Bei einem prepared Statement wird das schon einmal im Voraus erledigt und nur Platzhalter für die variablen Werte eingesetzt.
    Stark vereinfacht gesagt: normale Statements werden interpretiert - prepared Statements liegen schon im Maschinencode vor.


    Edit: Wie es aussieht entspricht _SQLite_Query statt _SQLite_Exec einem prepared statement. Wobei ich nicht sehe wie man die variabel hinbekommt - so wie es in der Hilfe steht scheinen es auch nur statische Statements zu sein weswegen der Vorteil der Wiederverwendbarkeit ja wegfällt.

  • Ich glaube du verfehlst denn Sinn eines RDBMS ;)


    Klar, wenn die Daten in Textformat vorliegen sind sie schneller aus einer Textdatei geparst, als aus einer DB.
    Hinter einer Textdatei steht ja auch kein SQL Server, der die Daten auf-, vor- und abarbeitet ;)
    Das pure Speichern von Daten ist ja nicht der Hauptgedanke eines RDBMS.


    Der Geschwindigkeitsvorteil kommt, sobald du Bedingungen hast.
    Willst du immer alle Datensätze aus der Datenbank abrufen? Wohl eher nicht :D


    Eine einzige WHERE Bedingung sollte den Geschwindigkeitsvorteil eigentlich schon deutlich machen.
    Oder auch, sollte sich deine Datenbank weiterentwickeln, mehrere Tabellen (3te. Normalform etc), JOINS,
    VIEWS wie du schon gesagt hast, Hinzufügen, ändern und Löschen von einzelnen Datensätzen oder auch größeren Datenmengen,
    darin liegt der Vorteil eines RDBMS ;)


    P.S:


    Du rufst alle Spaltennamen der Tabelle ab und speicherst sie in einem String.



    Selbes Ergebnis, aber 10mal Schneller ;)


    PRAGMA TABLE INFO: 3.01685434320743
    _SQLite_FetchNames: 0.352390055538509

  • Ich glaube du verfehlst denn Sinn eines RDBMS ;)


    Im Bench Beispiel hier definitiv, aber da die Anzeige der Daten (und durchaus auch großer Datenmengen bzw. aller Daten) nunmal ein Hauptbestandteil des realen Programms ist darf ich das natürlich bei der Frage ob sich ein Umstieg auf SQL lohnt oder auch nicht lohnt natürlich nicht vernachlässigen.



    Der Geschwindigkeitsvorteil kommt, sobald du Bedingungen hast.
    Willst du immer alle Datensätze aus der Datenbank abrufen? Wohl eher nicht :D
    Eine einzige WHERE Bedingung sollte den Geschwindigkeitsvorteil eigentlich schon deutlich machen.


    Natürlich nicht immer, aber z.B. zum Programmstart und auch immer dann wenn die Filter des Listviews zurückgesetzt werden. Manche Filter schränken auch kaum ein, daher entstehen selbst bei Filterung teilweise noch über 1000 Ergebniszeilen. Daher ist die Performance insbesondere in diesem Fall sehr wichtig. Klar wenn der Filter wirklich stark einschränkt oder nur das Ergebnis einer Berechnung (sum/avg...) relevant ist dürfte SQL deutlich im Vorteil sein, das habe ich oben aber denke ich auch schon erwähnt.


    Da mir der Vorteil also durchaus bewusst ist war eben die Frage ob ich noch Möglichkeiten habe den eher ungünstigen Fall, also viele Ergebniszeilen irgendwie zu beschleunigen. Mit 600ms kann ich im Moment leben, das Problem ist nur, dass die Daten in 1-2 Jahren den doppelten Umfang haben werden und wir dann nicht mehr von 600ms, sondern von 1,2 Sekunden reden, was eher unschön wäre und den Einsatz einer SQL Lösung aus Performance sicht in Frage stellt.



    Oder auch, sollte sich deine Datenbank weiterentwickeln, mehrere Tabellen (3te. Normalform etc), JOINS,
    VIEWS wie du schon gesagt hast, Hinzufügen, ändern und Löschen von einzelnen Datensätzen oder auch größeren Datenmengen,
    darin liegt der Vorteil eines RDBMS ;)


    Jupp, das war naürlich alles angedacht, allerdings bin ich mir noch nicht sicher ob es wirklich sinnvoll wäre eine Normalisierung vorzunehmen. Ich spare dadurch zwar Redundanz ein, allerdings gestaltet sich die Verwaltung komplexer und die Zugriffsgeschwindigkeit leidet vermutlich auch ein wenig (zumindestens hat das meine Lehrerin mal behauptet). Da ich das Thema aber prinzipiell interessant finde werde ich das auf jedenfall irgendwann mal in Angriff nehmen. Schon alleine weil ich all mein theoretisches Wissen in diesem Bereich bislang kaum praktisch anwenden konnte/musste.



    Selbes Ergebnis, aber 10mal Schneller ;)


    PRAGMA TABLE INFO: 3.01685434320743
    _SQLite_FetchNames: 0.352390055538509


    Verdammt die Funktion ist mir ganz entgangen. Hätte ich mir die Recherche auf der sqlite page sparen können. :D Letzlich aber eh nur ein Gewinn von 3ms, also nicht wirklich der Rede wert, das Listview wird ja glücklicherweise nicht tausende male neu erstellt.



    So nun zurück zum eigentlichen Problem:


    Ich habe nachwievor den Verdacht, dass die 3000 Aufrufe von FetchData (dürften jeweils Autoit DLL calls sein) den Flaschenhals darstellen und nicht die Zeit die Sqlite für die Erstellung der Ergebnistabelle oder das Parsing des SQL Befehls verschlingt. Daher hatte ich noch einen Gedanken wie man prinzipiell die Zeit massivst verkürzen könnte. Theoretisch könnte ich wie folgt vorgehen:


    1. Stored function in der DB erstellen, welche das SQL Query erledigt und sämtliche Ergebniszeilen verkettet und in einer einzigen Rückgabvariable speichert.
    2. Stored function in Autoit aufrufen, es sollte nun nur noch ein einziges FetchData nötig sein um einen riesigen String, der die ganze Tabelle enthält zu bekommen
    3. String per Autoit wieder in Zeilen zerlegen und damit das Listview befüllen


    Praktisch scheitert das Vorhaben aber offensichtlich daran, dass SQLite keine stored functions/procedures kennt. Und ja das Vorhaben klingt auch äusserst eklig, dennoch hätte so theoretisch die Zahl der "externen" DB Zugriffe / dll calls deutlich reduziert werden können.
    Nun gut es scheint eh nicht zu gehen, also erstmal wieder verworfen.


    Könnte man eine Zeilenverkettung auch mit einem verschachtelten Select hinbekommen? Die Frage wäre aber auch ob der Zeitgewinn durch die Reduzierung der dll calls letztlich nicht wieder durch das Stringsplit in Autoit aufgefressen wird...

  • Ich habe mir grade die Funktion für _SQLite_FetchData 
    angesehen und sehe ein gewisses Optimierungspotenzial ;)


    Ich bastel mal ein bisschen ;)


    --EDIT--


    Ich habe mich vertan, ich habe ein ReDim in der Funktion gesehen und dort an eine Optimierung gedacht, aber bei genauerer Betrachtung ist es nur eine Umwandlung der übergebenen Variable und keine große, zeitraubende Veränderung.


    Ich glaube aber auch, das dein Weg


    nicht funktioniert.
    1. Es gibt es keine Stored Funktions / Stored Procedures bei SQlite (http://www.sqlite.org/whentouse.html)
    2. Datenbanken arbeiten Datensatzweise, der Datensatz in der ersten Zeile hat nichts mit dem Datensatz in der 2ten Zeile zu tun, eine Verknüpfung ist also nicht möglich

  • nicht funktioniert.
    1. Es gibt es keine Stored Funktions / Stored Procedures bei SQlite (http://www.sqlite.org/whentouse.html)
    2. Datenbanken arbeiten Datensatzweise, der Datensatz in der ersten Zeile hat nichts mit dem Datensatz in der 2ten Zeile zu tun, eine Verknüpfung ist also nicht möglich



    1. hab ich ja selbst schon festgestellt gehabt, was 2. angeht:


    Soweit ich mich recht an die Cursor Beispiele bzgl. Stored Function in Oracle erinnere sollte das durchaus gehen. Du kannst in PL/SQL genau wie in Java/Autoit ein resultset/Cursor für das SQL Query deklarieren und diesen dann in einer Schleife nach dem Query Zeilenweise auslesen. In dieser Schleife sollte dann wie in Java/Autoit auch eine Stringverkettung mit einer zuvor deklarierten Rückgabevariable möglich sein. Theoretisch müsste es also schon gehen Zeilen zu verketten.


    Hier wird das sogar gemacht, wenn auch nur eine Addition und keine Stringverkettung: http://www.datenbank-plsql.de/sql.htm


    Vorteil des ganzen ist eben, dass das Query komplett DB intern verarbeitet wird und erst danach ein Ergebnis nach außen geliefert werden muss, was wohl deutlich performanter sein dürfte. Aber auch egal denn sqlite kann das ja leider nicht in dieser Form.

  • Könnte man eine Zeilenverkettung auch mit einem verschachtelten Select hinbekommen? Die Frage wäre aber auch ob der Zeitgewinn durch die Reduzierung der dll calls letztlich nicht wieder durch das Stringsplit in Autoit aufgefressen wird...


    Ha!


    Ich beantworte meine Frage mal selbst, da es mir einfach keine Ruhe gelassen hat. Ja man kann es auch über ein geschachteltes Select hinbekommen und nein Stringsplit kostet sogut wie garkeine Zeit. Das Ergebnis ist nun eine Methode, die wie oben skizziert die komplette Tabelle per Spalten und Zeilen Verkettung in ein einziges Tabellenfeld packt. Und ja auch wenn das ziemlich wirr klingt, es ist nun genauso schnell wie die CSV Methode, also nochmals doppelt so schnell wie die Variante, bei der nur die Spalten verktettet wurden.


    Einen Haken hat das ganze aber noch, denn die Performance bricht ab einer gewissen Datenmenge massivst ein und ist dann sogar schlechter als beim reinen Spalten verketten. Das scheint primär daran zu liegen, dass das Fetch des großen Pakets ab diesem Punkt scheinbar länger als die vielen kleinen Fetches braucht.


    Bei den Testdaten fällt das erst ab 8000-9000 Zeilen wirklich ins Gewicht. Die CSV Methode skaliert somit immernoch am besten mit ansteigender Zeilenzahl.



    ; Daten einlesen
    Local $subquery="SELECT " & StringReplace($allColumNames,"|"," || '|' || ") & " AS Result FROM " & $tablename
    _SQLite_Query ( $hDB, "Select group_concat(Result) FROM (" & $subquery & ")", $hQuery )
    _GUICtrlListView_BeginUpdate($myList)
    If _SQLite_FetchData($hQuery,$aResult) = $SQLITE_OK Then
    Local $aTemp = StringSplit($aResult[0],",")
    For $i = 1 To $aTemp[0]
    GUICtrlCreateListViewItem($aTemp[$i],$myList)
    Next
    Else
    ConsoleWrite("SQLite Error: Reading Table failed! Errorcode(" & @error & " / " & @extended & ")" & @CRLF)
    EndIf
    _SQLite_QueryFinalize($hQuery)
    _GUICtrlListView_EndUpdate($myList)


    Code
    CSV-Methode Time: 302.280300585426
    SQL-LWjoinFast-Methode Time: 323.844537573561
    SQL-LWjoin-Methode Time: 597.191229577753
    SQL-Fetch-Methode Time: 1017.78813832817
    SQL-2DArray_LWArrAdd-Methode Time: 1157.89243714743
    SQL-2DArray-Methode Time: 1265.99571796851