Massenänderung mit SQL
-
Hallo Newsgroup,
ich programmiere eine Datenbankanwendung mit Visual C++, aber ich denke, meine Frage ist hier eher richtig aufgehoben:
Ich habe täglich zu einem bestimmten Zeitpunkt viele Datensätze zu ändern (2304) bzw. zu löschen (9216).
Wenn ich dies in einzelnen SQL-Statements durchführe, dauert dies sehr lange, da die Applikation über eine sehr langsame Netzwerkverbindung an den SQL-Server angebunden ist.
Für das Löschen habe ich etwas gefunden, um dies zu beschleunigen, nämlich eine Abfrage mit "in":delete [values] where [id] in (2399304,2399312,2399320,2399328,2399344,2399352...Hiermit werden 1152 Datensätze in etwas weniger als 2 Sekunden gelöscht.
Das Ändern von 288 Datensätzen mit:
update [values] set [value]=470 where [id]=2408808 update [values] set [value]=466 where [id]=2408848 update [values] set [value]=469 where [id]=2408888 update [values] set [value]=471 where [id]=2408928...hingegen dauert etwa 4 Minuten.
Daher meine Frage: Gibt es ähnlich wie für das Löschen mit ..in.. etwas, das "in einem Rutsch" viele Updates ausführt?
Vielen Dank für hoffentlich komende Ideen,
Andreas
-
Hmmm, da fallen mir zwei Sachen ein.
Das erste ist Compound-SQL. Ich weiss aber nicht, ob das bei deiner Datenbank geht.
Das ist im Prinzip nichts anderes als das Zusammenfassen mehrere Statements zu einem einzelnen "Statement", das in einem Rutsch zum DB-Server geschickt wird.
Das, zusammen mit Prepared-Statements und Parameter-Binding (damit die Statements von der DB nur einmal geparst werden müssen) dürfte am schnellsten gehen.
Also dein Statement
update [values] set [value] = ? where [id] = ?
Davon 100 Stück machen, die Parameter binden und die am Stück per BEGIN COMPOUND und END COMPOUND COMMIT rüberschicken. Musst mal schauen ob das bei deiner DB auch geht.
Die zweite Möglichkeit wäre, wenn deine DB das einfügen mehrere Datensätze in einem Statement erlaubt:
insert into [temp] (new_val, id)
values (470, 2408808), (466, 2408848), (usw...)Ich weiss dass das so bei MySql und DB2 funktioniert. Musst mal sehen, ob das bei dir erlaubt ist.
Das dürfte so schneller gehen, einfach die Datensätze mit einer Handvoll Statements in eine Temporäre Tabelle einzufügen, als 2000 Updates zu machen.
Das Update kannst du dann per Subselect auf die Temp-Tabelle machen:
update values set value = (select new_val from temp where values.id = temp.id)
So solltest du mit einer Handvoll Statements davonkommen
-
Hi frenki,
Danke für die schnelle Antwort.
ich arbeite mit dem MS SQL-Server 20000.
Soweit ich das durchschaut hat, funktioniert leider keine von deinen beiden (an sich sehr guten) Ideen mit diesem DBMS: Compound-SQL scheint es nicht zu geben, und das Einfügen mehrere Datensätze in einem Statement ist nicht möglich.Ich habe aber in der MSDN gelesen, dass es so genannte "Bulk-Copy-Operationen" gibt, nur verstanden habe ich das Ganze nicht so recht
Es scheint sich aber um eine Möglichkeit zu handeln, größere Datenmengen "in einem Rutsch" in eine Tabelle einzufügen.
Das könnte doch Deinen zweiten Vorschlag ergänzen...Weisst Du oder weiss sonst vielleicht jemand, was es mit diesen Bulk-Operationen auf sich hat und wie man diese implementiert?
Eventuell hat ja jemand noch eine Alternativ-Idee.
-
Hallo,
ich habe das mit den Bulk-Operationen jetzt doch rausgefunden...
Das läuft über eine Zwischendatei, in der die einzufügenden Daten in einem bestimmten Format vorliegen müssen.
Mal schauen, ob es klappt...
-
packst du die ganzen updates in eine transaktion?
überträgst du alles als einzelnen string (statt getrennt)?wenn das nicht hilft versuchs mal über ne stored procedure.