Datenbank: Grosse .csv einfügen beschleunigen ?

Für allgemeine Fragen zur Programmierung mit PureBasic.
GPI
Beiträge: 1511
Registriert: 29.08.2004 13:18
Kontaktdaten:

Re: Datenbank: Grosse .csv einfügen beschleunigen ?

Beitrag von GPI »

Bisonte hat geschrieben:
GPI hat geschrieben:15GB an Daten zu importieren ist auch eine Hausnummer :)
Das ist wohl wahr....

Allerdings sind das "nur" die erforschten Systeme in dem Spiel. Es handelt sich dabei wohl so um vielleicht 1-2% wenn
ich das mal vorsichtig schätze, sehr weit aus dem Fenster gelehnt :mrgreen:

Die Macher haben wohl bei der prozeduralen Systemerstellung die Abbruchbedingungen falsch eingebaut ... Endlosschleife :D
Ich wette mit dir, die unerforschten Systeme sind noch gar nicht in der Datenbank. Wozu auch. Die werden zur Laufzeit erzeugt. Dauert dann der Warp-Sprung (oder was auch immer in Elite ist) notfalls etwas länger. Würde ich zumindest so programmieren. So kann man bspw. auch neue Sachen in die prozentuale Erstellung reinprogrammieren (neue Völker was weis ich), die dann bei neuen Systemen beachtet werden. Alte Systeme zu ändern ist ja immer so eine Sache.
CodeArchiv Rebirth: Deutsches Forum Github Hilfe ist immer gern gesehen!
Benutzeravatar
Kiffi
Beiträge: 10621
Registriert: 08.09.2004 08:21
Wohnort: Amphibios 9

Re: Datenbank: Grosse .csv einfügen beschleunigen ?

Beitrag von Kiffi »

Bisonte hat geschrieben:Ein "BEGIN" vor der "INSERT" Ausführung und ein "COMMIT" danach bringt irgendwie nichts...
Du musst ALLE Inserts mit Begin und Commit klammern. Sonst bringt das nichts.

Code: Alles auswählen

DatabaseUpdate -> "BEGIN TRANSACTION"

For Schleife ...
 DatabaseUpdate -> "INSERT"
Next

DatabaseUpdate -> "COMMIT"
Grüße ... Peter
Hygge
Benutzeravatar
Bisonte
Beiträge: 2427
Registriert: 01.04.2007 20:18

Re: Datenbank: Grosse .csv einfügen beschleunigen ?

Beitrag von Bisonte »

Kiffi hat geschrieben:
Bisonte hat geschrieben:Ein "BEGIN" vor der "INSERT" Ausführung und ein "COMMIT" danach bringt irgendwie nichts...
Du musst ALLE Inserts mit Begin und Commit klammern. Sonst bringt das nichts.

Code: Alles auswählen

DatabaseUpdate -> "BEGIN TRANSACTION"

For Schleife ...
 DatabaseUpdate -> "INSERT"
Next

DatabaseUpdate -> "COMMIT"
Grüße ... Peter
das hab ich probiert, dann passiert aber rein gar nichts....
Hab dann die SQL Seite durchforstet und dort steht was von blocken aller Sachen ausser SELECT .... deswegen dacht ich das wäre der Grund.
PureBasic 6.04 LTS (Windows x86/x64) | Windows10 Pro x64 | Asus TUF X570 Gaming Plus | R9 5900X | 64GB RAM | GeForce RTX 3080 TI iChill X4 | HAF XF Evo | build by vannicom​​
Benutzeravatar
Kiffi
Beiträge: 10621
Registriert: 08.09.2004 08:21
Wohnort: Amphibios 9

Re: Datenbank: Grosse .csv einfügen beschleunigen ?

Beitrag von Kiffi »

Bisonte hat geschrieben:das hab ich probiert, dann passiert aber rein gar nichts....
woran erkennst Du das? :wink:

beim Insert innerhalb einer Transaktion wird die Datenbank in Deinem RAM befüllt und erst mit dem Commit auf die Platte geschrieben. Aus diesem Grund verändert sich während der Insert-Orgie auch nicht die Größe Deiner DB.

Ohne Transaktion wird für jedes Insert die Datenbank geladen, der Datensatz eingefügt und die Datenbank gespeichert. Aus diesem Grund ist ein Massenimport ohne Transaktion auch so elendig langsam.

