odbc: (zu) langsam, alternativen gesucht

[ipu]

Guten Morgen!

Euer fachlicher Rat und eure Erfahrung ist gefragt zur OBDC-Schnittstelle:

bisher gibt es 2 ACCESS-DB. DB1 ist aufgeteilt in Frontend und Backend. In DB1 werden täglich Daten aus DB2 importiert. Programmiert ist das mit VBA (DAO); läuft gut und in akzeptabler Zeit.

DB1 wurde jetzt auf eine Oracle-DB umgestellt. Der Import soll weiterhin täglich durchgeführt werden. Dazu habe ich das ACCESS-Frontend entsprechend angepaßt und über die odbc-Schnittstelle die Tabellen verknüpft. Beim Debuggen des Imports stellte ich fest, dass VBA zum Einfügen eines Datensatzes in einer Tabelle mitunter 2-3 Sekunden braucht. Bei einer kleinen Datenmenge mag das noch angehen, aber bei der Datenmenge, die täglich importiert wird, ist das indiskutabel!

Frage1: gibt es eine Möglichkeit, den Zugriff über ODBC-Schnittstelle zu beschleunigen? Würde es evtl. helfen, die Datenbank direkt in VBA anzusprechen, anstatt über die verknüpften Tabellen?

Frage2: kann der Datenimport mit PL/SQL programmiert werden? Davon habe ich nämlich (bis jetzt) noch keine Ahnung!

Es dankt im Voraus,

mit vielen Grüßen,

ipu

[Amstelchen]

verwendest du den odbc-oracle treiber von ms oder von oracle bzw.

welche versionen verwendest du denn?

hast du bzw. welches verbindungspooling hast du für die einzelnen treiber eingestellt?

welche einstellung beim batch autocommit mode verwendest du?

im ersteren fall (oracle odbc-treiber) versuch mal, mit den einstellungen Enable Result Sets und Enable Closing Cursors zu experimentieren.

achja, und zu deiner zweiten frage: musst du daten immer aus der access-db nach oracle pumpen? mir würde jetzt nämlich nur sql*loader einfallen, welcher allerdings über textfiles arbeitet und absolut nix mit odbc zu tun hat.

s'Amstel

[ipu]

Hi,

vielen Dank für die schnelle und informative Antwort!

verwendest du den odbc-oracle treiber von ms oder von oracle bzw.

welche versionen verwendest du denn?

Ich verwende den OBDC-Treiber von Oracle. Die Oracle-Version ist 9i, gilt das dann auch für den ODBC-Treiber?

hast du bzw. welches verbindungspooling hast du für die einzelnen treiber eingestellt?

welche einstellung beim batch autocommit mode verwendest du?

Sorry, da muß ich bei beidem passen. Ich hab keine Ahnung, was das ist und wo ich da was wie einstellen kann. Aber ich werde mich bemühen, mein Wissen zu erweitern

im ersteren fall (oracle odbc-treiber) versuch mal, mit den einstellungen Enable Result Sets und Enable Closing Cursors zu experimentieren.

Auch das fällt in die Kategorie :confused: (s.o.)

achja, und zu deiner zweiten frage: musst du daten immer aus der access-db nach oracle pumpen? mir würde jetzt nämlich nur sql*loader einfallen, welcher allerdings über textfiles arbeitet und absolut nix mit odbc zu tun hat.

Nee, das hat nix mit odbc zu tun, das stimmt. Die schon vorhandenen Daten in der ACCESS-Datenbank, die auf Oracle umgestellt wurde, habe ich schon mit dem sql*loader rübergeschaufelt, das war kein Problem, nachdem ich mich da erstmal mit beschäftigt hatte. Man kann ja auch mit dem sql*loader die Tabellen Updaten. Das hieße aber, die Daten von ACCESS nach ACCESS schaufeln, da dann wieder in .dat-Dateien exportieren und mit dem sql*loader die Daten nach Oracle rüberschaufeln und ... . Dat fand ich dann nich so wirklich gut!

grüße von ipu

[Amstelchen]

verwende den OBDC-Treiber von Oracle. Die Oracle-Version ist 9i, gilt das dann auch für den ODBC-Treiber?

prinzipiell ist bei der clientinstallation von oracle der odbc-treiber dabei (wenn er im oracle installer ausgewählt wurde), die versionsnummer ist bei 9i die 9.2.0 - es gibt auf der oracle-homepage allerdings neuere odbc-treiber (9.2.0.6 sollte die letzte version sein). versuch allenfalls mal die und lies dir die releasenotes dazu mal durch.

verbindungspooling:

damit ist eine option gemeint, welche angibt, nach wievielen sekunden gemeinsam genutzte (gepoolte) verbindungen geschlossen werden. verbindest du dich z.b. bei jedem update einzeln mit mit der db, dann kann der client schon manchmal in die knie gehn. im pool werden dann die verbindungen offengehalten und das spart an- und abmeldezeit.

