MySQL extrem langsam?!?

[Lexor]

Hallo PureBasic Community,

ich habe folgendes Problem:

Mein Script soll Daten aus der DB eines Warenwirtschaftssystem lesen und diese dann in eine andere DB schreiben.
Gelesen wird aus einer Simba DB über eine ODBC System-DNS
Geschrieben wurde bisher in eine Access DB über ODBC.

Weil sich an die Ziel DB (bisher Access) viele Benutzer gleichzeitig anmelden können sollen, möchte ich die Access DB gegen eine MySQL DB austauschen.

Das Problem ist nun, dass der Schreibzugriff auf die MySQL DB ca. um den
Faktor 50 langsamer ist, als auf die vorherige Access DB.
Leider habe ich keine Ahnung, woran das liegen könnte ...

Hier noch einige Information
MySQL Server 5.0
Mysql ODBC Treiber 3.51
Getestet als Standart DB und als Multiuser DB (MySQL Server config)
der Schreibzugriff findet local statt.

Die Konfiguration meines Arbeitsrechners ist die gleiche, wie zur Zeit der Access DB und das System arbeitet normal.

Nun der fragliche Qelltext:

Code: Alles auswählen

Structure Item
  No.s
  Brand.s
  Vendor_No.s
  Description.s
  VertComp.s
  Horzcomp.s
  Colour.s
  Vendor_Colour.s
  Reference.s
EndStructure

Global b.l 
Global a.l 

b = 1
a = 1

; #############Procedure für den Schreibzugriff############

