Array in SQL

[Jasocul]

Hinweis auf Cross-Posting:
Enwickler-Ecke

Ja, ich weiß, dass es in SQL keine Arrays gibt.
Mein Problem ist folgendes:
Ich benötige eine Stored-Procedure, die ca. eine halbe Million Datensätze verarbeitet.
Darin enthalten sind verschiedenste String-Operationen, die auf zwei Tabellen (weniger als 50 Datensätze) zurückgreifen, um z.B. Zeichen- und Wort-Ersetzungen durchzuführen. Dies ist für jeden Datensatz der großen Tabelle erforderlich und muss jeden Tag einmal durchgeführt werden (Datenabgleich zwischen mehreren, unabhängigen Systemen).

Damit ich auf die kleinen Tabellen zurückgreifen kann, habe ich 2 Cursor mit Scroll-Option definiert. Diese beiden Cursor sind aber mein Flaschenhals und ich würde hier gerne optimieren. Ohne diese Cursor läuft die SP weniger als 30 Sekunden, mit den Cursor fast 10 Minuten. Alle anderen Operationen in der SP habe ich zum Test rausgenommen.

Ein Versuch, innerhalb der SP eine Tabelle zu deklarieren und diese als Basis zu nehmen, hat keinen Geschwindigkeitsvorteil gebracht. Ich vermute, das die Datenbank intern eine temporäre Tabelle dafür aufbaut und somit genauso arbeitet, als würde ich die echten Tabellen nutzen.

Theoretisch könnte ich mir eine Menge Variablen deklarieren und das ohne Tabellen machen, aber dann müsste ich bei jeder Änderung der Tabellen die SP anpassen.

Hat jemand eine Idee, wie das ganze optimiert werden kann?

[rokli]

Moin Jasocul,

ich hatte vor einigen Wochen auch das Problem das große Datenmengen in Beziehung gesetzt werden mussten und die Verarbeitung elendig lange gedauert hat. War übrigens SQL2012

Im SQL Management Studio habe ich mir dann mal die Ausführungspläne angesehen, und der Ausführungsplan hat mir Empfehlungen für weitere Indices gegeben. Ich konnte durch den Einsatz eines ORDER(!) die Verarbeitungszeit drastisch senken ... von mehren Minuten auf einige Sekunden!

Vielleicht siehst Du dann einen Flaschenhals ...

Grüße
Rolf

[Jasocul]

Hi roldi,

es werden alle Datensätze aller genutzten Tabellen sequentiell durchlaufen und für den Test habe ich alles deaktiviert, was die Daten verändert. Ein fehlender Index ist ganz sicher nicht das Problem.

Ich habe aber jetzt zur Sicherheit noch einen Test-Durchlauf gemacht.
- Durchlauf der kann 500.000 Datensätze ohne einen Änderungs-Cursor dauert ca. 15 Sekunden
- Mit Aktivierung eines Änderungs-Cursor (ohne irgendwelche weiteren Aktionen) knapp 3 Minuten

[rokli]

Du durchläufst 500' Sätze und gehst bei jedem Satz die kleinen (Cursor-) Tabellen durch?

Kannst Du das evtl. anders miteinander verbinden?

[Jasocul]

Leider nicht.
Ich muss in 2-3 Feldern ein Wort-Splitting machen, dabei verschiedene Neben-Bedingungen beachten, bestimmte Wörter ignorieren/ersetzen. Teilweise betrifft das auch nur einzelne Buchstaben. Joins oder ähnliches sind auf diese Weise nicht zu realisieren, da die Inhalte durch die Operationen ja variabel sind.

Gebraucht wird das für:
Pro Datensatz wird dann je zulässigem Wort in eine neue Tabelle geschrieben/aktualisiert. Diese dient dann der Überprüfung, ob neue, ähnliche oder gleiche Daten bei der nächsten Daten-Lieferung kommen.

[rokli]

puh

[Aviator]

Zitat von Jasocul:

Leider nicht.
Ich muss in 2-3 Feldern ein Wort-Splitting machen, dabei verschiedene Neben-Bedingungen beachten, bestimmte Wörter ignorieren/ersetzen. Teilweise betrifft das auch nur einzelne Buchstaben. Joins oder ähnliches sind auf diese Weise nicht zu realisieren, da die Inhalte durch die Operationen ja variabel sind.

