[NEWSboard IBMi Forum]
  1. #1
    Registriert seit
    Dec 2005
    Beiträge
    18

    Access Abfrage / Direkte Kriterien schnell / Aus Tabelle langsam

    Hallo AS400 Cracks, ich habe eine Performance Frage bezüglich eine AuswahlAbfrage und den Kriterien in Microsoft Access.
    Bei direkter Kriterieneingabe benötigt die Abfrage Sekunden, stammen die Kriterien aus einer Tabelle dauert es quasi Minuten.

    Tabelle "AMFIBU" als ODBC - Verknüpfung per IBM i Access ODBC Driver 13.00.16.00
    Spalte "AMKundennummer" für Kriterium und weitere Spalten deren Wert ich benötige
    Tabelle "tKundenNummern"

    Wenn ich per Auswahlabfrage für die Spalte AMKundennummer bei Kriterien untereinander 2800, 3600, 4200, 4344 eingebe und die Abfrage ausführe, habe ich innerhalb Sekunden alle Datensätze da.

    Wenn ich aber nun bei Kriterum sage das die Kundennummern aus der Tabelle tKundenNummern gesucht werden sollen mit "In (SELECT [AMKundennummer] FROM [tKundenNummern]), dauert die Abfrage fast eine Minute obwohl in der Tabelle exakt die gleichen Nummern drinnen stehen.

    Warum dieser extreme Unterschied bei der Performance ?

    Ich habe diese Frage auch schon in einem Access Forum gestellt, als Lösung wurde mir vorgeschlagen eine View zu erstellen um den Datenumfang einzuschränken oder mittels Passthrough zu arbeiten.

    Welche Möglichkeiten gäbe es da noch?

  2. #2
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Das Problem ist hier tatsächlich, dass du in der Abfrage 2 Tabellen aus unterschiedlichen Quellen abfragst.
    Access löst das Problem so, dass zuerst die Daten aus Tabelle A (AS/400) komplett geladen und anschließend mit Tabelle B (Access) verglichen wird.
    Was nicht benötigt wird, wird dann wieder verworfen.
    Dies erklärt halt auch die lange Laufzeit. Wobei 1 Minute bei wenigen 100 Sätzen ja noch geht.
    Stelle dir vor, du hättest eine Tabelle mit 20Mio Sätzen abgefragt.
    Access hätte erst mal 16Mio geladen, dann die 16 Mio entladen und anschließend einen Überlauffehler gemeldet;-).

    Wahrscheinlich wird es einfacher sein, per VBA die Tabelle B zu lesen und für jeden einzelnen Kunden die Abfrage aus Tabelle A aufzurufen.

    Alternativ lege für Tabelle B eine Tabelle auf der AS/400 an und mach nur eine verknüpfte Tabelle zur Tabelle B. Nun liegen beide Tabellen auf der AS/400 und Access löst die Abfrage tatsächlich mit einem Join auf.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  3. #3
    Registriert seit
    Dec 2005
    Beiträge
    18
    Hallo Fuerchau, vielen Dank für die Erklärung. Die erste Variante, per VBA für jeden einzelnen Kunden eine SQL Anweisung auf Tabelle A drüberlaufen zu lassen und die Datensätze an einer Access Tabelle anzuhängen habe ich verstanden.
    Aber die zweite Variante ist mir nicht so ganz klar. Tabelle B auf der AS400 anlegen, in Access verknüpfen, die Tabelle B anschließend mit den benötigten Kundennummern füllen und anschließend eine Abfrage erstellen wo Tabelle A und B mit den entsprechenden Spalten verknüpft sind und dann die Datensätze abfragen? Habe ich das so richtig verstanden?
    PS: Tabelle 1 hat über 2Mio Datensätze..

  4. #4
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Das ist so korrekt.
    Bei 2Mio Sätze hat Access da wohl etwas dazugelernt;-), was auch immer.
    Die 2. Variante hast du korrekt verstanden.
    Sinn und Zweck ist es, die Abfrage über 2 Tabellen von nur einem System durchführen zu lassen.
    Noch besser ist es, wenn über Tabelle A und B eine View per Inner-Join angelegt ist und du nur die View abfragen brauchst.
    Über die verknüpfte Tabelle B ist es einfacher, diese per Access zu befüllen, da jede Aktion direkt durchgeroutet wird.
    Bedenke allerdings, dass die Tabelle B multisessionfähig angelegt werden sollte. D.h., wenn mehrere Anwender mit deiner Tabelle B gleichzeitig umgehen musst du eine eindeutige ID in den Daten zur Abfrage hinzufügen.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  5. #5
    Registriert seit
    Dec 2005
    Beiträge
    18
    Bis jetzt habe ich den ODBC Zugriffsmodus immer auf Lesen/Ausrufen beschränkt...fühle mich da wohler da ich damit keinen Blödsinn anstellen kann *hüstel*
    Multisessionfähig wäre zwar nicht nötig da voraussichtlich nur ein Mitarbeiter damit arbeiten wird aber ich werde es berücksichtigen.
    Kommen wir zur meiner Achillesferse , ich erstelle seit gut 10 Jahren kleinere Statistiken und Queries
    aber bis jetzt musste ich mir nur Aliase für Teildateien anlegen, Views waren nie nötig da es im Falle immer eine passende Logische Datei gab.
    1. Ist mit View und Logische Datei eigentlich das gleiche gemeint?
    2. Ich werde mir erst mal versuchen eine View auf die Tabelle 1 anlegen um die Datensätze und Spalten einzuschränken, eventuell werden dadurch meine Performance schon Probleme behoben. Ich kann ja dann immer noch eine View über Tabelle1.View und Tabelle2 legen nach meinen bisherigen Kenntnisstand?
    3. Abseits der Problemstellung noch die Frage, kann ich per View auch mehrere Aliase zu einer Tabelle zusammenfassen?

  6. #6
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Ich weiß nicht wofür du Aliase benötigst.
    Heißt das, dass Ihr Daten einer Tabelle in mehreren Teildateien (Membern) untergebracht habt?
    Wenn du mehrere Aliase hast, die alle den identischen Satzaufbau haben, kann man diese per View mittels
    select * from AliasA
    union all
    select * from AliasB
    :
    zusammen fassen.
    Wichtig ist Union All, da damit alle Sätze ausgewählt werden, ansonsten werden Dubletten ausgefiltert.

    Performant können Abfragen nur werden, wenn sie aus genau einer Quelle zusammengefasst werden.

    Zum Thema Statistiken und kleinere Queries würde ich dir da eher zu einem BI-Tool raten. Damit macht man sich das Leben sehr viel einfacher und außerdem sieht's meist besser aus.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  7. #7
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    Aliase werden in erster Linie in Verbindung mit SQL Naming verwendet, wenn auf Objekte, die nicht im Default Schema vorhanden sind (unqualifiziert) zugegriffen werden soll.
    Das hat in erster Linie nichst mit Teildateien zu tun.

    Da SQL jedoch Teildateien, wie sie z.T. auf der IBM i immer noch verwendet werden, nicht kennt (partitioned Tables sind ähnlich, aber nicht ganz das gleiche), werden Aliasse gerne als permanente Objekte verwendet, um sich entprechende Overrides zu ersparen.

    SQL-Views sind eigentlich nichts anderes als gesicherte SQL-Statements und können alles beinhalten, was in einem SELECT-Statement zulässig ist, mit einer Ausnahme. ORDER BY ist in einer View-Definition nicht erlaubt.
    ... und auch der Zugriff auf ALIASse ist in einer View-Definition erlaubt.

    SQL-Views kann man mit ungeschlüsselten logischen Dateien vergleichen.

    Birgitta
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

  8. #8
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    "Aliase werden in erster Linie in Verbindung mit SQL Naming verwendet, wenn auf Objekte, die nicht im Default Schema vorhanden sind (unqualifiziert) zugegriffen werden soll."
    In diesem Fall nehme man einfach eine View mit "Select * from Lib/Table".
    Dies bietet sich auch bei Berechtigungskonzepten an, in der man bestimmte Tabellen in einer anderen Lib zum Lesen bereitstellen möchte.
    Ansonsten dienen Aliase eigentlich dazu, Remotezugriffe zu vereinfachen (wobei dann keine explizite Anmeldung per Connect möglich ist, siehe DDMF) oder eben Teildateien anzugeben (bei ODBC ist es mit dem OVRDBF nicht ganz so einfach).
    Natürlich kann man auch ganz normale PF's angeben, aber da finde ich persönlich Views übersichtlicher.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  9. #9
    Registriert seit
    Dec 2005
    Beiträge
    18
    Ok, jetzt kann ich wieder mal nachvollziehen wie sich manch Anwender fühlt wenn ich etwas erkläre
    Ne, ganz so schlimm ist es nicht und vielen dank für eure Infos
    Ein gutes BI-Tool haben wir Gottseidank schon im Einsatz (wobei das Tool von FTSolutions flexibler aussieht), nur wird immer wieder nach Daten verlangt wo man nicht darum herumkommt eigenständige Lösungen zu programmieren.


    Also Beispiel warum ich Aliases verwenden: Wir haben eine Datei names MAUFTRHIS, darin wandern nach 3 Jahren sämtliche historische Aufträge um das System performant zu halten. Und jedes Historische Jahr wird in eine Teildatei aufgeteilt. H2014 H2013 usw.
    Machmal will jemand eine Statistik welche auch historische Aufträge einschließt, per OCDB und Access und auch per SQL habe ich da keinen Zugriff. Darum der Umweg über Aliases.
    Das ich diese per View und union all zu einer File zusammenfassen kann wird mir bei manch zukünftiger Statistik nützlich sein.

    Ich werde jetzt mal meine erste View bastel und Vielen Dank für eure starke Hilfe hier

  10. #10
    Registriert seit
    Dec 2005
    Beiträge
    18
    Nachtrag: Eine View erstellt mit welcher ich die Datenmenge von 2+Mio auf ca. 300.000 eingrenzen konnte. Die identische Abfrage nur diesmal auf die View dauert nun 2 Sekunden
    Somit lohnt es sich gar nicht eine 2. Tabelle und ein entsprechendes View auf beide Tabellen zu legen. Aber wenn es mal um einen größere Datenmenge geht habe ich schon die entsprechende Lösung zur Hand.

    Danke!

  11. #11
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Das kann man mal analysieren wenn man Zeit hat. Wie gesagt, vielleicht ist die Access-Strategie da ja inzwischen optimiert worden.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  12. #12
    Registriert seit
    Dec 2005
    Beiträge
    18
    Ich werden zukünftig versuchen Access nur noch als Frontend zu betrachten und Daten und Tabellen auf der AS400 belassen. Mit den Views ergibt sich da neue Möglichkeiten samt einen deutlichen Performancegewinn. Wenn man aber nur alle paar Monate eine Statistik erstellen muss geht man zu gerne den einfachsten Weg

    Diesen Tip hab ich noch erhalten:

    ich würde empfehlen, die IN-Klausel gegen EXISTS zu ersetzen, was in der Regel viel schneller ist.

    Bei IN werden die Daten als Tabelle geladen und verglichen, bei EXISTS wird die Vergleichstabelle nur dazu verwendet, um zu testen, ob sie existieren, dabei müssen aber keine Daten geladen werden (weswegen bei EXISTS im SELECT normalerweise nur ein Dummy wie "1" steht).

    Also z.B. so:

    CODE: ALLES AUSWÄHLEN
    WHERE EXISTS (SELECT 1 FROM [020tDatenFuerFibu] WHERE AMERED500_BUBE51.BUBEPKTO = [020tDatenFuerFibu].[AUKDN])

Similar Threads

  1. SQLRPGLE: Abfrage als Tabelle
    By ExAzubi in forum NEWSboard Programmierung
    Antworten: 10
    Letzter Beitrag: 10-11-14, 11:12
  2. AS400 zu langsam?
    By infomio in forum NEWSboard Windows
    Antworten: 2
    Letzter Beitrag: 25-08-03, 09:50
  3. Mehrsprachigkeit einfach und schnell
    By Kilianski in forum NEWSboard Server Software
    Antworten: 1
    Letzter Beitrag: 11-10-02, 10:56
  4. MS Access SQL Abfrage über ODBC
    By Helwo in forum IBM i Hauptforum
    Antworten: 4
    Letzter Beitrag: 19-03-02, 10:01
  5. AS400 zu langsam?
    By infomio in forum IBM i Hauptforum
    Antworten: 8
    Letzter Beitrag: 19-06-01, 09:02

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • You may not post attachments
  • You may not edit your posts
  •