batch autocommit:

gibt an, ob ein commit (also datensätze festschreiben) nach erfolgreicher sql-ausführung bei allen datensätzen erfolgt, oder nur bei bei fehlerfreien, oder bei allen bis zum ersten fehlerhaften sql.

Enable Result Sets, Enable Closing Cursors:

closing cursors, also zeiger zu schliessen, braucht auch zeit - das ist standardmässig auf off, kann aber manchmal sein, dass man cursors explizit schliessen muss. würde ich auf off lassen.

obiges sind alles spezifische einstellungen des oracle odbc-treibers.

Das hieße aber, die Daten von ACCESS nach ACCESS schaufeln, da dann wieder in .dat-Dateien exportieren und mit dem sql*loader die Daten nach Oracle rüberschaufeln und ... . Dat fand ich dann nich so wirklich gut!

seh ich auch so - ein zahnarzt arbeitet auch nicht durch die bauchhöhle des patienten.

ich hoffe, dir helfen die obigen angaben etwas weiter.

s'Amstel

[ipu]

ich hoffe, dir helfen die obigen angaben etwas weiter.

s'Amstel

Hi!

Ja, vielen Dank!! Ich habe zwar gestern auch versucht, über die Hilfe in der ODCB zu verstehen, wofür diese Einstellungen sind, aber dat hab ich nich verstandn. Ich werden dann deinem Rat folgen und verschiedene Konstellationen testen und die Zeit messen und sehen, was am besten läuft. Ich denke (und "fürchte") aber, dass ich um eine Neuprogrammierung des Datenimports in PL/SQL auf Dauer nicht drumherum komme ... .

Nochmals vielen Dank, hat weitergeholfen!

Grüße von ipu

[tuxfriend]

Hallo,

hast du schonmal mit der Anzahl der commits die du ausführst experimentiert?

Wenn auf jedes einzelne insert ein commit folgt, verlangsamt das den Prozess genauso wie wenn du nur nach dem komplett erfolgten import ein commit absetzt. Im ersteren Fall weil jeder einzelne Datensatz seperat gescrieben wird, im zweiten Fall weil die Redo Logs unendlich groß werden.

Hier müsstest du mit Transaktionen arbeiten.

Ich würde mit commits alle 100 - 1000 Datensätze experimentieren.

Ausserdem ist PL/SQL was den import von Massendaten angeht auch nicht immer die schnellste Lösung. Das ist dann bei mir immer der Punkt wo ich zum einem unserer Oracle Spezies gehe und mir etwas stricken lasse, das direct über OCI Direct Path arbeitet, das macht nämlich der SQL Loader auch und der ist nun wirklich ziemlich das Maximum an Performance.

Gruß

Nils

[ipu]

Hi,

jetzt habe ich alle Möglichkeiten, den ODBC-Treiber zu konfigurieren, unter mehr oder weniger gleichen Testbedingungen (Netzwerktraffic ist nicht beeinflußbar) getestet. Die Zeitdifferenz zwischen den Testläufen mit verschiedenen Einstellungen bewegte sich zwischen 1 und 10 Sekunden, meines Erachtens kein wirklicher Unterschied. Trotzdem danke für den Tip und die Infos; ich habe wieder was dazu gelernt!

hast du schonmal mit der Anzahl der commits die du ausführst experimentiert?

Nee, zumindest nicht bei meinem Import. Beim SQL*Loader habe ich damit experimentiert, mit Erfolg übrigens! Ich bin mir auch nicht sicher, ob und wenn dann wie ich damit bei meinem Import experimentieren kann, denn der Import ist mit VBA in ACCESS2000 programmiert, da die Daten bisher in einer ACCESS-Datenbank verwaltet wurden.

Wenn auf jedes einzelne insert ein commit folgt, verlangsamt das den Prozess genauso wie wenn du nur nach dem komplett erfolgten import ein commit absetzt. Im ersteren Fall weil jeder einzelne Datensatz seperat gescrieben wird, im zweiten Fall weil die Redo Logs unendlich groß werden.

Kann man denn beeinflussen, wann und wieviel in den Redo Logs reingeschrieben wird? Bisher dachte ich, dass der langsame Import vor allem an dem ODBC-Treiber liegt. Wenn ich den Import debugge, dauert ein Hinzufügen eines Datensatzes über die ODBC-Schnittstelle in die Oracle-Datenbank oft sehr lange (zw. 5 und 10 Sekunden), wo hingegen ein Hinzufügen eines Datensatzes in der Access-Datebank mit der gleichen Methode normal kurz dauert, mit anderen Worten sehr schnell.

Hier müsstest du mit Transaktionen arbeiten.

