MySql: UPDATE über mehrere Zeilen

Ich stehe gerade etwas auf dem Schlauch .. ich habe folgenden table:
CREATE TABLE `user` (
`userID` int(5) unsigned NOT NULL auto_increment,
`erfahrung` int(4) unsigned NOT NULL default '0'
);
Im Script habe ich ein Array, dessen Schlüssel userIDs sind und die values die dazugewonnene Erfahrung. Ich wüsste, wie ich ein Query schreibe, wenn dieses Array als DB-Table vorhanden wäre aber so .. fiele mit nur die Variante in der while-Schleife ein, die ich nur äußerst ungern benutzen möchte. Noch eine Möglichkeit sehe ich darin, die Werte zuerst temporär in einen table zu schreiben und dann die oben angesprochene Query durchzuführen - aber so richtig passt mir das auch nicht. Würde die Angelegenheit aber immerhin auf 2 Queries begrenzen.
Habe ich ein Brett vorm Kopf oder geht es tatsächlich nicht?

Was spricht gegen die Variante der while Schleife? Vorzugsweise solltest dann Prepared Statements verwenden, sodass das ganze recht performant von statten geht.
Über eine temporäre Tabelle geht zwar auch, aber dann hast ja erstmal das Create der Tabelle, die Inserts, das Update und danach das Drop. Dürfte um einiges langsamer sein.

UDATE taXYZ SET spalte=wert
WHERE key IN(wer1, wer2)
dürfte klappen?

Original von Fobby
...dessen Schlüssel userIDs sind und die values die dazugewonnene Erfahrung...

Du berücksichtigst nicht die Werte. Aber das bringt mich auf diese Idee:

UDATE tabelle SET spalte = 

CASE WHEN key = schluessel1 THEN wert1
ELSE wert2
WHERE key IN(schluessel1, schluessel2)
Musst das Statement dann halt entsprechend zusammenbauen und für jeden zusätzlichen Schlüssel ein weiteres "WHEN" definieren. Aber dann hättest eine Lösung mit einem Statement. Ob das schneller geht als die Lösung mit dem Prepared Statement wüßte ich allerdings nicht.

Original von DrakeL
Was spricht gegen die Variante der while Schleife? Vorzugsweise solltest dann Prepared Statements verwenden, sodass das ganze recht performant von statten geht.
Über eine temporäre Tabelle geht zwar auch, aber dann hast ja erstmal das Create der Tabelle, die Inserts, das Update und danach das Drop. Dürfte um einiges langsamer sein.

Wie performant ist das denn? Habe mir die Grundlagen zu Prepared Statements gerade durchgelesen, klingt ja durchaus interessant. Nur habe ich antrainierte Aversionen gegen Queries in Schleifen .. weshalb ich dem etwas skeptisch gegenüberstehe.
@DrakeL:
Danke, das hilft mir. So geht es

Wie schnell ist relativ, kommt natürlich immer auf die Datenbank mit deren Einstellungen sowie vor allem auf die Menge der Daten an.
Aber im Gegensatz zu "normalen" SQL Statements in Schleifen sind die Prepared Statements deutlich schneller. Daher vor der Schleife das Statement vorbereiten und in der Schleife nur noch Variablen binden und Statement ausführen.
Ich würde dir einfach mal raten es auszuprobieren und die Zeit zu messen bei beiden Lösungen (wäre natürlich schön wenn diese dann auch hier postest). Dann siehst es am besten und hast einen Vergleich.
Bei der Lösung ohne Prepared Statement musst aber beim Zusammenpuzzeln des Statements darauf achten, dass es nicht zu groß wird. Es gibt bei manchen Datenbanken Begrenzungen in der Größe und Komplexität eines Statements. In wie weit dies bei MySQL speziell ist, weiß ich allerdings nicht. Aber solltest dahingehend bei größeren Datenmengen Fehler bekommen oder größere Mengen erwarten, solltest dich darüber informieren oder ausprobieren.

