"Bulk Update" MS SQL

Moin,

ich suche eine für uns sinnvolle Variante zum Massen-Updaten von SQL-Daten, das heißt zügig und am besten ohne Stored Procedure. Abgesehen von ein paar notwendigen Feldern, sind die Spalten im Voraus unbekannt.

Mein bisheriger Favorit wäre:

1. Temporäre Tabelle anlegen
2. Datensätze in Datatable laden
3. Werte lokal ändern
4. Bulkcopy in temporäre Tabelle
5. Wenn alles in temp. Tabelle geschrieben -> Update über Join
6. Drop temp. Tabelle

Ist das sinnvoll / macht man das so, oder gibt es andere, bessere Möglichkeiten?

Klingt fast nach dem Lösen eines Problems was z.B. durch OR Mapper bereits gelöst ist.
Wenn du nicht weißt welche Spalten geupdatet werden, dann klingt dies nach dynamischen Updates.
Sowas löst z.B. Entity Framework Core bereits für dich.

Ansonsten müsstest du einfach nur passende Update Anweisungen generieren.
Sind es mehrere, kannst du diese auch einfach in einer Transaktion sammeln und ausführen.

Die Frage ist allerdings was für ein Problem du konkret hast und was du damit lösen willst.
Wenn es ein Performance Problem ist, wie werden dann z.B. die Daten für das Update ermittelt (Where Klausel)?
Ggf. fehlt hier einfach ein Index?

T-Virus

Das konkrete Problem ist folgendes:

Es gibt ein paar ganz faszinierende Ini-Dateien, aus denen sich Daten, Bedingungen und Felder für das Update ergeben.
Ganz zu Begin wurde das einzeln aufgedröselt und dann pro Feld und Datensatz geschrieben. Wenn die Datenmenge dann steigt eskaliert das natürlich schnell und plötzlich ist man im Minutenbereich...

Ein paar Dinge sind direkt auf Feldinhalte zurückzuführen und können per WHERE in einem Rutsch abgehandelt werden, aber das sind auch erstellte codes bei, die für jeden Index anders sind...

Daher würde ich das gerne komplett umbauen.

Kann man das Entity Framework auch benutzen, wenn man das erst zur Laufzeit generieren kann? Dachte immer man muss bei "Database first" das Model zumindest bei der Programmerstellung erzeugen.

Jeder ORM braucht ein Mapping. Du brauchst also Modelle, die die Datenbankstruktur darstellen. Aber jede relationale Datenbank kann überhaupt erst benutzt werden, wenn es ein Schema gibt.
Ob die Modelle aus Database First oder Code First (Recommended) kommen - egal.
Der Unterschied ist, dass ein echtes Bulk invalidiert Daten in das Schema pumpt, und ein ADO.NET basierter ORM, zB Entity Framework, halt immer aktiv mit dem Schema arbeitet.
Ersteres ist, durch die fehlende Validierung, halt deutlich schneller.

Was Du hast ist auch kein klassisches Bulk. Denn Bulk ist eben einfach nur das unvalidierte Schieben von Daten 1:1.
Ich pers. seh jetzt primär nicht unbedingt, wieso das im Speziellen durch ein ORM gelöst ist - aber ich seh auch nicht, wieso man einen ORM dazu nicht nutzen kann, wenn das End-Ziel (Schema) eh klar ist, weil relational.

Es gibt ein paar ganz faszinierende Ini-Dateien, aus denen sich Daten, Bedingungen und Felder für das Update ergeben.

Hab sowas regelmäßig bei Maschinenbauern.
Wir machen das eigentlich so, dass wir die Daten alle lokal aufarbeiten - zum Beispiel durch Transformationen in XML-Dateien.
Und diese XML-Daten werden dann als Quelle für den (echten) Bulk verwendet.

Die Datenmenge entscheidet dann, ob man echtes Bulk verwendet oder halt ein ORM.
Wenn die Op jetzt nicht gerade mehrere Tage dauert, dann brauch ich keine Stunden in nen Bulk investieren (schreiben, testen.. = mehr Aufwand als bei EF), das mit EF in Minuten umgesetzt ist.

