TAdoTable.Open() sprengt Arbeitsspeicherlimit

[AxelO]

Hallo,

ich habe als Quereinsteiger ein großes altes Delphi-Programm übernommen, das über ADO auf Datenbanken aus je ca. 15 Tabellen zugreift. Ursprünglich war dieses Programm nur für Access-DBs gedacht, seit einigen Jahren kann es aber auch SQL Server. Die Tabellenstrukturen sind auf beiden Seiten identisch.

Im Moment habe ich folgende Situation:

• Ein Kunde möchte aus vielen kleinen Access-DBs einen einzigen großen Datenbestand auf einem SQL Server zusammenkopieren, der in einem Remote-Netzwerk "steht".
• Das Umkopieren erfolgt in einem historisch gewachsenen Mix aus Delphi/DB-API-Zugriffen und SQL-Befehlen. Das ist ein Legacy-Code, den ich bisher nur unvollständig durchschaue.

Der Showstopper ist nun folgender:

• Durch das Zusammenkopieren haben die Tabellen auf dem SQL Server inzwischen derart viele Datensätze, dass allein schon das Öffnen mit TAdoTable.Open() seehr lange dauert und bei einer bestimmten Tabelle sogar mit der Meldung „Zuwenig Arbeitsspeicher“ abbricht.
• Der PC selbst hat genug Arbeitsspeicher, es wird einfach die Obergrenze von 4 GB für 32-bit-Programme überschritten.
• Ich habe schon versucht, den Cursor der Server-Datenbank von client- auf serverseitig umzustellen (clUseServer). Dieser Modus wird von dem DB-Provider auch nicht zurückgesetzt, wenn ich das Open() ausführe. Demnach habe ich also eine gültige Kombination aus Cursorlocation und Cursortype gewählt.
• Aber auch mit dem serverseitigen Cursor wird erstmal ca. 15 Minuten lang versucht, die gesamte Tabelle ins RAM zu laden, und endet ebenfalls in diesem Abbruch wegen zuwenig Arbeitsspeicher.

Jetzt meine Fragen:

• Ich hatte es so verstanden, dass mit einem „serverseitigen Cursor“ das Open() nicht mehr versucht, die ganze Tabelle auf den lokalen PC runterzuladen. Tut es aber . Mache ich was falsch? Muss ich eventuell noch mehr umstellen? Irgendwas an der Connection? Oder braucht man doch einen dynamischen Cursor, statt "Keyset"?
• Kann man das Open() noch irgendwie beschleunigen?
• Was ist mit dieser neuen Schnittstelle, FireDAC? Macht die das grundlegend anders?
• Eine Umstrukturierung der DB, kommt m.E. nicht in Frage, da dazu Hunderte von uralten Stellen angefasst werden müssten. Selbst wenn, würde es m.E. nur das Speicherproblem lösen, aber nicht das Geschwindigkeitsproblem.
• Gleiches gilt m.E. für eine Umstellung auf 64 bit.

Gruß
Axel

[dummzeuch]

Bist Du sicher, dass das Programm die zur Verfügung stehenden 4 GB auch nutzt? Dafür muss im PE-Header das Large Address Aware Flag gesetzt sein, sonst nutzt es nur 2 GB.

Falls es nicht gesetzt ist, reicht es vielleicht ja schon, es zu setzen, um das akute Problem zu beheben?

[AxelO]

Zitat von dummzeuch:

Bist Du sicher, dass das Programm die zur Verfügung stehenden 4 GB auch nutzt? Dafür muss im PE-Header das Large Address Aware Flag gesetzt sein, sonst nutzt es nur 2 GB.

Falls es nicht gesetzt ist, reicht es vielleicht ja schon, es zu setzen, um das akute Problem zu beheben?

