Office: (Office 2003) Ausführen von Aktionsabfragen

Hi,

ich verfolge jetzt den Thread schon eine Weile...
ohne echten Lösungsvorschlag. Ihmo hat der Aufruf nur mittelbar mit dem Zeitproblem zu tun.

Habe aber ein paar Ideen.
Zum Testen:
1. die Abfrage gegen die Informix unabhängig vom Tabellenschreiben zu testen.
Achtung: in der Abfrage immer bis zum letzten DS durchgegehen (normal werden die ersten 1000 DS sofort geliefert), der 2. Durchlauf ist ggf. wesentlich schneller, da der Ausführungsplan gecached wird.
2. Tabelle lokal speichern und dann wegschreiben.
Jetzt sollte der Zeiträuber sichtbar werden.

Zum Netztwerk:
ist auf dem Weg eine Firewall?
Es gibt da ggf. Paketgrößenprobleme, die den Durchsatz wesentlich minimieren, ohne das die Netzlast überdimensional steigt (schlecht zu lösen)

Zur Performance (generell, keine Erklärung für die Änderung):
Ich würde die Abfrage als Pass Through gestalten, um sichher zu gehen, dass keine unnützen Daten transporiert werden. Wenn man die Datentypen auch noch vernüftig castet (Ganzzahl, wenig Strings, einfache accessbekannte Typen) wirkt das meistens Wunder und dann das SQL mit
Code:

starten.

mfg Carsten

 

\@Marsu:

Ja, das ist zutreffend, die "plötzlichen Performanceeinbußen" betrafen alle. Nur wirkt sich bspw. ein Faktor x bei bislang akzeptablen Antwortzeiten anders aus als bei mittelmäßigen bis schlechten. Das heißt, die Steigerung von 1 Minute auf ca. 3 wird als weniger störend empfunden als die von 3 auf ca. 10 Minuten.

Ja, so ist es, und so war es eigentlich auch immer schon, nur ... (s.o).

Nachdem ich mich in den letzten Tagen noch einmal ganz intensiv damit beschäftigt und viele verschiedene Varianten ausgetestet habe, komme ich zu dem Schluss, dass ich wohl nicht darum herum komme, die Ausführung der Abfragen von Execute auf RunSql oder OpenQuery umzustellen, da die wirkliche Ursache für die drastische Performance-Verschlechterung bei uns nicht feststellbar ist. Ich finde das aus diversen Gründen (s. Vorteile von Execute) unerfreulich, und aufgrund der ungeklärten Situation natürlich auch unbefriedigend, aber was soll's - der Anwender muss zufrieden sein!

 

Ja, das könnte ich, aber dieser Weg überzeugt mich nicht, da die Abfrage - direkt aus Access heraus gestartet - durchaus schnell ausgeführt wird.

Der Unterschied zeigt sich erst bei Ausführung der Abfrage via RunSql/OpenQuery oder Execute!

 

\@CSekulla:

Danke dafür!

habe ich gemacht, mit überraschendem Ergebnis, wie bereits berichtet.

Das fällt wohl unter das Thema: "Genau das will ich nicht (mehr)"

Firewall ist grundsätzlich vorhanden, wird aber über die freigeschaltete IP-Adresse umgangen.

Das hört sich ausgesprochen interessant an!

Die Idee hatte ich auch schon, brachte aber überraschenderweise keine Verbesserung.

 

Mit so einer Aussage kann ich nichts anfangen. Das Einfügen der Daten aus Informix in eine lokale DB teilt sich doch in zwei Teile:
1) Filterung der Daten und Transport in die lokale Umgebung (bei Jet-SQL mglw. in umgekehrter Reihenfolge, und Access benutzt erst einmal Jet-SQL)
2) Einfügen in die Accesstabelle (Schreiben)

Sinnvoll wäre es ja, die Vorgänge bewusst zu trennen und zu messen, um etwas tiefere Ursachenforschung zu betreiben. Diese testweise Trennung empfahl CSekulla in #16, und mein Vorschlag aus #9 (Umweg CSV) sagt ja nichts anderes.

Und pauschal hinzugefügt:
Code:

... steht nun nicht für eine Auswahl von 300k Datensätzen aus 3 Mio.

 

Dann will ich dir gern noch einmal erklären wie das gemeint ist. Im ersten Post beschrieb ich, wie ich eine Anfügeanfrage in eine externe Tabelle mit DB.Execute ausführe. Wenn ich genau denselben SQL-Code in einer gespeicherten Abfrage im Frontend einsetze und ausführe, so dauert dies maximal zwei Minuten und ist somit um etliches schneller als die Ausführung mit der Execute-Methode.

Was stellst du dir unter "Transport in die lokale Umgebung" vor? Es ist definitiv nicht so, dass das FE als "Zwischenspeicher" benutzt wird, sondern die Daten landen direkt in der Temp-DB - was ja auch der Sinn der Sache ist.