Jeder ORM braucht ein Mapping. Du brauchst also Modelle, die die Datenbankstruktur darstellen. Aber jede relationale Datenbank kann überhaupt erst benutzt werden, wenn es ein Schema gibt.
Ob die Modelle aus Database First oder Code First (Recommended) kommen - egal.

Wie macht man das denn, wenn man die Felder in der DB vorher nicht kennt? Dachte die Objekte spiegeln mit ihren Properties immer die vorhandenen Spalten wieder - oder eben umgekehrt. Dynamic Object, Dictionary?
Abgesehen schreibt man doch mit einem ORM auch per Object, oder? Wenn das super-lahm ist, würde es zwar richtiger werden, aber nicht schneller.

Hab sowas regelmäßig bei Maschinenbauern.
Wir machen das eigentlich so, dass wir die Daten alle lokal aufarbeiten - zum Beispiel durch Transformationen in XML-Dateien.
Und diese XML-Daten werden dann als Quelle für den (echten) Bulk verwendet.

Ja genau, in die Richtung geht das. Und weil ich schon mal per Bulk sowas in der Art für neue Datensätze gemacht hatte, dachte ich, dass es vielleicht besser wäre möglichst intern zu updaten und die Daten erst mal in den Server zu schieben.
Wenn ich so drüber nachdenke, wäre es vielleicht aber besser diese Dateien in eine Struktur zu bringen -> das in der Datenbank abzubilden und dann nur über Verweise zu schreiben...? Könnten komplizierte Queries werden, aber ich glaube das müsste gehen. 😐

Die Datenmenge entscheidet dann, ob man echtes Bulk verwendet oder halt ein ORM.
Wenn die Op jetzt nicht gerade mehrere Tage dauert, dann brauch ich keine Stunden in nen Bulk investieren (schreiben, testen.. = mehr Aufwand als bei EF), das mit EF in Minuten umgesetzt ist.

Ne Tage dauert das nicht, passiert aber ständig. Mittlerweile wartet da jemand schon mal Minuten mitten während der Arbeit (hab ich schon gesagt, dass da alles unfassbar langsam ist? 😄). Es wäre schön die Zeit zumindest auf Sekunden zu drücken. Der Server verarbeitet die Anfragen zwar schnell, aber von Senden bis Bestätigung empfangen vergeht einiges, daher tendiere ich dazu die Anzahl der Anfragen zu reduzieren aber den Gehalt aufzublasen. Gemessen habe ich die reine Verarbeitungszeit über DateTime-Abfragen am Anfang und Ende des Queries.

Wie macht man das denn, wenn man die Felder in der DB vorher nicht kennt?

Eine relationale Datenbank hat immer ein Schema mit festen Bezeichnern.
Du kannst keine relationale Datenbank anprogrammieren, dessen Bezeichner unbekannt sind.

Des weiteren braucht ein ORM immer Modelle, die dieses Schema abbilden.
Will man also typisiert arbeiten, müssen die Bezeichner vorher - auf irgendeine Art - vollständig bekannt sein.

Natürlich kann man das auch alles vollständisch dynamisch umsetzen.
Jedoch dann ohne ORM und ohne Typisierung - aber mit allen Nachteilen.

Die Frage wäre auch was bei euch Zeit frisst.
Wenn es quasi das Ping Pong zwischen Client und Server ist, dann kannst du dies durch zusammenfassen in einer Transaktionen verringern.
Wenn die Verarbeitung der Updates selbst die Zeit fisst, müste man eben wissen wie diese ausgeführt werden.
Wenn diese eben keinen Index verwenden, müssen auch große Tabellen seriell gelesen werden.
Das frisst dann nicht nur enorm die Performance sondern kann bei gleichzeiten Zugriffen zu Problemen und Wartzeiten führen.

Mein Gedanke mit dem OR Mapper war eher, dass durch Änderungen an den Objekten der OR Mapper selbst ermittelt welche Felder geupdatet werden müssen.
Dann würde er auch entsprechendes SQL generieren um nur diese Felder zu updaten.
Wenn aber dein SQL durch externe Dateien zusammen gezimmert wird, was mir erstmal nicht ganz geheuer ist, dann funktioniert dieser Ansatz nicht bzw. auch nur mit Aufwand.
Hier müsste man entsprechende Modelle anlegen, die schon dem DB Schema entsprechen.
Aber auch dann bleiben Probleme wie fehlender Index etc. als Performance Bremse vorhanden.