Grüße ... Peter
Hygge
Benutzeravatar
Bisonte
Beiträge: 2427
Registriert: 01.04.2007 20:18

Re: Datenbank: Grosse .csv einfügen beschleunigen ?

Beitrag von Bisonte »

ich habs am Speicher gesehen, den die exe "verschlingt". Es war so wie bei dem File. So nach 10 minuten ca 500KB mehr...
PureBasic 6.04 LTS (Windows x86/x64) | Windows10 Pro x64 | Asus TUF X570 Gaming Plus | R9 5900X | 64GB RAM | GeForce RTX 3080 TI iChill X4 | HAF XF Evo | build by vannicom​​
Benutzeravatar
bobobo
jaAdmin
Beiträge: 3857
Registriert: 13.09.2004 17:48
Kontaktdaten:

Re: Datenbank: Grosse .csv einfügen beschleunigen ?

Beitrag von bobobo »

begin transaction
import
commit

so macht man das

ein demo kommentier mal die Zeiel mit begin transaction und commit aus

Code: Alles auswählen


Enumeration
#sqlite
EndEnumeration

UseSQLiteDatabase()
CreateFile(0,"D:\fridolin")
CloseFile(0)

OpenDatabase(#sqlite,"D:\fridolin","","")
sql.s="create table daten (i int,t text,t1 text)"
DatabaseUpdate(#sqlite,sql)
Debug DatabaseError()
;PRAGMA schema.journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF
sql.s="PRAGMA journal_mode = MEMORY"
DatabaseUpdate(#sqlite,sql)
Debug DatabaseError()
 sehrviel=400000
 DatabaseUpdate(#sqlite,"begin transaction")
Debug ElapsedMilliseconds()
For i =1 To sehrviel
  sql="insert into daten Values("+i+","+Chr(34)+"textinhalt"+i+Chr(34)+","+Chr(34)+"textinhalt 2"+i+Chr(34)+")"
  DatabaseUpdate(#sqlite,sql)
Next i
DatabaseUpdate(#sqlite,"commit")
CloseDatabase(#sqlite)

Debug ElapsedMilliseconds()
GB weise nach sqlite dürfte aber immer ne Ecke dauern.
Du kannst das logging mit pragmas auf memory stellen (siehe code), dürfte bei einem reinen import aber wenig bringen.
‮pb aktuell5.7 - windoof aktuell und sowas von 10
Ich hab Tinnitus im Auge. Ich seh nur Pfeifen.
Benutzeravatar
Waldixxl
Beiträge: 108
Registriert: 27.12.2005 17:35
Wohnort: Linz, Österreich

Re: Datenbank: Grosse .csv einfügen beschleunigen ?

Beitrag von Waldixxl »

Guten Morgen Bisonte

Habe das gleiche Problem schon mal gehabt und folgendes gefunden.
Man kann mit Insert gleich mehrere Datensätze auf einmal in die Datenbank schreiben.

Code: Alles auswählen

INSERT INTO Mitarbeiter(Vorname, Nachname, Abteilung) VALUES('Meier','Klaus','HR'), ('Schmidt','Dirk','R&D'), ('Schneider','Tobias','R&D')
somit sollte sich das beschleunigen lassen

Walter
Lesen bei schlechten Lichtverhältnissen ist nicht schlecht für die Augen, sondern trainiert sie sogar.

Linux Ubuntu Mate 18.04
PB 5.71
Intel i5
Benutzeravatar
Bisonte
Beiträge: 2427
Registriert: 01.04.2007 20:18

Re: Datenbank: Grosse .csv einfügen beschleunigen ?

Beitrag von Bisonte »

Ich hab da noch mehr Experimente angestellt...

Also Kiffi's Aussage stimmt. Es tut sich doch was, ich habs nur "noch" nicht gesehen.
Da ich aber dem User nicht zumuten kann, auf ein Fenster zu starren wo nichts passiert, habe ich
in Schritten das BEGIN und COMMIT eingeleitet. Also alle 250000 Zeilen. Dann kann man tatsächlich sehen
das etwas passiert. Ich hab das ganze natürlich in einem Thread laufen und schicke den aktuellen "Zeilenstand"
per PostEvent zum Fenster (ein ProgressBar-, und ein TextGadget).

Ich spiele noch mit der Zeilen-Anzahl, wo ein schreiben ins File stattfinden soll, aber bisher klappt das ganz gut.
Dauert zwar immer noch bei weitem länger als der Import mit der SQLiteShell, aber es klappt ;)

@bobobo:
Das mit dem "PRAGMA journal_mode = MEMORY" werd ich mal testen, obwohl das wirklich nicht viel
bringen wird, ausser das die Platte weniger beschrieben wird. Das Journal nahm bei 250000 Einträgen immer nur 9KB
in Anspruch. Das DB File wuchs indes um 32 MB.

@Waldixxl:
Die Idee ist nicht schlecht, ich hab da nur ein Problem... Die Länge der Strings....
Ich habe eine Konstante, der ich den "INSERT INTO" String zugewiesen habe.

Code: Alles auswählen

#xSQL = "INSERT INTO systems(" +
        "id, " +
        "edsm_id, " +
        "name, " + 
        "x, " +
        "y, " +
        "z, " +
        "population, " +
        "is_populated, " +
        "government_id, " +
        "government, " +
        "allegiance_id, " +
        "allegiance, " +
        "state_id, " +
        "state, " +
        "security_id, " +
        "security, " +
        "primary_economy_id, " +
        "primary_economy, " +
        "power, " +
        "power_state, " +
        "power_state_id, " +
        "needs_permit, " +
        "updated_at, " +
        "simbad_ref, " +
        "controlling_minor_faction_id, " +
        "controlling_minor_faction, " +
        "reserve_type_id, " +
        "reserve_type) VALUES (" + 
        "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Ich finde diesen String schon extrem lang, aber er ist "fest", was mir das eintragen in die DB dank
SetDataBaseString() erleichtert.

Die Thread Prozedur sieht momentan so aus :

Code: Alles auswählen

; Lines.s() = Linklist (eine Zeile der CSV Datei) 
; Es sind momentan 12.653.802 Listeneinträge mit 28 Elementen, durch "," getrennt
; CHR(34) soll dabei entfernt werden (es sind Namen dabei)
; Counter   = Zähler für ProgressBar Fortschritt
; C2        = Zähler für Datenbank Zugriff
; i         = Index Zähler für SetDataBaseString()
; DB\       = Mein eigenes SQLite DB Interface
; #xSQL     = Konstante die den SQL INSERT INTO String enthält

Procedure.i Thread2(d = 0)
  
  Protected i, Counter, C2
  
  DB\Update("BEGIN;") ; Entspricht DataBaseUpdate()
  PostEvent(#ListEV, 0, 0, #Start, ListSize(Lines()))
  
  ForEach Lines()
    Counter + 1 : c2 +1
    db\SetQuad(0, Val(StringField(Lines(), 1, ","))) ; Entspricht SetDatabaseQuad()
    For i = 1 To 27
      db\SetString(i, RemoveString(StringField(Lines(), i + 1, ","), Chr(34))) ; Entspricht SetDatabaseString()
    Next i
    If Not DB\Update(#xSQL) ; Entspricht DataBaseUpdate()
      Debug DB\LastError() ; Entspricht DataBaseError()
    EndIf
    If C2 => 250000
      DB\Update("COMMIT;") ; Entspricht DataBaseUpdate()
      C2 = 0
      DB\Update("BEGIN;") ; Entspricht DataBaseUpdate()
    EndIf
    PostEvent(#ListEV, 0, 0, #Loc, Counter)  
  Next
  DB\Update("COMMIT;") ; Entspricht DataBaseUpdate()   
  Delay(100)
  PostEvent(#ListEV, 0, 0, #Stop, Counter)

EndProcedure
Edit : Die Tests mit der Erhöhung der Einträge, bevor sie in das Datenbankfile geschrieben werden, hat einen
verlangsamenden Effekt. Messungen : 500k Einträge ca. 9,7 Minuten 1Mio Einträge circa 10,6 Minuten
Das ganze gemessen ohne Debugger... Also das war eher ein Schuss in die falsche Richtung... :(
PureBasic 6.04 LTS (Windows x86/x64) | Windows10 Pro x64 | Asus TUF X570 Gaming Plus | R9 5900X | 64GB RAM | GeForce RTX 3080 TI iChill X4 | HAF XF Evo | build by vannicom​​
Benutzeravatar
Waldixxl
Beiträge: 108
Registriert: 27.12.2005 17:35
Wohnort: Linz, Österreich

Re: Datenbank: Grosse .csv einfügen beschleunigen ?

Beitrag von Waldixxl »

Hallo Bisonte

Habe mein damaliges Programm wieder ausgegraben und ein Beispiel daraus gemacht.
Ich kann schon einen großen Geschwindigkeitsvorteil erkennen.

Code: Alles auswählen

UseSQLiteDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
  Result = DatabaseUpdate(Database, Query$)
  If Result = 0
    Debug DatabaseError()
  EndIf
  ProcedureReturn Result
EndProcedure

Procedure CheckDatabaseQuery(Database, Query$)
  Result = DatabaseQuery(Database, Query$)
  If Result = 0
    Debug DatabaseError()
  EndIf
  ProcedureReturn Result
EndProcedure

Procedure Datenbankerstellen(DatenbankFiles$="test.sql")
  If CreateFile(0, DatenbankFiles$)
    CloseFile(0)
    If OpenDatabase(0, DatenbankFiles$, "", "")
      CheckDatabaseUpdate(0, "CREATE TABLE Test (IndexID INTEGER PRIMARY KEY, Zeit NUMERIC, Wert NUMERIC(12,3))")
    Else 
      MessageRequester("Fehler", "Kann die Datenbank nicht öffnen!", #PB_MessageRequester_Ok)
    EndIf
  Else
    MessageRequester("Fehler", "Kann die Datenbankdatei nicht erstellen!", #PB_MessageRequester_Ok)
  EndIf 
EndProcedure  

Structure Dat
  Zeit.l
  Value.d
EndStructure

NewList Daten.dat()
DatenZ.dat

Debug "Liste erstellen"

For i= 0 To 2000000
  DatenZ\Zeit = Zeit+i
  DatenZ\Value = Wert+i
  AddElement(Daten())
  Daten() = DatenZ 
Next

Datenbankerstellen()    
OpenDatabase(0, "test.sql", "", "")

Debug "10000 Einträge einzeln in die Datenbank schreiben"
Zeit = ElapsedMilliseconds()

For i=0 To 10000
  EintragDatenbank$ = "INSERT INTO Test (Zeit, Wert) VALUES "
  EintragDatenbank$= EintragDatenbank$ + "("+daten()\zeit+", "+Daten()\Value+")"
  CheckDatabaseUpdate(0, EintragDatenbank$)
Next

Debug "benötigte Zeit " +Str(ElapsedMilliseconds()-Zeit)+ "msek."
Debug " "
Debug "2000000 Einträge in Gruppen zu 1000 Datensätze in die Datenbank schreiben"
Zeit = ElapsedMilliseconds()

;String zusammenstellen
*MemoryID = AllocateMemory(40000)
*NextSpeicher + PokeS(*MemoryID+ *NextSpeicher, "INSERT INTO Test (Zeit, Wert) VALUES ")
Maxanzahl=0
ForEach Daten()
  If Maxanzahl > 1000
    EintragDatenbank$ = PeekS(*MemoryID,*NextSpeicher)
    EintragDatenbank$=Left(EintragDatenbank$,Len(EintragDatenbank$)-1)
    CheckDatabaseUpdate(0, EintragDatenbank$)
    *NextSpeicher=0
    *NextSpeicher +PokeS(*MemoryID+ *NextSpeicher, "INSERT INTO Test (Zeit, Wert) VALUES ")
    Maxanzahl = 0
  EndIf
  *NextSpeicher +PokeS(*MemoryID+*NextSpeicher,  "("+daten()\zeit+", "+Daten()\Value+"),")
  Maxanzahl + 1
Next

EintragDatenbank$ = PeekS(*MemoryID,*NextSpeicher)
EintragDatenbank$=Left(EintragDatenbank$,Len(EintragDatenbank$)-1)
CheckDatabaseUpdate(0, EintragDatenbank$)

Debug "Benötigte Zeit" +Str(ElapsedMilliseconds()-Zeit)+ "msek."

End
      
bei mir brauchen 10000 einzelne Datensätze ca. 13 sek. und 2000000 Datensätze in Gruppen 10 sek.
Walter
Lesen bei schlechten Lichtverhältnissen ist nicht schlecht für die Augen, sondern trainiert sie sogar.

Linux Ubuntu Mate 18.04
PB 5.71
Intel i5
Antworten