Gebraucht wird das für:
Pro Datensatz wird dann je zulässigem Wort in eine neue Tabelle geschrieben/aktualisiert. Diese dient dann der Überprüfung, ob neue, ähnliche oder gleiche Daten bei der nächsten Daten-Lieferung kommen.

Kann man diese Bearbeitung der Wörter evtl. in dem anderen DBMS machen indem ein OnInsert/OnUpdate Event eingebaut wird? Keine Ahnung ob das an der Geschwindigkeit was ausmacht, aber vielleicht kannst du es ja mal probieren.

[hanvas]

Zitat von Jasocul:

Hinweis auf Cross-Posting:
Enwickler-Ecke
Hat jemand eine Idee, wie das ganze optimiert werden kann?

Das naheliegendste, aber vermutlich bist Du darauf selbst schon gekommen -> Wenn die Zeichen und Wortersetzungen reproduzierbar bei gleichen Input immer den gleichen Output erzeugen (also nicht Abhängig von Tag, Uhrzeit etc. sind) und die 500 k Datensätze nicht jeden Tag neu erzeugt bzw geändert werden, dann müsste es doch möglich sein den Output einmal für alle Datenstätze vorrauszuberechnen und ggf. in zusätzlichen und von mir aus auch redundanten Feldern oder in einer eigenen Relation zu speichern.

Damit würde sich der Aufwand darauf reduzieren die neu hinzugekommenen oder geänderten Werte zu übersetzen. Je nach Aufwand kann man das durch einen Aufruf deiner SP machen oder direkt bei der Eingabe / Änderung mittels eines Triggers reagieren.

cu Ha-Jö

[Jasocul]

Ja, das habe ich natürlich gemacht.
Aber jeder Hinweis kann helfen. Könnte ja sein, dass ich mich irgendwo verrannt habe.

Die Dauer für die erste Erstellung der Abgleich-Tabellen ist ca. eine halbe Stunde.
Die tägliche Aktualisierung liegt jetzt bei ca. 7 Minuten.

[hanvas]

Zitat von Jasocul:

Ja, das habe ich natürlich gemacht.
Aber jeder Hinweis kann helfen. Könnte ja sein, dass ich mich irgendwo verrannt habe.

Dann hätte ich noch zwei.

1. In einem anderen Post hast Du geschrieben das es sich um einen Bulk-Insert handelt. Wenn ich richtig vermute liegen die einzufügenden Daten dann in irgendeiner Form extern vor. Wenn es sich bei den einzufügenden Daten (beispielsweise) um Textdateien handelt, dann könntest Du die Umwandlung/Übersetzung doch vor dem Einfügen der Datensätze in bearbeiten. Du könntest Dir ein Programm schreiben das die externen Daten übernimmt, die gewünschten Berechnungen durchführt und dann entweder in die DB schreibt oder ebenfalls wieder extern ablegt.

Wenn keine Abhängigkeiten zwischen den einzelnen Datensätzen existieren dann könntest Du die einzufügenden Daten aufteilen und in mehreren Threads bzw. Prozessen, wenn es sein muss sogar über mehrere Rechner verteilt, verarbeiten und nach der Verarbeitung die Originaldaten wieder zusammenfügen oder häpchenweise bearbeiten.

2. Falls die Datensätze aus mehreren Datenquellen kommen und nicht alle gleichzeitig behandelt werden müssen bzw. zu unterschiedlichen Zeiten zur Verfügung stehen, könnte es Sinn machen jede Datenquelle einzeln zu behandeln und das Ergebnis temporär abzuspeichern, und später alles zusammen, also wenn alle Daten vorhanden und umgewandelt sind, zu kopieren. Die Erzeugung der Zwischenergebnisse kann man im Hintergrund laufen lassen so das am Ende nur die Einfügeoperation der bearbeiteten Daten Last erzeugt.

3. (Abwandlung und Kombination von 1 und 2) Falls es mehrere Datenlieferanten auf mehreren Rechnern gibt und meine Annahme mit den externen Dateien richtig ist, wäre es auch eine Möglichkeit das jeder Datenlieferant bzw. jede Maschine vor dem Export der Daten diese selbst aufarbeitet und die aufgearbeiteten Daten an den DB Server liefert.

cu Ha-Jö