Ich würde mit commits alle 100 - 1000 Datensätze experimentieren.

Wo kann ich den Parameter setzen, so dass bei Ablauf des VBA-Programms das berücksichtigt wird? Das ist mir nicht klar.

Ausserdem ist PL/SQL was den import von Massendaten angeht auch nicht immer die schnellste Lösung. Das ist dann bei mir immer der Punkt wo ich zum einem unserer Oracle Spezies gehe und mir etwas stricken lasse, das direct über OCI Direct Path arbeitet, das macht nämlich der SQL Loader auch und der ist nun wirklich ziemlich das Maximum an Performance.

Der Oracle Spezi soll hier wohl ich werden ... . Leider gibts da keinen sonst hier, den ich fragen könnte :confused: .

Was verstehst du denn unter Massendaten? An PL/SQL hatte ich gedacht, um täglich pro Tabelle (20 Tabellen sind vorhanden) zwischen 10 und vielleicht 100 Datensätzen zu speichern. Das sind Daten, die unter bestimmten Kriterien aus einer ACCESS-Datenbank herausgesucht und dann in der Oracle-Datebank gespeichert werden. Solche Datenmengen dürften kein Problem sein, denke ich, oder? Und bisher ist die Perfomance über die ODBC-Schnittstelle wirklich grausam! Denkst du denn, das könnte an etwas anderem liegen als an der ODBC-Schnittstelle?

Grüße von ipu

[Amstelchen]

den sachverhalt mit den redo logs hat tuxfriend ja bereits gut dargestellt, ich möcht da was hinzufügen. ich seh nämlich das problem nicht nur in den redo-logs, sondern auch im undo (rollback).

jedesmal, wenn du einen datensatz mit insert oder update schreibst, wird die änderung zum vorigen datensatz festgehalten, und zwar:

die datensätze werden in einem oder mehreren redo logs (und noch nicht in die datendateien) geschrieben, welche wiederum später als archive logs archiviert werden. im undo-tablespace wird für jedes DML ein eintrag gemacht.

beispiel:

insert into blabla values ... -> 1 undo-eintrag

insert into blabla values ... -> 1 undo-eintrag

insert into blabla values ... -> 1 undo-eintrag

sind also 3. dann ein commit; -> undo wird gelöscht

