MySQL schnell UPDATEN!

[Ruewue]

Hallo an die Datenbankspezies,

ich habe da ein Probelm mit unsere MySQL Datenbank. Es sollen ca. 2.200.000 Datensätze überarbeitet werden und dann mit einem UPDATE neu gesetzt werden.

Dieser UPDATE Befehl dauert immer sehr lange. Kann mir einer sagen, wie ich diesen beschleunigen kann? Als Beispiel habe ich aus den 2,2 Mio Sätzen ca 120.000 rausgeholt und will diese UPDATEN. Da sie am Ende der Tabelle sitzen dauer jedes Update sehr lange. Ich würde jetzt lieber die ausgewählten Datensätze sequenziell ab arbeiten und dann auch gleich updaten. Geht dies in MySQL? Es müssen bei jedem Satz 3 Felder neu gesetzt werden. Ich habe es schon mit einem INDEX auf die 4 Felder der WHERE Abfrage gemacht aber es dauert einfach sehr lange.

Für einen kleine Tip oder einer Hilfe schon mal jetzt meinen Dank!

[Bernhard Geyer]

Die üblichen Beschleunigungsmöglichkeiten verwenden:

Prepared Statements oder gleich eine Stored Procedure.

[HeikoAdams]

Zitat:

Zitat von Ruewue

Dieser UPDATE Befehl dauert immer sehr lange. Kann mir einer sagen, wie ich diesen beschleunigen kann?

Die einzigen effektiven Möglichkeiten wäre vernünftig eingesetzte Indizes, da zuviele Indizes in einer Tabelle auch bremsen können. Wobei Du Dich mit dem Gedanken anfreunden solltest, das ein Update von 2,2 Mio Datensätzen halt eine gewisse Zeit dauert.

Zitat:

Zitat von Ruewue

Ich würde jetzt lieber die ausgewählten Datensätze sequenziell ab arbeiten und dann auch gleich updaten. Geht dies in MySQL?

Da heißt das Zauberwort für Tante Google mysql cursor

Zitat:

Zitat von Bernhard Geyer

oder gleich eine Stored Procedure.

Wobei die SP bei einer nicht indizierten Tabelle wahrscheinlich auch sehr lange brauchen würde. Ergo führt um Indizes (fast) kein Weg herum.

[himitsu]

Was heißt hier eigentlich "extrem lange"?

Bei 2.200.000 nötigen Änderungen und 10 Änderungen pro Millisekunde, dann ergibt das immerhin über 3,5 Minuten.

[hoika]

Hallo,

zeig doch mal ein bisschen Code.

Wie genau machst du das Update ?
Werden Transaktionen benutzt ?
Welche DB-Engine benutzt ihr (InnoDB ... )?


Heiko

[Ruewue]

Danke erst Mal für die Antworten!

Der Ablauf sieht wie folgt aus:

Mit SELECT alle Daten aus Tabelle 1 einlesen.

WHILE not EOF do
Daten in Tabelle 2 suchen

wenn gefunden dann Daten in Tabllle 3 suchen sonnst in Tabelle 4

Daten mit UPDATE wieder auf neuen Stand bringen

Nächsten Datensatz holen
END
Ich habe eine Indize mit den 4 Suchfeldern auf Tabelle 1.

Ein Datensatz benötigt in obigen Durchlauf ca. 30 sekunden, was einfach nicht sein kann.

[JohannesK]

Welche Tabelle enthält denn welche Daten ?
Sind alle mit 2.2 Mio Datensätzen
Gibt es einen Primärschlüssel in der Tabelle fürs Update?
auf welche der 4 Tabellen geht das Update?

Wie sieht es mit einem JOIN von Tabelle 1 und 2 aus ?
Ein bisschen mehr Info wäre hilfreich.

[Ruewue]

OK Danke!

In Tabelle 1 sind 2.2 Mio. Datensätze
In Tabelle 2, Tabelle 3 und Tabelle 4 núr ca. 5400 Datensätze.

Das Update geht wieder auf Tabelle 1

Es gibt keien JOIN zwischen Tabelle 1 und Tabelle 2. Da es sein kann, dass in Tabelle 2 keine Daten vorhanden sind, was zu einem Fixwert und der Suche in Tabelle 4 führt.

[DeddyH]

Wenn ich das richtig verstanden habe, holst Du Dir alle Daten auf den Client, verarbeitest die Datensätze einzeln und schickst dann je DS ein Update zum Server zurück. Da denke ich, hat Bernhard Recht, eine SP sollte da um einiges performanter sein.

[JohannesK]