Das habe ich dann wohl einerseits nicht richtig verstanden und andererseits wüsste ich nicht was mir das bringen könnte, wenn doch die Abfrage an sich schnell genug ist und nur durch die Ausführung mit Execute derart lahm wird. Wie gesagt, dieser Effekt trat erst nach längerem Einsatz von einem auf den anderen Tag ein.

... steht nun nicht für eine Auswahl von 300k Datensätzen aus 3 Mio.

Das verstehe ich nun wiederum nicht. Im Original steht noch ein "Insert Into ..." davor und "a_tmp_tab1_auswahl" ist natürlich eine Auswahlabfrage.

 

Hi,

den 2. Teil habe ich wohl nicht verstanden.

Gibt es dazu evtl. noch weitere Hinweise?

Daran hatte ich auch schon gedacht, aber in der Auswahlabfrage werden Funktionen eingesetzt, die als PT natürlich nicht funktionieren.
Alternativ könnte ich den SQL-Code der PT-Abfrage vor Ausführung dynamisch erstellen, das wäre noch eine mögliche Variante.

 

Hi,
1. Ja war als Test gemeint zum Zeit messen, nicht in Produktion.
2. Genaueres kann ich dir da auch nicht sagen, nur so viel: wir haben einen OACLE-Server in einer DMZ und aus dem internen Netz ist der Datendurchsatz mies. Nach langem Suchen stellte die Firma fest, das die TCP-Blöcke zu kleine für ORACLE waren (was, wie, keine Ahnung, Problem ist bis heute nicht richtig gelöst)
Ihmo gibt es hier nur die Möglichkeit sich mit einem Laptop in das gleiche Subnetz hängen und dort probieren.
3. Du sagtest deine Access-Query ist nicht langsamer als die PassThrough.
Das gibt es, wenn ACCESS die Abfrage schon "vernünftig" an den Server schickt (Join und Where sind meist kein Problem)

Beim testen immer an das Caching denken und in die Abfrage eine Order By rein, so wird der ganze Recordset geholt.


p.s. ist das Ganze vielleicht Last-abhängig. Wenn viele Nutzer Tabellen sperren dauern die Abfragen länger. Bestimmte Querys mache ich nur Nachts.
cu CS

 

Hallo Carsten,

was ich z.Zt. mache sind ja alles nur Tests zum Zeiten messen - aber was meinst du jetzt mit "1."? Ich meine, auf welchen Punkt genau beziehst du dich da?
Ist es

dann weiß ich nicht, wie du dir das vorstellst.
Die komplette Abfrage inkl. Schreiben in die externe Tabelle braucht wie gesagt keine 2 Minuten, wenn sie aus dem Abfrage-Entwurf gestartet wird. Via Execute bis zu 12 Minuten und mehr.

Genau das habe ich doch in den letzten Tagen gemacht und am 14.01.2014 23:05 darüber berichtet.

Sorry, WO sagte ich das?
Ich schrieb doch, dass die Abfrage aufgrund der Funktionsaufrufe nicht PT-tauglich sei.

Nein, die Tests liefen zu verschiedenen Tageszeiten (also auch nach "Dienstschluss") ohne nennenswerte Unterschiede.

 

Sorry,
da habe ich doch nicht alles richtige gelesen. *upps
Zum Testen:
Mit 1. meinte ich meinen Vorschlag das SQLStatment zu trennen: in Abfrage und wegschreiben.
also etwa so
Code:

(ungetestet)

Zum Netzwerk:
ich gehe davon aus, dass dann das gleiche Subnetz genutzt wurde.
Dito kein Firewallproblem.

Zur PT:
wenn die Abfrage aus Access (ausreichend) schnell ist, kann also DAO und der ODBC-Treiber die Abfrage gut in Informix-SQL übersetzen. Ihmo bringt dann ein Pass Through nicht viel mehr.

(Neue?) Idee zum Abfrageaufruf:
Ich hoffe diese Variante noch nicht gelesen zu haben (sonst vergiss es)
Set qd = db.querydefs("DeineQuery")
qd.execute
So wird das interne prekompilieren genutzt.

cu CS

 

Hallo Carsten,

ich vermute, du meinst das so: Code:

wobei ich mir das Sortieren noch sparen würde.
Allerdings ist das genau der Weg, den ich nicht gehen möchte, weil ich das FE nicht "zumüllen" will.

Deine Vermutung zum Netzwerk ist richtig, gleiches Subnetz, kein Firewall-Problem.

Die Idee, über das QueryDef-Objekt zu gehen, hatte ich tatsächlich auch schon.
Wenn ich mich recht erinnere, waren die Antwortzeiten damit eher noch schlechter.

 