da du dich nicht im hochperformancebereich bewegen wirst, wird der zeitliche unterschied genau 0 sein.
ich gebe zu, das solche überlegungen generell sinnvoll sein können, um generell performanceschonend zu arbeiten. jedoch wird es im endeffekt keinen unterschied machen ob du ein wirres konstrukt mit 2 querys oder ein simples while insert machst. ich wage auch zu behaupten das dir das wirre konstrukt irgendwann bei einer erweiterung auf die füsse fällt.
ob du jetzt 2 oder 200 insert machst, fällt idr (vor allem bei solch einfacher tablestruktur) nicht auf

Und das wäre bei dir die Freikarte dafür ein "normales" Insert Statement in der Schleife zu benutzen statt ein Prepared Statement?
Klar bewegen wir uns nicht im hochperformanten Bereich, was allerdings nicht heißen soll, dass man die Performance verschwenden muss. Wäre wie wenn man generell immer ein "select *" macht und immer nur eine Spalte benötigt.
Außerdem sehe ich keine Zahlenangabe vom OP, dass er sich im Bereich 2 - 200 bewegt, es könnte genauso gut 2.000 - 200.000 sein und dann macht es sich sehr wohl bemerkbar. Aber warum eine Lösung der Anzahl der Daten anpassen...

nein das was in meiner aussage rauskommen sollte, war, das man sich nicht so sehr auf wirre konstruktlösungen stürzen soll, um 2 ms an perf zu spaaren.
ok die zeitliche reihenfolge der poste passt da nicht so ganz, war eigentlich eher auf die case / where sache bezogen.
wenn er prep statements einfach und schnell einsetzen kann und es für ihn keine grosse probleme bereitet und er mit der lösung leben kann, dann soll er das durchaus machen. er sollte nur eben nicht jetzt x mannstunden in die erspaarung von x ms cpu zeit investieren.

In welcher Sprache wird das Statement abgesetzt? Php oder Java?

Dann bin ich beruhigt.
Klar gebe ich dir Recht, wobei ich denke dass Prepared Statements allgemein kein Problem sein sollten, wenn diese von der verwendeten Datenbankschnittstelle (und der Datenbank) unterstützt werden. Denn genau für solche Fälle sind diese ja gedacht.
Ob der OP die komplexere Lösung einsetzen will muss er ja wissen. Er sieht ja ob die Performance schneller ist oder nicht und ob sich der Aufwand dahingehend lohnt dies einzusetzen.

Original von RaydenDD
In welcher Sprache wird das Statement abgesetzt? Php oder Java?

Spielt das eine Rolle? (nur so Interesse halber)

Ja, weil wenn es aus Java abgesetzt wird, könnte ich meine Lösung für das schnelle absetzten mehrerer updates/inserts posten, was kein Sinn macht wenn ers in Php braucht.
Weil ich nicht weiss wie das in Php aussieht, bzw. in reinem SQL.

Post es doch einfach und schreib dazu, dass es eine Java spezifische Lösung wäre. Würde mich jetzt auch interessieren wie diese aussehen würde. Vielleicht kann ich dann die PHP Entsprechung dafür nachliefern.

Ok hier isses, hat bei mir nen extremen Performancegewinn gebracht, da meine Datenbankverbindung übers Netzwerk geht.

DbConnect.getConnection().setAutoCommit(false);

PreparedStatement pstmt1 = DbConnect.prepareStatement(".....")

for (int currId=0;currId
pstmt1.setInt(1,[wert]);
...
pstmt1.setLong(8,[wert]);
pstmt1.addBatch();
}

pstmt1.executeBatch();

Statement stmt = DbConnect.createStatement();
stmt.execute("COMMIT");

DbConnect.getConnection().setAutoCommit(true);

Das "addBatch" ist mir nicht bekannt, ich denke aber mal es sammelt einfach die Werte um diese später komplett in einem Rutsch rüber schicken zu können und auszuführen.
Das würde aber glaube ich weniger Sinn machen, wenn die Datenbank auf der selben Maschine wie der Webserver ist. Eine PHP Entsprechung ist mir nicht bekannt, "mysql", "mysqli" und "pdo" haben hierzu nichts mir bekanntes. Aber vielleicht hat der OP ja Java.
Was natürlich auch Performance bringen könnte wäre das Autocommit abzuschalten und erst nach allen Inserts den Commit zu machen. Man muss nur beachten, dass bei sehr vielen Daten nicht die Transaktion überläuft (vielleicht alle 1000 Datensätze ein Commit machen). Aber ich denke der größte Performancegewinn für den OP wird das Umstellen auf Prepared Statements bringen.