Ohne Code und Tabellenstruktur ist es schwierig einen vernünftigen Tipp zu geben.
Wie sieht z.B. Dein SELECT aus und Dein Index?

[Ruewue]

Hallo DeddyH, das stimmt so. Ich hole alle 2.2 Mio Daten runter auf den Client. Gehe dann jeden Satz einzeln durch. Ich suche erst einen Bestimmten Wert in Tabelle 1. Ist dieser forhanden, wird in Tabelle 2 weiter gesucht und dann ein neuer Wert berechnet(gebildet) und mit 2 weiteren Werten in Tabelle 1 upgedatet. An SP habe ich auch shcon gedacht, doch ich muß halt einen Wert vergleichen und neu ermitteln. Dies geschieht im Programm in 2 Functionen.

Ich bräuchte also mindestens 2 SP. Eine für den Download und eine fürs Update.

[mkinzler]

Wie wäre es damit, die daten Serverseitig auszuwerten und nur die benötigten an den Client zu senden ( so macht man es normalerweise)

[HeikoAdams]

Zitat:

Zitat von Ruewue

Hallo DeddyH, das stimmt so. Ich hole alle 2.2 Mio Daten runter auf den Client. Gehe dann jeden Satz einzeln durch. Ich suche erst einen Bestimmten Wert in Tabelle 1.

Das klingt so, als ob Du ein

SQL-Code:

SELECT * FROM <Tabelle>

machst und kein

SQL-Code:

SELECT * FROM <Tabelle> WHERE <Feld> = <gesuchter Wert>

Mit letzterem kannst Du Dir (und der CPU) wahrscheinlich eine Menge Arbeit sparen :wink:

[DeddyH]

Zitat:

Zitat von Ruewue

Ich bräuchte also mindestens 2 SP. Eine für den Download und eine fürs Update.

Du brauchst doch keine SP für den Download (ich wüsste auch gar nicht, wie das gehen soll). Die SP arbeitet die Daten ja serverseitig ab, so dass Du sie am Client überhaupt nicht brauchst.

[mkinzler]

Sonst würde ich noch zu einen Cross-Join-Raten um die Datenmenge noch etwas zu erhöhen :stupid:

[Ruewue]

Hallo und danke erst Mal.
komme nun erst wieder dazu.

Also ich brauche alle 2.2 Mio. Daten. Es handelt sich um die Neuzuordnung aller je gelaufenen Kollektionen in zwei neue Kategorien. Der Ablöauf sieht wie folgt aus:

SELECT Feld1_1, Feld2_1, Feld3_1, Feld4_1, Feld5_1 FROM Tabelle1

SELECT Feld1_2, Feld2_2 FROM Tabelle2 WHERE Feld3_2=Feld1_1

IF RECORDCOUNT=0 THEN
Vari1='RO'
ELSE
IF Feld1_2 > Feld5_1 THEN
Vari1='PO'
ELSE
Vari1='RO'

UPDATE Tabelle1 SET Feld6_1=Vari1, Feld7_1=Feld1_2, Feld8_1=Feld2_2 WHERE Feld1_1=Fled1_1 AND Feld2_1=Feld2_1 AND Feld3_1=Feld3_1 AND Feld4_1=Feld4_1

Der UPDATE dauert auf Grund der Datenmenge extrem lange.

Danke schon mal Im Voraus
RueWue

[DeddyH]

Nochmal: holst Du immer noch alle Daten zum Client, wertest sie dort aus und schickst die Änderungen zurück zum Server?

[mkinzler]

Der SQL Update Befehl kennt auch ein where :zwinker:

[HeikoAdams]

Ohne Dir jetzt zu nahe treten zu wollen, aber Du solltest Dir dringend mal die Dokumentation vom MySQL bzw. SQL ansehen. Solche SQL-Statements würde ich höchstens einem Azubi während der ersten 6 Monate seiner Ausbildung durchgehen lassen.

1. Was spricht dagegen, anstatt

SQL-Code:

SELECT Feld1_1, Feld2_1, Feld3_1, Feld4_1, Feld5_1 FROM Tabelle1

SQL-Code:

SELECT Feld1_1, Feld2_1, Feld3_1, Feld4_1, Feld5_1 FROM Tabelle1 WHERE Feldx_y = Gesuchter_Wert

zu schreiben?

Beim zweiten SQL-Statement

SQL-Code:

SELECT Feld1_2, Feld2_2 FROM Tabelle2 WHERE Feld3_2=Feld1_1

verwendest Du ja auch eine Where-Klausel, also ist Dir die Existenz dieser Funktionalität ja schon einmal bekannt. Also warum nutzt Du sie nur so sparsam?

