Office: (Office 2003) Ausführen von Aktionsabfragen

Hallo miteinander,

heute bitte ich mal wieder um einen Experten-Rat, und zwar geht es um eine Anfügeabfrage an eine Tabelle in einer externen DB.
In Anlehnung an den folgenden Beitrag zum Thema "SKAT - Abfrage mit Streichresultaten" setze ich in einer meiner Anwendungen eine temporäre Access-DB zur Auslagerung von Arbeitstabellen ein, die ich bei Programmstart erstelle und mit Inhalten fülle. Das Füllen der Tabellen erfolgt innerhalb der Startup-Prozedur mit Hilfe von Anfügeabfragen, welche bspw. folgendermaßen ausgeführt werden: Code:

Dabei handelt es sich um relativ große Datenmengen, die gelesen und von denen ca. 10% extrahiert werden. Diese Lösung setze ich seit ca. eineinhalb Jahren mit zufriedenstellenden Antwortzeiten ein. Seit kurzem jedoch sind die Ausführungszeiten besagter Abfrage um ein Vielfaches angestiegen. Bei der Suche nach einer Lösung des Problems bin ich auf folgendes gestoßen:
- bei Ausführung der Abfrage via OpenQuery reduzieren sich die Antwortzeiten auf ein gerade noch akzeptables Maß
- die Ausführung per RunSQL verhält sich ähnlich
- ein Querydef.Execute verhält sich dagegen ebenso lahm wie Database.Execute

Ich hatte Execute gewählt, weil damit eine Fehlerbehandlung möglich ist. Außerdem überschreiben mir die anderen Methoden die von mir gesetzte Statusanzeige mit dem Hinweis "Abfrage ausführen..." und dem dazugehörigen blauen Balken.

Hat jemand eine Erklärung dafür, warum die verschiedenen Methoden derart unterschiedliche Antwortzeiten liefern?
Und weiß vielleicht auch noch jemand näheres dazu, wie welche Methode genau funktioniert und warum man sich für die eine oder andere entscheiden sollte?

PS:
Den Link zum Original-Thread mit Noubas Musterlösung konnte ich leider nicht finden, auch hierzu wäre ich für einen Tipp dankbar.

:)

 

Wie drücken sich relativ große Datenmengen und Antwortzeiten in Zahlen aus?
Woraus importierst Du? Welches reale Backend wird verwendet?
Ist TmpDbPath recht lang?
Wie sieht die Tabelle genau aus, in die angefügt wird? Sprich: Wie genau wird sie erstellt?
Ändert sich was, wenn der DB-Bezug bei der Anfügeabfrage getauscht wird?
Ändert sich was, wenn die Temp-Tabelle wie normal ins FE verlinkt wird?

Gerade bei großen Tabellen wird die Abfrage an sich die meiste Zeit brauchen und der gewählte Aufruf dieser zu vernachlässigen sein (in meiner Vorstellung). Also keine Erklärung.

Noubas Musterlösung ist keine richtige Musterlösung. Da wird nur live bei Programmstart diese Temp-DB erstellt, das dauert nur Sekundenbruchteile.
Mit Martins Klasse (Neue DB temporär erzeugen) geht das nach meinem Empfinden sogar komfortabler, weil da z.B. die DAO-Referenz auf die Temp-DB gleich mitgeliefert wird.

 

Hallo Eberhard,

erstmal Danke für dein Interesse. Ich dachte mir schon, dass hierzu erstmal einige Nachfragen zu beantworten sein würden, aber ich wollte den ersten Beitrag nicht gleich überfrachten. *wink.gif*