Mir ist aber noch nicht klar wie der Ablauf insgesamt ist.
Wie Abt schon schreibt, muss es ja ein Schema geben, da du nicht SQL mit unbekannten Tabellen/Spalten erstellst.
Dadurch ergibt sich ja schon ein gewisses Schema, die frage wäre nur grob wie dies aussieht.
Oder fahrt ihr hier z.B. eine Art Key/Value Struktur auf einer relationalen Datenbank?
Dafür gibt es dann bessere Ansätze als relationale Datenbanken im NoSQL Bereich.

Kannst du hier ein Sample für die Ini Datei und einer Update Abfrage liefern?
Vielleicht wird dann klarer was das Problem ist und wie man es ggf. anders/sauber löst.

T-Virus

@Abt
Da sich die Felder ändern können und die Dateien dann editiert werden, geht das dann so wohl nicht.

@T-Virus
Ja, ich gehe davon aus, dass es NICHT die Verarbeitung auf dem Server ist und eine Zusammenfassung helfen wird. Ganz grob überschlagen dauert jeder Query 150-200+ ms schonmal rein für die Anfrage an sich. Mündete dann in z.B. bei einem Vorgang pro Feld pro Datensatz in 200 ms * 100 * 20 > 6 min. Ich gehe davon aus, dass man das netzwerktechnisch schneller machen kann und ich gehe davon aus, dass das nicht passieren wird xD

Es ist alles indexed.

Key/Value Struktur klingt passend.
Die Rahmenbedingungen können nicht geändert werden. Es ist SQL, es gibt Einträge, die sich über tausende Zeilen wiederholen und eigentlich in eine eigene Tabelle gehören, was aber nicht geht, weil es wohl irgendein Programm gibt, das für irgendwas Daten ausliest und das nur aus einer bestimmten Tabelle kann... [Entwickler in Rente oder was weiß ich xD]
Also egal wie murksig das ist - ich kann es nicht ändern 😁

Ich habe jetzt schon mal ein paar Dinge rausgedröselt und das restliche Problem reduziert sich SQL-technisch schlicht auf:

Pseudo:

foreach(Item item in Items)
{

  //item.Data = Dictionary<string, string>
   [sql]
         Update table SET Feld1 = item.Data[V], Feld2 =  item.Data[W], Feld3= item.Data[X], Feld4 = item.Data[Y], Feld5= item.Data[Z] WHERE [Index] =  item.index
   [/sql]
}

Das möchte ich aber nicht, weil auch das dauern kann.

Daher war die Idee:
Alles in eine Temp-Tabelle mit Index, V, W, X, Y, Z bulken -> über den index joinen zum updaten -> tabelle löschen.

Es ist ja grundsätzlich richtig die reine Existenz dieser Dateien in Frage zu stellen - hilft aber nicht 😁

Also gerne nochmal: Du kannst eine relationale Datenbank mit einem Schema nicht blind anprogrammieren (außer natürlich dieses Schema hat keinerlei Regeln, alles ist untypisiert - geh ich mal nicht von aus).
Ein stupides UPDATE, das keinerlei Typen eines Schemas beachtet, knallt mit fast 100%tiger Wahrscheinlichkeit.
[Artikelserie] SQL: Parameter von Befehlen

Willst Du einen absolut generalisierten Weg einschlagen, dann musst Du eine Typerkennung nachbauen; => ein großer Batzen an Reflections und Expressions.
Im Endeffekt das, woraus zB bei EF Core Database First das Schema anhand von XML Dateien gepflegt wird.

Du musst Dich an die Regeln halten, wie SQL funktioniert - da kannst Dich wenden wie Du willst. Kannst natürlich sagen "Ich will das aber so!" - SQL wird antworten: "Nö."
Ob Du aber das Schema statisch hinterlegst oder dynamisch: Deine Sache.
Aber es wird kein Weg vorbei führen - und schwarze Magie gibts leider noch nicht.

