Hilfe von einem SQL Guru erbeten

Für allgemeine Fragen zur Programmierung mit PureBasic.
MenschMarkus
Beiträge: 220
Registriert: 30.04.2009 21:21
Computerausstattung: i5-2300 (2.8 Ghz) Win10 -64bit / PB 5.73 LTS

Hilfe von einem SQL Guru erbeten

Beitrag von MenschMarkus »

Ich habe da ein kleines SQL Timing Problem.

1. Wie ist die Ausgangssituation
Ich habe eine Tabelle mit ID Daten und einigen noch leeren Feldern. Die ID Felder sind NICHT Unique. Das bedeutet diese können mehrfach vorkommen
2. Was mache ich
Ich zähle wie häufig eine ID in der Tabelle vorkommt und trage in einem zweiten Feld die Anzahl der Vorkommnisse ein. In einem dritten Feld trage ich einen Marker ein der mir signalisiert, "Diese ID habe ich schon abgearbeitet"
Hier mal ein Code Snippet dazu

Code: Alles auswählen

Procedure testdb()
  Define i.i, Query.s, field1value.i, found.i
  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)
  Query = "SELECT * FROM testdb WHERE field2 = 0"
  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
  
EndProcedure
testdb()
Das sollte sich selbst erklären
3. Das Problem
Das dauert ewig lange, da ich jeden Datensatz updaten muss
4. Was soll das ganze
Eine solche Tabelle ist mir vorgegeben und ich muss diese statistisch auswerten, also wie häufig kommt eine ID vor, muss das in die Tabelle eintragen um das Ergebnis später wieder auslesen zu können
5. Was könnt Ihr für mich tun
Mir dabei helfen das ganze Ding wesentlich zu beschleunigen. (Leider kann man mehrere UPDATE Bedingungen nicht in eine Befehlszeile zusammenfassen so wie das bei INSERT funktioniert. Zumindest habe ich dazu nichts gefunden)

Danke schon mal für die Unterstützung
Wissen schadet nur dem, der es nicht hat !
Benutzeravatar
Kiffi
Beiträge: 10621
Registriert: 08.09.2004 08:21
Wohnort: Amphibios 9

Re: Hilfe von einem SQL Guru erbeten

Beitrag von Kiffi »

MenschMarkus hat geschrieben: 29.09.2021 16:51Das dauert ewig lange, da ich jeden Datensatz updaten muss
Da es sich bei SQLite um eine Datei-Datenbank handelt (ohne dass ein Server dazwischen hängt), wird die DB bei jedem Insert / Update / Delete direkt auf dem Datenträger geöffnet, bearbeitet und geschlossen.

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.

also so:

Code: Alles auswählen

DatabaseUpdate(0,"BEGIN TRANSACTION")

[DeineDatenbankUpdates]

DatabaseUpdate(0,"COMMIT")
Hygge
Benutzeravatar
NicTheQuick
Ein Admin
Beiträge: 8675
Registriert: 29.08.2004 20:20
Computerausstattung: Ryzen 7 5800X, 32 GB DDR4-3200
Ubuntu 22.04.3 LTS
GeForce RTX 3080 Ti
Wohnort: Saarbrücken
Kontaktdaten:

Re: Hilfe von einem SQL Guru erbeten

Beitrag von NicTheQuick »

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".
Bild
MenschMarkus
Beiträge: 220
Registriert: 30.04.2009 21:21
Computerausstattung: i5-2300 (2.8 Ghz) Win10 -64bit / PB 5.73 LTS

Re: Hilfe von einem SQL Guru erbeten

Beitrag von MenschMarkus »

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()
Wissen schadet nur dem, der es nicht hat !
Antworten