Die erste Abfrage wählt ca. 300.000 Datensätze aus einer per ODBC verknüpften (Informix-) Tabelle mit über 3 Mio. Datensätzen aus, um mal eine Vorstellung von der Größenordnung zu geben. Diese Lösung setze ich seit ca. eineinhalb Jahren mit zufriedenstellenden Antwortzeiten ein. Seit kurzem jedoch sind die Ausführungszeiten besagter Abfrage um ein Vielfaches angestiegen, bspw. von 1 Min. oder weniger auf 6 bis 8 Minuten und mehr, je nach Arbeitsplatz (!), und das praktisch von einem Tag auf den anderen.
Da ich mit diesem Problem ziemlich allein dastehe (DB- und Netzwerk-Administratoren können keine Auffälligkeiten feststellen), habe ich nach einer Alternative gesucht und dabei festgestellt, dass die Abfrage mit identischem SQL-Code und direkt aus dem SQL-Fenster einer Abfrage gestartet, weniger als 1 Minute benötigt. Daraufhin kam ich auf die Idee, die Abfrage per OpenQuery auszuführen, was im Vergleich zu Execute ebenfalls zu wesentlich besseren Antwortzeiten führte.

Der Pfad zur Temp-DB ist nicht sonderlich lang und sieht auf Win7-Rechnern bspw. folgendermaßen aus:
C:\Users\xxxxxxxx\AppData\Local\VirtualStore\xxxx.tmpdb

Nach Erstellung der Temp-DB werden die Tabellenstrukturen (inkl. Indizierung diverser Felder) für die anschließenden Anfügeabfragen aus der Anwendung in die Temp-DB exportiert.

Sorry, aber diese Fragen verstehe ich nicht. *confused.gif*

 

zum Thema "lange Speicherpfade" habe ich kürzlich erst eigene Erfahrungen gemacht.
http://ms-office-forum.net/forum/sho...=304575&page=2
Kannst Du die Datenbanken testweise mal ins Root eines Laufwerks legen und von dort testen?

 

Bei einer mehr als Versechsfachung wäre ich auch ratlos. Die bemängelte Anweisung wird per DAO aufgerufen, bei den beiden anderen weiß ich nicht, wie das umgesetzt wird. Vielleicht könnte man auch mal ADODB testen.
Die Kombination Acc2003 und DAO 3.6 stimmt noch, oder ist da die ACE ins Spiel gekommen?
Mag der Informix ODBC Treiber DAO nicht mehr?

Das halte ich in allen Varianten für ungünstig. Indizes helfen beim Lesen, bremsen aber beim Schreiben, weil da datensatzweise der Indexbaum gepflegt werden muss. Wenn man erst die Daten anlegt und die Indizes nachträglich anlegt, passiert das deutlich schneller. Auch auf evtl. vorhandene Gültigkeitsregeln würde ich an der Stelle verzichten. Mit der Integrität der Daten dürfte man bei einem Auszug aus einer datenbank sicher keine Probleme bekommen.
Daher könnte man auch an eine Tabellenerstellungsabfrage denken, und die Indizierung erfolgt nachträglich per DDL. Gibt es dann auch Beziehungen?
Ich übe selber auch gerade die Kompletterstellung eines BE per DDL und DAO statt Kopieren und Umbauen. Da gewinnt man dann Freiheiten, um die Reihenfolge von Maßnahmen besser zu steuern.
Bei AiU gibt es ein Add-In SQLDump, das einem bei Auswahl einer Tabelle die DDL-Anweisungen zur Erstellung der Tabelle und der Indizes liefert. Das gibt sehr nützliche Vorgaben.

DB-Bezug bei der Anfügeabfrage getauscht:
Code:

Temp-Tabelle wie normal ins FE verlinkt:
Tabelle in Temp-DB per DDL oder per leerer TE-Abfrage erstellen, danach ins Frontend verlinken per TransferDatabase acLink, danach Anfügen usw.

In Jet-SQL (Seiten 8/9) schreibt Karl Donaubauer, dass die verwendete Einbindung der externen DB nicht so performant wäre. Daher auch mal die Variante mit IN sowie eben das übliche Verknüpfen testen. Solche Fälle hatte ich aber noch nicht, wo ich wegen Performance hätte da tauschen müssen, sondern nur den im von Dir verlinkten Fall, wo es um die Funktionalität des Verlinken an sich wegen verschiedener Versionen ging.

 