Hallo!

Irgenwie fehlt mir in diesem Thread der Überblick.

Ich versuche es einmal zusammenzufassen:
In einer Informix-DB liegen Daten, von denen ein Auszug in eine Access-Tabelle geschrieben werden soll.

Das Auslesen der Daten aus der Informix-Tabelle läuft ruck-zuck - aber beim Einfügen per insert into über db.Execute dauert es sehr lange.
Führt man die gleiche Insert-Into-Anweisung über eine Access-Abfrage (also über DoCmd.OpenQuery aus, wird sie um einiges schneller angefügt.

Passt das soweit?


Mich würde interessieren:
Wie lange dauert das Abfragen der Daten aus der Informix-DB, wenn du per Code ein Recordset öffnest und in diesem RS bis ans Ende springst?

Warum kann der Select-Teil für die Informix-DB nicht über eine (dynamische) PT gelöst werden?
Verwendest du Access-Funktion zum Filtern der Datensätze? ... dann hätte ich aber erwartet, dass bereits das Abholen der Daten etwas länger dauert, wenn ein Table-Scan zum Filtern verwendet wird.
Oder werden Joins auf Access-Tabellen benötigt?


Noch zum Ausprobieren:
Was wäre, wenn du statt insert into die Add-Methode von einem DAO-Recordset verwendest?
Das kann bei vielen DS eventuell sogar schneller laufen (weil DB-übergreifend), wenn du zwischendurch eine eigene Transaktion schließt und wieder öffnest.

Prinzip:
Code:

Anm.: Code natürlich optimieren .. also Feld-Referenzen (DAO.Field) verwenden und nicht mit rst.Fields(index) die Werte lesen/schreiben.

mfg
Josef

 

Hi Anne,

ich wollte erstmal "zum Testen" das Select vom Into trennen, um zu sehen
welcher Teil die meiste Zeit braucht. Das Order By ist nur dazu da,
den Server zu zwingen den ganzen Recordset zu senden.

cu CS

 

Hallo miteinander und vielen Dank, dass ihr noch nicht aufgegeben habt.

@Josef:

So ist es.

Nicht ganz. Das Ausführen der Anfügeabfrage aus dem DB-Fenster bzw. dem geöffneten SQL-Fenster heraus läuft relativ schnell, nicht aber bei Ausführung über DB-Execute.

Ja, so ist es.

Erstaunliche 19 Sekunden! (und weniger)

Das war ein Irrtum meinerseits, die zeitkritische erste Abfrage enthält nur feste Kriterien. Doch wenn ich die Abfrage starte (erstmal zum Test aus dem Entwurf heraus), hängt sich die Anwendung auf.

Ich hätte es nicht für möglich gehalten, wie schnell das sein kann! Nachdem ich noch ein bißchen mit verschiedenen Commit-Intervallen experimentiert habe, bin ich auf eine äußerst erfreuliche Ausführungszeit von ca. 1:10 Min. gekommen.

Hier habe ich keinen Unterschied feststellen können, so dass ich dann doch bei der "bequemen" Lösung mit der Loop geblieben bin.

@Carsten:

Auswahlabfragen kannst du aber nicht mit Execute ausführen und für OpenQuery brauchst du eine gespeicherte Abfrage.
Ok, das lässt sich lösen, aber ich bezweifle, dass die Sortierung die erwartetete Wirkung hat. Springe ich in der so geöffneten Abfrage auf den letzten Datensatz, vergehen weitere Sekunden.
Aber das bringt mich jetzt auch nicht weiter, denn dass das reine Öffnen der Abfrage - und sogar das Ausführen inkl. Speicherung - relativ schnell geht, hatte ich ja bereits festgestellt.

 

Hallo!

Hast du schon einmal im Jet-Showplan geprüft, was Access/Jet in Insert-Fall alles anstellt?
Irgenwie finde ich es eigenartig, dass Currentdb.Openrecordset("...") + MoveLast bzw. alle DS lesen flott abläuft, aber ein Insert in eine lokale Access-Tabelle dieser Select-Anweisung dann so lange dauert.
Müssen eventuell viele eindeutige Felder oder andere Regeln in der Access-Tabelle überprüft werden?
Kannst du die Insert-Anweisung einmal testen, wenn du in eine lokale Tabelle einfügst, die keinen einzigen Index außer einem Autowert-PK hat?

Nur zur Sicherheit, damit wir das gleiche meinen:

(Prinzip-Code):
Code:

... ist zwar etwas mehr Code (den man aber sowieso nur einmal schreibt), dafür muss nicht bei jedem Durchlauf die Fields-Auflistung durchsucht werden (Earlybinding vs. Latebinding).
... das wird aber nur bei sehr vielen Datensätzen bemerkbar sein.

mfg
Josef