machst du 50.000 inserts ohne commit, wächst undo an, machst du jedes insert als eigene transaktion (also nach jedem DML ein commit; wird jedes einzelne sql im redo abgelegt.

somit (siehe posting von tuxfried) empfiehlt es sich: commit nur alle paar hundert datensätze.

auf deine frage, wie man das in VBA berücksichtigt:

arbeite mit BeginTrans und CommitTrans, das können sowohl DAO als auch ADO.

als zusätzliche hilfe kannst du (falls du zeit und die lust hast), odbc-logging oder (aber nicht beides gleichzeitig) oracle trace und TKPROF verwenden.

s'Amstel

[Amstelchen]

nachtrag: optional kann man für tabellen nolooging bzw. direct-load inserts verwenden (nologging unterbindet das genereieren von redo, die tabelle ist dann allerdings nicht mehr wiederherstellbar, alter table blabla nologging), für direct-load muss ein parallelitätsgrad angegeben werden (alter table blabla parallel degree soundso). ob und wie weit das performancegewinn mit access bringt, ist dann allerdings ein anderes thema.

s'Amstel

[tuxfriend]

Nee, zumindest nicht bei meinem Import. Beim SQL*Loader habe ich damit experimentiert, mit Erfolg übrigens! Ich bin mir auch nicht sicher, ob und wenn dann wie ich damit bei meinem Import experimentieren kann, denn der Import ist mit VBA in ACCESS2000 programmiert, da die Daten bisher in einer ACCESS-Datenbank verwaltet wurden.

Wie das mit den Transaktionen funzt hat Amstelchen ja schon beantwortet.

Kann man denn beeinflussen, wann und wieviel in den Redo Logs reingeschrieben wird? Bisher dachte ich, dass der langsame Import vor allem an dem ODBC-Treiber liegt. Wenn ich den Import debugge, dauert ein Hinzufügen eines Datensatzes über die ODBC-Schnittstelle in die Oracle-Datenbank oft sehr lange (zw. 5 und 10 Sekunden), wo hingegen ein Hinzufügen eines Datensatzes in der Access-Datebank mit der gleichen Methode normal kurz dauert, mit anderen Worten sehr schnell.

Der Oracle ODBC Treiber bringt normalerweise wenig Performance Verlust. Es kommt nur darauf an was man damit tut und wie man es tut.

Hast du schon mal versucht, inserts direkt über den Oracle Enterprise Manager bzw. das SQL Plus Worksheet auszuführen? Die von dir angegebnen Zeiten deuten meiner Meinung nach auf ein Performance Problem in euerer Datenbank hin. Laufen beim insert irgendwelche Trigger ab? Wenn ja sollte man sich die vielleicht mal ansehen.

Der Oracle Spezi soll hier wohl ich werden ... . Leider gibts da keinen sonst hier, den ich fragen könnte :confused: .

Dann solltest du deinen Chef mal wegen eines Oracle Schulungskonzeptes anhauen. Für Programmierer die sich im Bereich Oracle spezialisieren ist eine OCI Schulung doch ziemlich wichtig. Ausserdem braucht ihr wohl einen Admin, der fit ist in Sachen Oracle Wartung und Tuning. Ansonsten habt ihr an dem System glaub ich nicht lange freude.

Was verstehst du denn unter Massendaten? An PL/SQL hatte ich gedacht, um täglich pro Tabelle (20 Tabellen sind vorhanden) zwischen 10 und vielleicht 100 Datensätzen zu speichern.

10 bis 100 Datensätze sollten weder in PL/SQL noch über normale ODBC Transaktionen ein Problem sein. Ich hatte schon mit Imports von 5000 bis 10000 Datensätzen zu kämpfen und dafür im Schnitt unter 1 sek. gebraucht. Sql*Loader ist genial!

Und bisher ist die Perfomance über die ODBC-Schnittstelle wirklich grausam! Denkst du denn, das könnte an etwas anderem liegen als an der ODBC-Schnittstelle?

Wie oben beschrieben: Das scheint mir nicht an ODBC zu liegen. Versuch mal mit oracle trace festzustellen, was da auf Datenbankseite passiert und mache die inserts tatsächlich übers Worksheet. Dann hast du jegliche ODBC Probleme ersteinmal zur Seite geschoben.

Falls dein Chef einverstanden ist, kannst du die Ergebnisse ja mal veröffentlich. Bin zwar Programmierer und kein DBA aber vielleicht kann hier ja sonst jemand helfen.

Gruß

Nils

[ipu]

Der Oracle ODBC Treiber bringt normalerweise wenig Performance Verlust. Es kommt nur darauf an was man damit tut und wie man es tut.

Hast du schon mal versucht, inserts direkt über den Oracle Enterprise Manager bzw. das SQL Plus Worksheet auszuführen? Die von dir angegebnen Zeiten deuten meiner Meinung nach auf ein Performance Problem in euerer Datenbank hin. Laufen beim insert irgendwelche Trigger ab? Wenn ja sollte man sich die vielleicht mal ansehen.

Mmh, das mit den Triggern werde ich ausprobieren. Das ist noch eine Möglichkeit. Ja, es laufen 2 Trigger ab. 1 Trigger zur Erzeugung einer ID und 1 Trigger für das AuditTrail. Den 1. Trigger kann ich nicht abschalten, aber den 2. Trigger; der wird für den Import im Prinzip sowieso nicht benötigt.

Dann solltest du deinen Chef mal wegen eines Oracle Schulungskonzeptes anhauen. Für Programmierer die sich im Bereich Oracle spezialisieren ist eine OCI Schulung doch ziemlich wichtig. Ausserdem braucht ihr wohl einen Admin, der fit ist in Sachen Oracle Wartung und Tuning. Ansonsten habt ihr an dem System glaub ich nicht lange freude.

Ja, da hast du wohl recht. Administriert hat bisher mein Chef die Oracle-DB. Ich bin ja auch keine Administratorin und will auch keine sein. Ich programmiere lieber. Und eine Schulung steht dieses Jahr noch an; ich wollte mich mit PL/SQL fortbilden.

10 bis 100 Datensätze sollten weder in PL/SQL noch über normale ODBC Transaktionen ein Problem sein. Ich hatte schon mit Imports von 5000 bis 10000 Datensätzen zu kämpfen und dafür im Schnitt unter 1 sek. gebraucht. Sql*Loader ist genial!

Ja, mit dem SQL*Loader ging das auch rabotti. Und das waren auch sehr viele Datensätze. Selbst die Daten aus der Tabelle mit über 300000 Datensätzen rüberzuschaufeln, war da kein Problem. Allerdings hatte ich da die Trigger deaktiviert ... .

Wie oben beschrieben: Das scheint mir nicht an ODBC zu liegen. Versuch mal mit oracle trace festzustellen, was da auf Datenbankseite passiert und mache die inserts tatsächlich übers Worksheet. Dann hast du jegliche ODBC Probleme ersteinmal zur Seite geschoben.

Falls dein Chef einverstanden ist, kannst du die Ergebnisse ja mal veröffentlich. Bin zwar Programmierer und kein DBA aber vielleicht kann hier ja sonst jemand helfen.

Vielen Dank! Ich werde dann weiterhin berichten, ich hoffe von Erfolgen !

Bis dahin,

Grüße von ipu