Hallo!

Interessehalber:
Da OpenQuery mit der gleichen Abfrage schneller als db.Execute läuft, könntest du auch probieren, ob eine explizit geöffnete Transaktion helfen würde.

Code:

mfg
Josef

 

OpenQuery stammt wie alle DoCmd-Methoden aus der Access-Bibliothek,
Execute ist ein DAO-Befehl.
Vorteile der Execute-Methode sind
- du brauchst die Warnmeldungen nicht abzuschalten (SetWarnings)
- du kannst eine Fehlerbehandlung verwenden (dbFailOnError)
- du kannst Transaktionen (inkl. Rollback bei Fehlern) verwenden

Egtl. liest man öfter, dass die Execute-Methode schneller sein sollte.
Bist du sicher, dass bei dir der Cache beim Vergleichen gelehrt war und nicht
für die unterschiedl. Ergebnisse verantwortlich ist?

Immer wieder stolpert man über den Hinweis, dass die Execute-Methode auf
DBEngine(0)(0) um einiges schneller läuft; kannst es ja mal versuchen.

Wäre vlt. lohnenswert zu erforschen, was sich geändert hat.
Z.B. rapide angestiegene Datensatzanzahl, neuer ODBC-Treiber,
Änderung der Verzeichnisstruktur, Netzwerkänderungen ...

Den Hinweis mit den max 8-Stelligen Verzeichnis- und Dateinamen würde ich
an deiner Stelle auf jeden Fall beherzigen.

 

Hallo miteinander,

vielen Dank für das rege Interesse. Leider bin ich noch nicht wirklich weitergekommen, aber hier meine Antworten im Detail:

Ich habe die Temp-DB auf "C:\" gelegt, das hatte aber keinerlei Auswirkungen.

das habe ich noch nie eingesetzt, müsste mich also erstmal einarbeiten.

Ich wüsste nicht, woher die ACE ins Spiel gekommen sein sollte, zumindest ist der Verweis auf die DAO-DLL vorhanden.

Daran wurde nichts geändert, der Informix-Treiber wird überal in derselben Version bereits seit längerem eingesetzt.

Ja, da habe ich auch schon dran gedacht, interessanterweise bringt eine Entfernung der Indizes keine nennenswerte Veränderung bzgl. der Anfügeabfrage.

Nein, Beziehungen gibt es nicht.

Interessanter Tipp, werd ich mir gleich mal merken.

-> keine Verbesserung

-> nochmalige "dramatische" Verschlechterung, die Laufzeit wird quasi nochmal verdoppelt

Darauf hatte ich zunächst wegen vermutlicher Aussichtslosigkeit verzichtet, inzwischen hat sich aber die Vermutung bestätigen lassen.

Leider ebenfalls kein Erfolg.

Ja, aber was steckt letztlich dahinter, was passiert bei OpenQuery und RunSql?

klar, genau darum habe ich sie ja ausgewählt. *wink.gif*

Was genau meinst du damit und wie kann ich das überprüfen?

Auch das bringt leider keine Veränderung.

Ja, das zu hinterfragen ist natürlich naheliegend. Alles was von mir (als Entwicklerin) überprüfbar ist, gibt jedoch keinen derartigen Hinweis. Der Datenbestand wächst ständig an, aber nicht unbedingt sprunghaft. Der ODBC-Treiber ist wie gesagt bereits seit längerem im Einsatz, ebenso die Verzeichnisstrukturen. Allein was das Netzwerk betrifft, so ist das für mich quasi eine Black-Box und ich bin auf die Auskünfte der Kollegen angewiesen (angeblich ist da alles im grünen Bereich).

Nun, zum einen setze ich eh den kurzen (DOS-) Pfadnamen ein und zum andern hatte ich ja auch mit "C:\" als Zielverzeichnis getestet.

Ich habe jetzt die Anwendung auf einem Laptop installiert und werde damit morgen mal ein paar Büros abklappern, um den Test dort vor Ort vorzunehmen. Ehrlich gesagt erwarte ich keine neuen Erkenntnisse daraus, aber auch das wäre schließlich ein Ergebnis. *rolleyes.gif*