Ja, dieses Flag ist gesetzt (Compilerschalter IMAGE_FILE_LARGE_ADDRESS_AWARE). Ich kann auch im Taskmanager mitverfolgen, dass das Programm weit über 2 GB RAM Speicherverbrauch hinaus läuft. Der Abbruch kommt dann (z.B.) bei 3,3 GB, das variiert auch. (Wobei ich mich schon wundere, warum die Exception dann doch so "früh" kommt, also warum kommt sie nicht tatsächlich erst bei den vollen 4 GB...)

[Uwe Raabe]

Zitat von AxelO:

• Was ist mit dieser neuen Schnittstelle, FireDAC? Macht die das grundlegend anders?

Ein simpler Test mit einer TFDTable/TFDQuery Komponente für die betreffende Tabelle mit dem Speicherproblem sollte leicht zu bewerkstelligen sein.

Zur Kontrolle solltest du das auch mit einer TADOTable versuchen, nur um auszuschließen, dass andere Code-Sequenzen für das Laden der kompletten Tabelle in den Speicher verantwortlich sind.

[Delphi.Narium]

Statt TADOTable TADOQuery nehmen. Scheint sich (zuweilen) etwas dynamischer zu verhalten.

Benötigst Du tatsächlich alle Daten der Tabelle immer an "einem Stück"?

Was genau wird gemacht? Tabelle lesen und woanders schreiben?

Wenn die Daten nicht an einem Stück benötigt werden, sondern das Kopieren von A nach B auch in mehreren Schritten hintereinander (durchaus in einer Transaktion) erfolgen kann, dann verarbeite die Daten in einer Schleife. Gibt es einen eindeutigen Schlüssel (z. B. ID = 1:n), dann nimm immer ca. 10000 Sätze, also ID von 1 bis 10000, dann ID von 10001 bis 20000, ... Die Menge kannst Du natürlich variieren, indem Du erstmal versuchst herauszufinden, bei wievielen Sätzen der Arbeitsspeicher voll ist. Davon nimmst Du dann (vorsichtshalber) etwa die Hälfte.

Sieht irgendwie nach Gefrickel aus, hat aber in der Vergangenheit schon häufiger für Jahre (mit wachsenden Datenmengen) funktioniert.

[AxelO]

Zitat von Delphi.Narium:

Statt TADOTable TADOQuery nehmen. Scheint sich (zuweilen) etwas dynamischer zu verhalten.

Aha, interessant. Also die ADOTable einfach durch eine ADOQuery mit Abfrage "Select * from..." ersetzen, das ist dann gleichwertig? Die Query kann auch .Edit und .Append?

Zitat:

Benötigst Du tatsächlich alle Daten der Tabelle immer an "einem Stück"?

Denke schon, da in einem Zuge auf a) Vorhandensein und b) Gleichheit der jeweiligen Quelltabellen-Zeile in der Zieltabelle geprüft und dann ggfs. die Zeile entweder eingefügt oder aktualisiert wird. Dazu muss der Vergleich ja immer die Zieltabelle als Ganzes sehen, oder?

Zitat:

Was genau wird gemacht? Tabelle lesen und woanders schreiben?

Mehrere Tabellen aus einer Quell-Datenbank werden mit den entsprechenden Tabellen in einer Ziel-Datenbank abgeglichen. Wenn die Quellzeile in der Zieltabelle nicht vorhanden ist, wird sie eingefügt, ansonsten aktualisiert. Es wird in der Zieltabelle aber nicht gelöscht, also es ist keine vollständige "Synchronisation". Zur Ermittlung der Gleichheit zweier Zeilen dient (wo vorhanden) eine proprietäre UID, ansonsten inhaltlicher Vergleich aller Spalten. Das Einfügen bzw. Aktualisieren erfolgt aber nicht mit SQL-Insert- oder SQL-Update-Befehlen, sondern über die API mit .Append bzw. .Edit. (Geht Append/Edit überhaupt mit einer Query?)

Was ich mir noch an Alternativen überlegt habe, um um das .Open() herumzukommen:

