MySQL: Spalte auf einmal füllen, ohne iterieren zu müssen

[matlev]

Hallo,

ich habe ein Problem, dessen Lösung ich auch nach langem Suchen nicht gefunden habe. In meinem Java-Programm habe ich u.a. folgende Spalten:

name | zahlen1 | zahlen2 | ...

Unter "Name" habe ich mittlerweile tausende Einträge. Nun will ich eine neue Spalte, z.B. "zahlen5" einfügen, und diese gleich mit Werten füllen. Ich habe das bisher aus Java so gemacht, dass ich jedesmal folgendes ausgeführt habe:
UPDATE tabelle SET zahlen5 = x WHERE name = y
Dies mache ich dann in einer Schleife für jeden Namen y. Das Problem ist, dies dauert viel zu lange. Für meine 10000 Einträge brauche ich in etwa 5 Minuten.

Ich würde das gerne beschleunigen, in dem ich nur einen Befehl an meine SQL-Datenbank sende. Dieser sollte ungefähr so aussehen:

ALTER TABLE table ADD COLUMN zahlen5 VALUES (x1, x2, x3, x4, x5,...)
oder
INSERT INTO table (zahlen5) VALUES (x1, x2, x3, x4, x5,..)

Dann schreibe ich die Inhalte mit einem Befehl auf einmal. Hat jemand eine Idee, wie ich das mach kann, ohne in einer Schleife iterieren zu müssen?

 

[tfa]

Du kannst beim Anlegen einer neuen Spalte höchstens einen Default-Wert angegen, aber 5 Minuten für 10000 Einträge ist wirklich sehr viel. Machst du für jede Query eine neue Connection auf?

 

[robertpic71]

SQL Weg fällt mir da keiner ein. 10.000 Sätze auf diese Art updaten zu müssen, ist wohl die mühsamste Art.

Folgendes kann Batch-Updates stark beschleunigen:

1.) Autocommit ausschalten
Autocommit ausschalten und z.B. nur alle 500 INSERT und am Ende ein Commit machen

2.) PreparedStatement verwenden
Bei normalen Statements muss er 10.000 mal das SQL interpretieren, bei PreparedStatements nur die Werte.

3.) Batchupdate verwenden
Um die Kommunikation einzuschränken, zuerst z.B. 500 Statements sammel und dann aufeinmal mit ExcuteBatch ausführen. Das kann man gleich mit der COMMIT-Abfrage verbinden.

Natürlich können auch Fehler wie z.B. jedesmal eine neue Connection aufmachen usw. die Sache stark einbremsen.

/Robert

 

[robertpic71]

Das Wichtigste hab ich jetzt vergessen:

Wenn es bei UPDATE tabelle SET zahlen5 = x WHERE name = y

keinen Index über das Feld "name" gibt, machen die meisten Datenbanken eine "Tablescan" - sprich sie klappern alle Sätze sequentiell ab.

/Robert

 

[matlev]

Hey, die Tipps waren Gold wert. Jetzt geht es tatsächlich richtig schnell. Habe es nun folgendermaßen gelöst:

1) Ich benutze jetzt Batch-Updates

2) Ich habe Autocommit ausgeschaltet! Das war der wesentlichste Faktor. Seitdem dauert es nur noch wenige Sekunden statt viele Minuten

3) Ich habe das Feld <name> als Primary Key gesetzt

Außerdem habe ich meine SQL-Befehle schlauer eingesetzt, so dass ich für einen Datensatz nicht mehr drei Befehle brauche, um zu schauen ob der Name schon existiert um dann zu entscheiden, ob ich diesen aktualisiere oder neu anlege. Das geht mit einem einzigen Befehl auch:
INSERT INTO tabelle(name, zahlen5) VALUES ("meinname", 222) ON DUPLICATE KEY UPDATE zahlen5 = zahlen5 + 222

Ich hatte noch einige Probleme, bis ich herausgefunden habe dass ich auch con.commit() ausführen muss. Jetzt klappt alles super.

Danke Danke Danke!