Wenn noch jemand Ideen hat oder den Unterschied zwischen Execute und OpenQuery noch einmal genauer beleuchten kann, würde ich mich freuen.

 

Das wäre für mich der Hinweis, dass ein größeres Problem den eigentlichen Anfügevorgang dominierend überlagert.

a_tmp_tab1_auswahl ... ist eine verknüpfte View? (Reduzierung 3 Mio auf 300k)
Ansonsten: Was passiert mit einer richtigen Tabelle?

Was passiert über den Umweg CSV?

 

a_tmp_tab1_auswahl ist eine Abfrage im Frontend, welche sich auf per ODBC eingebundene Tabellen bezieht.

Was meinst du damit?

Wie könnte der aussehen?
Abfrage exportieren und wieder importieren? Müsste das im Zweifelsfall nicht noch schlimmer ausgehen?

Sorry für die dummen Fragen, aber mein Denkapparat scheint momentan etwas überstrapaziert zu sein.

 

Eine Auswahlabfrage muss ja erst einmal ausgeführt werden, ehe die ausgewählten Daten in die externe Tabelle eingefügt werden können (kostet ja auch Zeit).

Im Normalfall ja. Bei solchen "verrückten" Zuständen kann man aber mal wild herumprobieren. Manchmal gewinnt man überraschende Erkenntnisse. Die hattest Du eingangs ja auch schon.

 

Reihenfolge der Tests ändern bzw. vor jedem Test Access/Rechner neu starten.

Zur Eingrenzung:
Ergeben sich die selben Unterschiede, wenn du keine Temp-Db verwendest,
sondern die Daten in eine lokale Tabelle im Frontend schreibst?

 

Hi,

ich versuche mal, die letzten Beiträge zu beantworten, und evtl. weiterführendes Feedback zu geben.

ich habe das jetzt nicht noch einmal ausprobiert, weil das nicht meinem Ziel entspricht. So lief das früher. Aber dann wollte ich das FE vom Ballast der temporär erstellten Tabellen befreien, indem ich diese bei Programmstart in ein zur Laufzeit erzeugtes temporäres Access-Backend einfüge.
Dieses Verfahren lief bereits seit etlichen Monaten ohne Beanstandungen.

Ich habe die Abfragen heute wie angekündigt (via Laptop) an diversen Arbeitsplätzen getestet und weiß nicht so recht, was ich mit dem Ergebnis anfangen soll. Die Antwortzeiten waren durchweg schlecht, und zwar derart schlecht, dass sie überwiegend auf dem höchsten bislang bekannten Niveau lagen. Und das unabhängig von den "Steckdosen", in die das Netzkabel gesteckt wurde.
Was sagt mir das? Ist es ein Hardware-Problem?

Aber wie erklärt es sich dann, dass zwei baugleiche und zeitgleich beschaffte PCs in einem Raum deutlich unterschiedliche Antwortzeiten liefern? Ich weiß einfach nicht mehr wo ich ansetzen soll!

 

Ganz anderer Gedanke: Vergleiche DoCmd.TransferDatabase in DAO.Database-Objekt ausführen

Könntest Du in Informix Views anlegen (lassen), die die Filterung zur Datenreduzierung übernehmen, und dann diese ins Temp-BE kopieren?

 

Nu bin ich verwirrt *wink.gif*

Bislang wurden plötzliche allgemeine Performanceeinbußen und unterschiedlich
schnelle Zugriffs-/Ausführungsmethoden diskutiert.

Jetzt sind es (bei gleicher Anwendung und identischer Zugriffslogik?) Laufzeitdifferenzen auf unterschiedlichen Rechnern?(??)
Das solltest du vlt. genauer darstellen.

Ich interpretiere das jetzt so, dass nicht das Schreiben in die Temp-DB das Problem ist, sondern die Abfrage der 10%
vom Server unterschiedlich lange dauert?