- Aus den Access-DBs auf den SQL Server nicht direkt in die riesigen Originaltabellen kopieren, sondern erstmal in leere Dummy-Zieltabellen mit derselben Struktur, denn dann geht das .Open() ja schnell. Dann ein SQL-MERGE-Kommando absetzen, das jeweils die Dummytabelle in die Originaltabelle ein"merged" (oder Kombination aus INSERT und UPDATE). Dann ist ADO außen vor und der Server macht die Arbeit selbst. Allerdings müsste ich dann die Identitätsprüfung der Zeilen in SQL selbst formulieren, das dürfte auch haarig werden.
- Ginge sowas auch mit Stored Procedures? Nur eine vage Idee.

Zitat:

Wenn die Daten nicht an einem Stück benötigt werden, sondern das Kopieren von A nach B auch in mehreren Schritten hintereinander (durchaus in einer Transaktion) erfolgen kann, dann verarbeite die Daten in einer Schleife. Gibt es einen eindeutigen Schlüssel (z. B. ID = 1:n), dann nimm immer ca. 10000 Sätze, also ID von 1 bis 10000, dann ID von 10001 bis 20000, ...

Auch wenn das funktioniert, bleibt dann immer noch das Performanceproblem...

[mkinzler]

Zitat:

Denke schon, da in einem Zuge auf a) Vorhandensein und b) Gleichheit der jeweiligen Quelltabellen-Zeile in der Zieltabelle geprüft und dann ggfs. die Zeile entweder eingefügt oder aktualisiert wird. Dazu muss der Vergleich ja immer die Zieltabelle als Ganzes sehen, oder?

Hie würde sich eher update or insert oder merge anbieten

[himitsu]

Zitat von AxelO:

Die Query kann auch .Edit und .Append?

Natürlich ... so lange das SELECT der Query ein Rückschreiben erlaubt.
Notfalls kann man aber auch eigene INSERT/EDIT/DELETE-Statements angeben, wenn es gewisse Sonderfälle erfordern.

Eine Query mit SELECT * FROM deinetablle entspricht der Table.
Lädt also auch ALLES.

Teilweise kann man aber auch ein Nachladen/Paging aktieren (wenn DBMS, Treiber und QueryKomponente es ermöglichen)
Dann wird nicht sofort alles geladen, sondern erst nach und nach, während man mit Next durchläuft,
bzw. es wird immer nur ein kleiner Ausschnitt (Page) geladen und dann die Nächste und wieder freigeben.
z.B. bei Grids muß natürlich auch das Grid und sein DataControler es unterstützen, wenn man quasi nur den grade sichbaren Bereich geladen haben will.

Bei der Query kann man aber noch die Felder/Spalten auswählen/einschränken
und vor allem auch die Records/Zeilen, durch ein entsprechendes WHERE und/oder LIMIT.
Außerdem kann man JOINs einbinden und vieles mehr.

[Delphi.Narium]

Zitat von AxelO:

Zitat von Delphi.Narium:

Statt TADOTable TADOQuery nehmen. Scheint sich (zuweilen) etwas dynamischer zu verhalten.

Aha, interessant. Also die ADOTable einfach durch eine ADOQuery mit Abfrage "Select * from..." ersetzen, das ist dann gleichwertig? Die Query kann auch .Edit und .Append?

Ja, bei einem Select * from tabelle where irgendeinebedingung geht das problemlos. Bei Joins über mehrere Tabellen geht's nicht. Da TAdoQuery nur eine Tabelle kann, kannst Du das durch eine TADOQuery (mit 'nem entsprechenden Select) ersetzen.

Zitat von AxelO:

Zitat von Delphi.Narium:

Benötigst Du tatsächlich alle Daten der Tabelle immer an "einem Stück"?

Denke schon, da in einem Zuge auf a) Vorhandensein und b) Gleichheit der jeweiligen Quelltabellen-Zeile in der Zieltabelle geprüft und dann ggfs. die Zeile entweder eingefügt oder aktualisiert wird. Dazu muss der Vergleich ja immer die Zieltabelle als Ganzes sehen, oder?