Habe mich mal noch etwas weiter mit prapared statements auseinandergesetzt und kam auf die Idee, dass es ja schick wäre, diese über die Scriptlaufzeit hinweg erhalten zu können. Bei einer persistenten Datenbankverbindung, wie sie mit dem PDO leicht herstellbar ist, sollte das auch möglich sein - dachte ich jedenfalls. Der naive Versuch, ein Statement in eine Session zu packen, scheiterte an einem "fatal error".
Der Vollständigkeit halber hier mein Versuch:

if (!isset($_SESSION['teststatement'])) {
$statement = $db->prepare("SELECT name FROM skills WHERE skillID > :skillid");
$statement->bindParam('skillid', $someNumber);
$_SESSION['teststatement'] = $statement;
} else {
$statement = $_SESSION['teststatement'];
}
// resultiert in folgendem:
// fatal error: Exception thrown without a stack frame in Unknown on line 0
So geht es offensichtlich nicht. Gibt es dennoch die Möglichkeit, oft genutzte Queries irgendwie über die Scriptlaufzeit hinweg "vorzubereiten"?

SQL+?
Ich weiß aber nicht wie man die aus PHP raus anspricht, haben wir nie genutzt.
Wenn das Inserts sind, warum dann nicht ohne Prepared Statements und als erweiterten Insert?

INSERT INTO blub (id, text) VALUES
(1, blong), (2, blub), (3, bling);

Es sind Updates, keine Inserts. An die Lösung hatte ich natürlich auch ganz am Anfang gedacht

Original von Kampfhoernchen
SQL+?
Ich weiß aber nicht wie man die aus PHP raus anspricht, haben wir nie genutzt.
Wenn das Inserts sind, warum dann nicht ohne Prepared Statements und als erweiterten Insert?

INSERT INTO blub (id, text) VALUES
(1, blong), (2, blub), (3, bling);

Die Möglichkeit ist mir bewusst und so löse ich es auch oft. Aber das beantwortet meine Frage nicht.
Das schöne an prepared statements ist doch, dass die Datenbank einmal sämtliche Checks und Optimierungen vornimmt und dann bei jedem Aufruf über execute() lediglich das Query ausführt, was eine Menge Rechenzeit spart. Leider enden diese prepared statements mit dem Ende des Script.
Was ich suche, ist die Möglichkeit, diese Restriktion zu umgehen. Ich möchte das statement also auch bei weiteren Scriptaufrufen nutzen können, ohne es erst wieder mit Hilfe von prepare() vorbereiten zu müssen. Das würde die Performance sicher stark verbessern, denn es gibt einige Queries, die bei sehr vielen Klicks immer durchgeführt werden müssen.
Gibt es da eine Lösung mit oder ohne prepared statements? Ich habe bisher leider nichts finden können.

Wenn das dein Problem ist solltest du vielleicht eher über caching nachdenken als gegen Windmühlen zu optimieren

$_SESSION['teststatement'] = $statement;

Kann nicht funktionieren, da hier nur eine Referenz in der Session serialisiert wird.
Versuch es doch mit Stored Procedures.

Mit einem echten Application-Server wäre es nicht passiert (Siehe Signatur...)
Meines Wissens nach ist es nicht mögliche Stored-Procedures in PHP über Requestgrenzen zu schleppen.

ausserdem, sachen in ner session zu speichern um performance herzustellen und den standard sessionhandler zu benutzen ist ein kleiner wiederspruch in sich ^^
weil sessionaktionen immer einen io prozess darstellen, wo die sessiondaten auf die platte geschrieben werden/gelesen werden.
meiner meinung nach am ende der laufzeit des scriptes (bin mir aber nicht sicher)

Guck dir mal die set/get Funktionen an, wie sie eAccelerator oder APC bieten. Die speichern das global im RAM.