Kiffi hat geschrieben: ↑29.09.2021 18:08
Klammerst Du umfangreiche DB-Operationen mit 'BEGIN TRANSACTION' und 'COMMIT', so werden diese erst einmal im Speicher ausgeführt und dann (mit dem 'Commit') in einem Rutsch auf die Platte geschrieben. Das erhöht die Geschwindigkeiten von SQLite-Operationen
erheblich.
Danke Kiffi, aber das hat leider nicht wirklich etwas an performance gebracht.
Code: Alles auswählen
Procedure testdb()
Define i.i, Query.s, field1value.i, found.i, starttimer.q
UseSQLiteDatabase()
;- ===== Create Test Database With 1 Table
CreateFile(0,"testdb.db3")
CloseFile(0)
OpenDatabase(0,"testdb.db3","","",#PB_Database_SQLite)
DatabaseUpdate(0,"CREATE TABLE testdb (field1 INTEGER,field2 INTEGER,field3 INTEGER,field4 INTEGER)")
;- ===== Fill Test Database
Query = "INSERT INTO testdb (field1,field2,field3,field4) VALUES ("
For i = 1 To 100
If Mod(i,10) = 0
Query + Str(i) + ",0,0,0),("
EndIf
Query + Str(i) + ",0,0,0),("
Next
DatabaseUpdate(0,Left(Query,Len(Query) - 2))
;- ===== Calculate Table (Recursive)
starttimer = ElapsedMilliseconds()
Query = "SELECT * FROM testdb WHERE field2 = 0"
DatabaseUpdate(0,"PRAGMA journal_mode = DIFFERENT") ;Standard journal_mode unter SQLite
DatabaseUpdate(0,"START TRANSACTION")
DatabaseQuery(0,Query)
While NextDatabaseRow(0)
;- 1. Get field1 value
field1value = GetDatabaseLong(0,DatabaseColumnIndex(0,"field1"))
;- 2. Count Table entries with selected field1value
Query = "SELECT COUNT(*) FROM testdb WHERE field1 = " + Str(field1value) + " AND field2 = 0"
DatabaseQuery(0,Query)
NextDatabaseRow(0)
;- 3. Read count result
found = GetDatabaseLong(0,0)
;- 4. Write count result to field 3
FinishDatabaseQuery(0)
Query = "UPDATE testdb SET field2 = 1, field3 = " + Str(found) + ", field4 = 1 WHERE field1 = " + Str(field1value)
DatabaseUpdate(0,Query)
;- 5. Restart complete loop with values where field2 = 0 (this starts something like a rekursive While - Wend loop)
Query = "SELECT * FROM testdb WHERE field2 = 0"
DatabaseQuery(0,Query)
Wend
DatabaseUpdate(0,"COMMIT")
Debug ElapsedMilliseconds() - starttimer
EndProcedure
testdb()
im obigen Beispiel habe ich einen Timer mitlaufen lassen, mal mit, mal ohne Start Transaction und Commit. Beide Bearbeitungszeiten waren different. Bei 100 Datensätzen waren es ca. 2 sek. Bei 1000 Datensätzen wären is schon 20 sek., was schon sehr ordentlich ist
Bei den Versuchen hat die Festplatte immer noch gerattert, also einmal Journal Mode auf Memory gestellt bzw ganz ausgeschaltet. So konnte ich bis zu 90% Zeit einsparen.
Wie wahrscheinlich der Fall ist weiß ich nicht, aber soweit ich weiß crasht die Tabelle oder gar die komplette DB wenn es zu einem Speicherproblem kommt und der journal_mode auf Memory steht. Da ich keine Rückführung benötige stellt sich die Frage ob man das journal nicht ganz abschaltet.
Hier mal eine Performance Aufstellung:
Pragma / Transaction / Zeit (ms)
MEMORY / OFF / 2609
MEMORY / ON / 3407
OFF / OFF / 3458
OFF / ON / 2543
DELETE / OFF / 10569
DELETE / ON / 8837
NicTheQuick hat geschrieben: ↑29.09.2021 19:36
Ich würde zunächst ein SELECT ausführen, dass dir alle Werte ausgibt, die du brauchst:
Code: Alles auswählen
select field1, count(*) AS c from testdb group by field1;
Und dann machst du einmal wie von Kiffi erwähnt "BEGIN TRANSACTION" und dazwischen alle Updates auf einmal und am Ende wieder ein "COMMIT".
Auch Dir Danke für den Post, Nic.
Hab ich natürlich auch gleich getestet. Pfeilschnell, weil Ergebnis sofort da. Ich muss zwar noch einen % Wert berechnen und in jede Zeile eintragen, was wiederum Zeit kostet, aber der Code wird dadurch wesentlich kürzer und übersichtlicher. Das nehm ich gerne.
Code: Alles auswählen
Procedure testdb()
Define i.i, Query.s, field1value.i, found.i, starttimer.q, baseitems.i = 8
UseSQLiteDatabase()
;- ===== Create Test Database With 1 Table
CreateFile(0,"testdb.db3")
CloseFile(0)
OpenDatabase(0,"testdb.db3","","",#PB_Database_SQLite)
DatabaseUpdate(0,"CREATE TABLE testdb (field1 INTEGER,field2 INTEGER,field3 INTEGER,field4 INTEGER)")
;- ===== Fill Test Database
Query = "INSERT INTO testdb (field1,field2,field3,field4) VALUES ("
For i = 1 To 100
If Mod(i,10) = 0
Query + Str(i) + ",0,0,0),("
EndIf
Query + Str(i) + ",0,0,0),("
Next
DatabaseUpdate(0,Left(Query,Len(Query) - 2))
;- ===== Calculate Table (Recursive)
starttimer = ElapsedMilliseconds()
Query = "SELECT field1,COUNT(*) AS c FROM testdb group by field1"
DatabaseUpdate(0,"PRAGMA journal_mode = MEMORY")
;DatabaseUpdate(0,"START TRANSACTION")
DatabaseQuery(0,Query)
While NextDatabaseRow(0)
DatabaseUpdate(0,"UPDATE testdb SET field2 = " + GetDatabaseString(0,DatabaseColumnIndex(0,"c")) + ", field4 = " + Str((GetDatabaseLong(0,DatabaseColumnIndex(0,"c")) * 100) / baseitems) + " WHERE field1 = " + GetDatabaseString(0,DatabaseColumnIndex(0,"field1")))
Wend
;DatabaseUpdate(0,"COMMIT")
Debug ElapsedMilliseconds() - starttimer
EndProcedure
testdb()
Nochmals vielen Dank für den Code Nic.
(Ich dachte mir schon dass es da was geben muss. Bin halt nicht selber drauf gekommen. Da muss ich noch an mir arbeiten)
[ergänzender Nachtrag]
Nic's Code ist schon genial einfach und schnell. Das Update der bestehenden Tabelle kostet einfach unverschämt viel Zeit. Eine Ergebnistabelle zu öffnen und alles dort mit INSERT einzutragen ist wesentlich schneller als jedes UPDATE trotz "Start Transaction" und journal_mode. Durch gescheiten aufbau des INSERT Befehls kann die Tabelle in einem Schritt geschrieben werden. So konnte ich bei 1000 Datensätzen aus obigem Beispiel die Zeit von 10 Sekunden auf 350 ms reduzieren. Das nenne ich mal Performancegewinn.
Code: Alles auswählen
Procedure testdb()
Define i.i, Query.s, starttimer.q, baseitems.i = 8
UseSQLiteDatabase()
;- ===== Create Test Database With 1 Table
CreateFile(0,"testdb.db3")
CloseFile(0)
OpenDatabase(0,"testdb.db3","","",#PB_Database_SQLite)
DatabaseUpdate(0,"CREATE TABLE testdb (field1 INTEGER,field2 INTEGER,field3 INTEGER,field4 INTEGER)" +
";CREATE TABLE testdb2 (field1 INTEGER,field2 INTEGER,field3 INTEGER,field4 INTEGER)")
;- ===== Fill Test Database
Query = "INSERT INTO testdb (field1,field2,field3,field4) VALUES ("
For i = 1 To 1000
If Mod(i,10) = 0
Query + Str(i) + ",0,0,0),("
EndIf
Query + Str(i) + ",0,0,0),("
Next
DatabaseUpdate(0,Left(Query,Len(Query) - 2))
;- ===== Calculate Table
starttimer = ElapsedMilliseconds()
Query = "SELECT field1,field4,COUNT(*) AS c FROM testdb group by field1"
DatabaseQuery(0,Query)
Query = "INSERT INTO testdb2 (field1,field2,field3,field4) VALUES "
While NextDatabaseRow(0)
Query + "(" + GetDatabaseString(0,DatabaseColumnIndex(0,"field1")) + ",1," + Str(GetDatabaseLong(0,DatabaseColumnIndex(0,"c"))) + "," + Str((GetDatabaseLong(0,DatabaseColumnIndex(0,"c")) * 100) / baseitems) + "),"
Wend
DatabaseUpdate(0,Left(Query,Len(Query) - 1))
Debug ElapsedMilliseconds() - starttimer
EndProcedure
testdb()