Das kann man aber auch anders lösen. Tabelle A in sinnvollen Teilmengen laden (Query mit passender Wherebedingung) und satzweise durchgehen. Genau diesen einen Satz per weiterer Query in der Tabelle B suchen. Prüfen, einfügen und aktuallisieren dieses Satzes wie bisher.

Zitat von AxelO:

Zitat von Delphi.Narium:

Was genau wird gemacht? Tabelle lesen und woanders schreiben?

Mehrere Tabellen aus einer Quell-Datenbank werden mit den entsprechenden Tabellen in einer Ziel-Datenbank abgeglichen. Wenn die Quellzeile in der Zieltabelle nicht vorhanden ist, wird sie eingefügt, ansonsten aktualisiert. Es wird in der Zieltabelle aber nicht gelöscht, also es ist keine vollständige "Synchronisation". Zur Ermittlung der Gleichheit zweier Zeilen dient (wo vorhanden) eine proprietäre UID, ansonsten inhaltlicher Vergleich aller Spalten. Das Einfügen bzw. Aktualisieren erfolgt aber nicht mit SQL-Insert- oder SQL-Update-Befehlen, sondern über die API mit .Append bzw. .Edit. (Geht Append/Edit überhaupt mit einer Query?)

Das sollte mit oben beschriebenen Vorgehen realisierbar sein.

Zitat von AxelO:

Zitat von Delphi.Narium:

Was ich mir noch an Alternativen überlegt habe, um um das .Open() herumzukommen:

- Aus den Access-DBs auf den SQL Server nicht direkt in die riesigen Originaltabellen kopieren, sondern erstmal in leere Dummy-Zieltabellen mit derselben Struktur, denn dann geht das .Open() ja schnell. Dann ein SQL-MERGE-Kommando absetzen, das jeweils die Dummytabelle in die Originaltabelle ein"merged" (oder Kombination aus INSERT und UPDATE). Dann ist ADO außen vor und der Server macht die Arbeit selbst. Allerdings müsste ich dann die Identitätsprüfung der Zeilen in SQL selbst formulieren, das dürfte auch haarig werden.
- Ginge sowas auch mit Stored Procedures? Nur eine vage Idee.

Ja. Meine Devise war eigentlich immer: Was die Datenbank machen kann, lass die Datenbank machen, dafür ist sie da. Alles (über das Netzwerk) in den eigenen Arbeitspeicher holen, mit eigenem Programm verarbeiten und dann die Datenbank schreibenlassen, ist umständlich und aufwändig und niemals schneller als die Datenbank (auch wenn der Programmieraufwand in Delphi geringer erscheint).

Zitat von AxelO:

Zitat von Delphi.Narium:

Wenn die Daten nicht an einem Stück benötigt werden, sondern das Kopieren von A nach B auch in mehreren Schritten hintereinander (durchaus in einer Transaktion) erfolgen kann, dann verarbeite die Daten in einer Schleife. Gibt es einen eindeutigen Schlüssel (z. B. ID = 1:n), dann nimm immer ca. 10000 Sätze, also ID von 1 bis 10000, dann ID von 10001 bis 20000, ...

Auch wenn das funktioniert, bleibt dann immer noch das Performanceproblem...

Das liegt aber eventuell nur an der schier unüberschaubaren Datenmenge. Vermutlich haben beide Seiten nicht genug Arbeitsspeicher und müssen deshalb auf ihre Festplatten auslagern. Und die sind nun mal halt nicht unbedingt das schnellste Speichermedium. Wird virtueller Speicher genutzt (also Speicher, der physikalisch nicht vorhanden ist) wird's langsam.

Verarbeitungsmengen so wählen, dass der physikalische Speicher immer ausreicht, dann kann 1000 Mal ein Tausendstel durchaus schneller sein, als einmal alles, was ggfls. dann auch nur nach (gefühlten) Stunden mit 'ner Fehlermeldung scheitert.

[hoika]

Hallo,
forwardonly als CursorType?

https://stackoverflow.com/questions/...le-into-memory