Das möchte ich aber nicht, weil auch das dauern kann.

Auch die Eierlegende Wollmilchsau wirds wahrscheinlich nicht geben.

Wir können Dir nur den Weg sagen, aber nicht, was es für einen Aufwand bedeutet.
Das musst selbst für Deinen Fall individuell evaluieren.

Also entweder Prinzip-Hoffnung "wird schon so bleiben" und das so reinknüllen, wie ich das geschrieben hatte, damit leben, dass es langsam ist oder komplett neu aufziehen und dann eben 'richtig' machen.

Da wird es wohl auf "es bleibt alles so wie es ist" hinauslaufen.

Wenn die Abfrage im Kern immer gleich ist, dann ist es ein enormer Vorteil alles in einer Transaktion zu lösen.
Die Latenzen kommen auch zu einem nicht unbedeutenden Teil daher, dass z.B. die Anfrage erst über das Netz laufen muss und die Antwort wieder zurück.
Ebenfalls dauert auch die Verarbeitung der Anfrage, da diese vom SQL Server geparst und dann verarbeitet wird.

Wenn du auch SqlParameter verwendest, wovon ich erstmal ausgehen würde, dann sollte die Zusammenfassung dem SQL Server die Netzwerk sowie die Zeiten zum parsen der gleichen Abfragen ersparen.
Da du auch schon einen Index nuzt, kann die Ermittlung der passenden Einträge eigentlich auch nicht lange dauern.

Ich vermute, dass allein durch das Kapseln der Abfragen zu einer Transaktion die Zeiten nochmal fallen dürften.
Hat sich bei Massenupdates bei einigen Tausend Datensätzen, die per PK geupdatet wurde, enorm bemerkbar gemacht.
Auc spart man eben viel Ping/Pong zwischen Client und Server aus.

T-Virus

Ich vermute, dass allein durch das Kapseln der Abfragen zu einer Transaktion die Zeiten nochmal fallen dürften.
Hat sich bei Massenupdates bei einigen Tausend Datensätzen, die per PK geupdatet wurde, enorm bemerkbar gemacht.
Auc spart man eben viel Ping/Pong zwischen Client und Server aus.

T-Virus

So wurde daraus jetzt eine Mischlösung mit einer Teilzusammenfassung. Das hat die Zeit auf unter eine Minute gedrückt - das ist allerdings immernoch unangenehm, wenn man davor sitzt und wartet und der resultierende Query ist etwas "unübersichtlich" 😉

Bin wirklich kein Netzwerkadmin und meine Versuche zu verstehen, warum das da so lahm ist, haben nichts ergeben 😄.
Habe nur wiederholt festgestellt, dass ein Update eines Feldes in einem Datensatz > 100 ms dauert - in einem Netzwerk mit eigenem Server (also Hardware, nicht virtuell) und nicht über das Internet oder so. Eventuell so eine Security/Viren Scanner Geschichte...? Ich hab da keinen Plan von.

Dann solltest du dich mit den Admins bei euch mal abstimmen und prüfen ob es dort ggf. in den Logs Meldungen gibt.
Ansonsten bleibt eben auch die Frage wie die Performance der Abfrage auf dem SQL Server aussieht.
Dazu musst du dir entsprechende Ausführungsplane anschauen und gucken wo die Kosten verursacht werden.

Auch ist noch unklar wie die Abfragen aktuell aussehen.
Werde z.b. Updates auch mehrfach auf den gleichen Datensatz ausgeführt weil diese z.B. nicht zusammen gefasst werden?
Es wäre für die DB unnötig wenn du z.B. den gleichen Datensatz mehrfach bearbeitest weil einmal Feld A und einmal Feld B geupdatet werden muss.
Hier dürfte ggf. auch noch Potential für Optimierungen stecken.

Ansonsten müsste auch geprüft werden ob die Latenzen ggf. durch hohe Last auf dem SQL Server zustande kommen.
Wenn hier z.B. primär noch Festplatten zum Einsatz kommen, hat man schon eine Mindestlatenz von einigen Millisekunden.
Je nach Auslastung steigt diese dann auch noch zusätzlich.

T-Virus