Procedure WriteNavData()

  TextGadget(6,10,160,180,20,"Daten werden geschrieben ... ",#PB_Text_Center)
  ProgressBarGadget(7, 10, 175,180, 30, 0,a, #PB_ProgressBar_Smooth)
 
ForEver[color=red] 
  result = OpenDatabase(0,"EANScan","root","*****")

  If result > 0  

        abfrage.s = "delete QUICK from EAN"
        If DatabaseUpdate(abfrage)
          For r = 1 To a
            abfrage = "INSERT INTO EAN (Brand, Vendor_No, Description,            HorzComp, VertComp, Vendor_VertComp, Reference) VALUES ('"+Current(r)\Brand+"', '"+Current(r)\Vendor_No+"', '"+Current(r)\Description+"', '"+Current(r)\HorzComp+"', '"+Current(r)\VertComp+"','"+Current(r)\Vendor_Colour+"','"+Current(r)\Reference+"')"
            If DatabaseUpdate(abfrage) >0
              SetGadgetState(7,r)
            Else
              MessageRequester("",DatabaseError())
              Break
            EndIf
          Next
          SetGadgetText(6,"Daten werden geschrieben ... fertig!")
      Else
        MessageRequester("",DatabaseError())
      EndIf
  Else
    MessageRequester("",DatabaseError())
  EndIf  
EndProcedure[/color]

Procedure EndTool()
  
EndProcedure

; #################Ende Schreibzugriff################

If InitDatabase() 
  If OpenWindow(0, 216, 0, 200, 240,  #PB_Window_SystemMenu | #PB_Window_TitleBar | #PB_Window_ScreenCentered , "Data Export/Import") 
    CreateGadgetList(WindowID(0))
    TextGadget(0,10,10,180,20,"Datenübertragung starten?",#PB_Text_Center)
    ButtonGadget(1,75,50,50,20,"Start")
  EndIf
Else
 MessageRequester("Info", "Operation canceled", 0)
EndIf

Repeat
event = WaitWindowEvent()

If event = #PB_EventGadget 
  Select EventGadgetID()
    Case 1:
      ReadNavData()
      WriteNavData()
      ButtonGadget(10,40,220,100,20,"Programm beenden!")
   Case 10:
      Break
  EndSelect
EndIf
Delay(1)

Bitte nehmt keinen Anstoß an evtl. unsauberen Code. Das ganze funktionierte immerhin gut mit der Access DB.

Sieht jemand, wo das Problem liegen könnte?
Gibt es vielleicht ein sinnvolles "Workaround"?

Vielen Dank bereits im Vorraus für Eure Antworten.

Gruß
Dominique

[bobobo]

Versuch mal die zu schreibenden Daten vorher zu kummulieren
und dann in einem Stück zu schreiben.
Den Schreibvorgang auf die MySql-Tabelle kannst Du dabei
eventuell beschleunigen wenn Du vor dem eigentlichen
Schreibvorgang ein
"Beginn Transaction"
und nach dem Vorgang ein
"commit transaction"
abschickst.

[DataMiner]

Ich glaube das liegt an der SQL-Optimierung und nicht an dem Code.
Ist nicht gerade einfach, siehe link
http://www.tu-chemnitz.de/docs/mysql-ma ... ation.html
daher bin ich bei Access geblieben.

[edel]

Das Problem hatte ich auch einmal , liess sich aber , durch das
anlegen eines Index, sehr gut loesen.

[Kiffi]

Das Problem hatte ich auch einmal , liess sich aber , durch das
anlegen eines Index, sehr gut loesen.

Vielleicht hast Du das falsch in Erinnerung.

Ein Index beschleunigt nur Select-Abfragen mit Kriterium des indizierten
Feldes. Insert-Statements werden dadurch verlangsamt.

Ich schliesse mich dem von bobobo vorgeschlagenen Lösungsweg an.

Grüße ... Kiffi

[MonkeyBoogie]

abfrage.s = "delete QUICK from EAN"
If DatabaseUpdate(abfrage)
For r = 1 To a
abfrage = "INSERT INTO EAN (Brand, Vendor_No, Description, HorzComp, VertComp, Vendor_VertComp, Reference) VALUES ('"+Current(r)\Brand+"', '"+Current(r)\Vendor_No+"', '"+Current(r)\Description+"', '"+Current(r)\HorzComp+"', '"+Current(r)\VertComp+"','"+Current(r)\Vendor_Colour+"','"+Current(r)\Reference+"')"

Kann ich mal die DB Struktur sehen, also den Create Dump?
Wieviele Daten lagen vor dem Delete in der Tabelle?

Ich glaube das liegt an der SQL-Optimierung und nicht an dem Code.
Ist nicht gerade einfach, siehe link
http://www.tu-chemnitz.de/docs/mysql-ma ... ation.html
daher bin ich bei Access geblieben.

Das ist aber nun Mumpitz, mit einem richtigen DB Layout rennt MySQL
aber minimum genauso schnell wie Access, bei solchen simplem Inserts
schneller

[Lexor]

zunächst einmal meinen herzlichen Dank für Eure rege Beteiligung.

Anhand Eurer Antworten sind mir noch ein par Dinge eingefallen, die ich vielleicht erwähnen sollte.

Hier der Create Dump für MonkeyBoogie:

Code: Alles auswählen

InitDatabase()
      result = OpenDatabase(0,"EANScan","root","panter")
      If (result) > 1
      UseDatabase(0)
      abfrage.s = "DROP TABLE EAN"
      DatabaseUpdate(abfrage)
      abfrage = "CREATE TABLE EAN ( Brand varchar(20), Vendor_No varchar(30), Description varchar(30), HorzComp varchar(10), "
      abfrage+   "VertComp varchar(5), Vendor_VertComp varchar(30),  Reference varchar(30), Fil varchar(20) )"
     DatabaseUpdate(abfrage) 
;         abfrage = "create index idx_ref on EAN (Reference)"
;         DatabaseUpdate(abfrage)
;         abfrage = "create index idx_no on EAN (Vendor_No)"
;         DatabaseUpdate(abfrage)
        MessageRequester("","OK")
        CloseDatabase(0)
        quit = 1
      Else
        MessageRequester("",DatabaseError())
      EndIf
      Repeat
        Delay(1)
      Until quit = 1

Wie Ihr seht, habe ich die Indizierung zum testen deaktiviert. Egal ob mit oder ohne Index, die Performance ist mieß (beim schreiben).

In der Tabelle liegen vorm DELETE max. 7.000 Zeilen, weil ich nicht die Geduld für alle 50.000 Zeilen aufgebracht habe. Mittlerweile habe ich sogar schon ein Limit von 1000 Zeilen gesetzt. Dieser Schreibvorgang dauert aber immer noch gute 20 bis 30 Sekunden mit nem Athlon 2500+.

Wenn ich die alte Tabelle vorm Import drope und neu erstelle, dann ändert das leider auch nichts.

Es gibt insgesamt nur diese eine Tabelle (EAN).

Der Zugriff ist exklusiv. Es gibt also keine anderen Abfragen, die parellel laufen könnten.

Alle Zeilen der Quelltabellen werden im Structure Item (Current) zwischengespeichert. Jede zu schreibende Zeile entspricht einem INSERT.
Um die Ziel Tabelle zu füllen, werden also 50.000 mal Daten aus der Structure geholt und 50.000 INSERT's ausgeführt.
Bei der Access DB war das gleich und ging ohne Probleme.

An bobobo:
heißt kumulieren in diesem Fall, dass ich jede Zeile mit einer Anfrage komplett fülle (was ich auch mache) oder gibt es eine Möglichkeit, gleich alle 50.000 Zeilen mit einem Rutsch bzw. einem Schleifendurchlauf zu füllen?

Begin Transaction und Commit Transaction werde ich morgen mal testen.

Danke auch an DataMiner für den Link. das ändern von Puffern etc. klingt mir aber etwas zu krass. Mit einer solchen, doch recht kleinen Aufgabe, sollte MySQL eigentlich standartmäßig fertig werden oder?

Gruß
Dominique

[MonkeyBoogie]

abfrage.s = "delete QUICK from EAN"

Ok, fangen wir mal hier an.
Generell gilt, das ein DELETE * langsamer ist als ein TRUNCATE TABLE EAN
(Ein DELETE auch schon mal ein paar Sekunden dauern bei 7k - 50k Einträgen)

Code: Alles auswählen

TRUNCATE führt ein Löschen und Neuerzeugen der Tabelle durch, was viel schneller sit, als Zeilen eine nach der anderen zu löschen.

http://dev.mysql.com/doc/refman/4.0/de/truncate.html

heißt kumulieren in diesem Fall, dass ich jede Zeile mit einer Anfrage komplett fülle (was ich auch mache) oder gibt es eine Möglichkeit, gleich alle 50.000 Zeilen mit einem Rutsch bzw. einem Schleifendurchlauf zu füllen?

Um viele Inserts auf einmal abzuarbeiten gibt es einen wunderbaren Syntax:

Code: Alles auswählen

INSERT INTO EAN (Brand, Vendor_No, Description, HorzComp, VertComp, Vendor_VertComp, Reference)
VALUES
  ('"+Current(r+1)\Brand+"', '"+Current(r+1)\Vendor_No+"', [...], '"+Current(r)\Reference+"'),
  [...],
  ('"+Current(r+1)\Brand+"', '"+Current(r+1)\Vendor_No+"', [...], '"+Current(r)\Reference+"')

Alternativ geht auch ein "LOAD DATA INFILE", was den Vorgang (heutige Benchmarks weiß ich nicht) um Faktor 20 beschleunigen kann.
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Und zum guten Schluss sollte man die Tabelle locken.

Code: Alles auswählen

# LOCK TABLES EAN WRITE;
# INSERT INTO EAN ([...]);
# UNLOCK TABLES;

Der hauptsächliche Geschwindigkeitsunterschied liegt darin, dass der Index-Puffer nur einmal auf HD zurück geschrieben wird, nachdem alle INSERT-Statements fertig sind.

[edel]

Das Problem hatte ich auch einmal , liess sich aber , durch das
anlegen eines Index, sehr gut loesen.

Vielleicht hast Du das falsch in Erinnerung.

Ein Index beschleunigt nur Select-Abfragen mit Kriterium des indizierten
Feldes. Insert-Statements werden dadurch verlangsamt.

Ich schliesse mich dem von bobobo vorgeschlagenen Lösungsweg an.

Grüße ... Kiffi

Da koenntest du Recht haben , ich glaube ich habe Update benutzt.

[bobobo]

Ich hab hier mal entsprechende DB's erzeugt. Das Löschen ist hier
eigentlich eher zeitunkritisch.

Das Insert gehört aber irgendwie optimiert.
wobei
LOCKING bringt nur minimal Verbesserung
Start Transaction - Commit bringt's auch nicht messbar

Das LOAD DATA INFILE haut dafür bestens hin.
Das Erzeugen des Infiles mal außer vor gelassen (dauert aber
nicht so lange , die wird bei mir mit 500K Sätzen immerhin 26MB gross).

1 bis 2 Sekunde für 500K Sätze mit LOAD DATA INFILE
der immerhin 26MB großen Datei

im Gegensatz zu 15 bis 17 Sekunden bei 50K Sätzen
bei den "normalen" Inserts.