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.

[TRomano]

Wenn ich das richtig verstanden habe läuft diese Stored Procedure einmal täglich mit einer Dauer von 15 sec. ? Lohnt sich da wirklich der Aufwand stundenlang zu Forschen, zu Proggen und zu Testen ? Oder ist das eine Fingerübung ?
Hat der DB-Server deine volle Aufmerksamkeit (keine anderen Jobs), sind die Parameter der DB so eingestellt, dass er bei bestimmten Operationen (z.B. ORDER BY) keinen Speichermangel hat und kann man vielleicht die Verarbeitung der Daten auf dem Client machen, um den Server zu entlasten. Alles Fragen, die gestellt werden sollten ...

[Jasocul]

Hallo Aviator,
das wäre nur eine sehr theoretische Möglichkeit, da ich selbst keinen Zugriff auf die anderen DB-Systeme habe.
So muss ich mit dem leben, was ich bekomme.

Zitat von Aviator:

OnInsert/OnUpdate Event eingebaut wird? Keine Ahnung ob das an der Geschwindigkeit was ausmacht, aber vielleicht kannst du es ja mal probieren.

Delphi ist da komplett außen vor. So ein Event gibt es also nicht. Die Überlegung das über Datenbank-Trigger zu machen, habe ich praktisch sofort verworfen, da die Daten im ersten Schritt über ein Bulk-Insert eingelesen werden. Die Trigger sind da meines Wissens nicht aktiv. Selbst wenn doch, würde dann der Cursor für die kleinen Tabellen jedesmal pro Datensatz neu erstellt werden, was die Performance noch weiter verschlechtert würde.

@TRomano:
Die 15 Sekunden erreiche ich nur, wenn ich die beiden Cursor zum Abgleich nicht verwende. Sobald die aktiviert werden, dauert es ca. 10 Minuten.
Wenn ich es vom Server auslagere, habe ich ja auch noch eine Schicht mehr.
Der Server hat zu dem Zeitpunkt nichts anderes zu tun, außer weitere Jobs durchzuführen, die aber sowieso sequentiell abgearbeitet werden müssen. Allerdings ist das auch der Grund, warum die Performance besser werden sollte.
Speicher ist genug da.
Im Moment habe ich einen Test-Server, der sehr ähnlich ausgestattet ist, aber wirklich sonst nichts zu tun hat.

[Aviator]

Zitat von Jasocul:

Delphi ist da komplett außen vor.

Das hatte ich mir gedacht. War nur dummerweise nicht mehr auf die Trigger Bezeichnung gekommen. Deshalb Event.