2. Sorry, aber Where-Klauseln wie

SQL-Code:

WHERE Feld1_1=Fled1_1 AND Feld2_1=Feld2_1 AND Feld3_1=Feld3_1 AND Feld4_1=Feld4_1

kann man sich auch komplett weglassen. Das ist das selbe, als ob Du

SQL-Code:

WHERE 1=1 AND 2=2 AND 3=3 AND 4=4

schreibst.

[Ruewue]

@DeddyH: Ja ich hole immer noch alle 2.2 Mios runter auf den Client.

@mkinzler: Ich habe in meinem UPDATE ein WHERE


Gruß
Ruewue

[DeddyH]

Dann schau doch einmal in die Dokumentation zu MySQL bzgl. Stored Procedures. Es ist ja kein Wunder, dass das bisher lange dauert.

[Ruewue]

Danke noch mals.

Es scheint, als habe ich mich etwas falsch ausgedrückt. Ich kann in der ersten Tabelle keine WHERE Klausels setzen, da ich jeden einezelnen dieser 2.2 Mio Sätze neu definieren muss.

Ich hole mir aus der Tabelle1 den EAN einse Artikels um damit in Tabelle2 ein bestimmtest Datum zu suchen. dann vergleiche ich diese Datum mit dem aus Tabelle1. Ist es größe gibt es den Wert 'PO' ist es kleiner den Wert 'RO' gibt es diesen EAN nicht in Tabelle2 dann auch den Wert 'RO'.
Dann muss ich genau in diesen Datensatz diese Werte neu setzen, also 'RO' oder 'PO' und das Datum aus Tabelle2 sowie eine weiteren Wert aus Tabele2.

Ich habe Tabelle1 und Tabelle2 auch schon mit einen LEFT OUTER JOIN verbunden doch muss ich den UPDATE trotzdem ausführen.

Ich bin auch dabei dies im SP zu machen.

Noch zur Anmerkung! Ich muß das Datum aus Tabelle2 noch umwandeln in eine anderes Format, da beide nicht im gleichen Format vorliegen. Sonst klappt der Vergleich nicht


Danke!

[mkinzler]

Ich würde es mit 2 getrennten Update Statements versuchen

[HeikoAdams]

Zitat:

Zitat von Ruewue

Ich hole mir aus der Tabelle1 den EAN einse Artikels um damit in Tabelle2 ein bestimmtest Datum zu suchen.

Ich bin mir zwar nicht sicher, ob es sich positiv auf die Performance auswirkt, aber Du kannst die beiden ersten SQLs auch zu einem kombinieren indem Du folgendes schreibst:

SQL-Code:

SELECT Feld1_2, Feld2_2 FROM Tabelle2 WHERE Feld3_2 IN (SELECT Feld1_1 FROM Tabelle1)

[Ruewue]

@HeikoAdams:Danke! Ich habe es schon mit einem JION gemacht (Dauert ca. 98 sec.). Aber die Bremse ist halt der UPDATE auf jeden einzelnen Datensatz.


Danke

RueWue

[himitsu]

Zitat:

Zitat von Ruewue

(Dauert ca. 98 sec.)

Das sind bei 2.200.000 Einträgen doch "nur" 45 Microsekunden (0,000045 Sekungen) pro Eintrag und das findest du jetzt soooooooo langsam?

[HeikoAdams]

Zitat:

Zitat von Ruewue

@HeikoAdams:Danke! Ich habe es schon mit einem JION gemacht (Dauert ca. 98 sec.). Aber die Bremse ist halt der UPDATE auf jeden einzelnen Datensatz.

Okay, dann nochmal meine Frage:
Ist die Where-Klausel

SQL-Code:

WHERE Feld1_1=Fled1_1 AND Feld2_1=Feld2_1 AND Feld3_1=Feld3_1 AND Feld4_1=Feld4_1

im Update Dein Ernst? So wie ich das sehe, kannst Du die auch genau so gut weg lassen, was eventuell sogar noch ein wenig Zeit spart.

[Schorschi5566]

Warum sollte man das Ganze nicht in einem einzigen Update erledigen können?

Verstehe ich nicht so ganz. :stupid:


Oder vielleicht zwei Updates.

Mit dem ersten setzt Du Feld_6_1 auf 'RO' für die Bedingung Feld1_2 <= Feld5_1 und mit dem zweiten setzt Du Feld_6_1 auf 'PO' wenn Feld1_2 > Feld5_1 zutrifft. Warum musst Du dazu alle Datensätze auf den Client holen?