[NEWSboard IBMi Forum]
Seite 1 von 2 1 2 Letzte
  1. #1
    Registriert seit
    Jul 2014
    Beiträge
    17

    Optimale SQL Indexe

    Hallo,
    Ich hättee Fragen zu den SQL Indexes, vielleicht kennt sich da jemand gut aus?

    Für unsere WebQuery Auswertungen verwenden wir ausschließlich SQL-Views die die Daten bereits so liefern und aufbereiten das wir in WebQuery keine Joins usw. machen müssen.
    In Summe haben wir bereits 800-900 Views (viele davon verschachtelt)
    Die ganze Berechnungslogik ist ebenfalls in den Views enthalten.


    Wir wollen das jetzt etwas optimieren um bessere Antwortzeiten zu erhalten und haben
    uns dabei die Frage gestellt ob die Indexe überhaupt optimal erstellt sind.


    Nun zu meinen Fragen:

    • Wie kann ich feststellen welche Abfragen von noch nicht erstellten Indexes profitieren würden? Und welche Indexes das sind.
    • Wie kann ich feststellen welche bereits erstellten Indexes nichts bringen und daher gelöscht werden können?
    • Wenn ein Performance Problem mit einer bestimmten Abfrage besteht, wie analysiere ich das am besten im Detail?



    2015 wurde hier schon mal das Thema Indexes diskutiert. Damals gab es unterschiedliche Meinungen zu dem Thema. In der Zwischenzeit hat sich ja möglicherweise viel getan. Vielleicht gibt es heute einfachere bzw. bessere Möglichkeiten das zu analysieren?
    (z.B die QSYS2.SYSIXADV usw. auswerten, ACS SQL Performance Center, ...)


    lg Michael

  2. #2
    Registriert seit
    Aug 2006
    Beiträge
    2.073
    Na ja,
    wenn Du hiermit suchst findest Du schon was.
    Index-Advisor und Primary Key

    GG 3972

  3. #3
    Registriert seit
    Jul 2014
    Beiträge
    17
    Sorry, deine Antwort war sicher gut gemeint aber die hilft mir leider überhaupt nicht weiter.
    Wenn ich den Index-Advisor bei mir aufrufe bekomme ich knapp eine Million Einträge. Oben steht je nachdem nach welcher Spalte ich sortiere. Ausserdem wurde gerade hier im Forum von der Verwendung des Index-Advisors eher abgeraten.
    Ich suche nach einer effiziente Strategie nur jene Indexes zu finden bzw. zu erstellen die auch etwas bringen (in Bezug auf Performance). Bzw. jene zu finden die nichts bringen und daher entfernt werden können.
    lg

  4. #4
    Registriert seit
    Feb 2017
    Beiträge
    41
    Da könnte dir das SQL Perfomance Center > Show Statements im ACS weiterhelfen. Dort kannst du dir u.a. die durschnittliche Perfomance von Statements, sowie im Detail anzeigen lassen. Außerdem kannst du dir da die Statements auch "erklären" lassen und die Empfehlungen dazu ausgeben lassen.

    Gruß,
    Manuel

  5. #5
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Das Thema heißt eher "Visual Eplain". Dies kann man vom alten iSeries Navigator, oder wenn du bereits ACS hast über SQL-Script ausführen (Menü Aktionen in 5250-Sitzung), aufrufen.
    Dieser gibt dir eine Schätzung der möglichen Indizes.
    Allerdings haben mir die Ausgaben einer Debugsitzung da schon oft weitergeholfen:

    strdbg
    strsql
    select * from myview where ....

    Ansehen, was da so im Joblog steht.
    Allerdings: In 1% der Fälle wird der vorgeschlagene Index dann gar nicht genommen.

    Indizes ist das Eine, eine qualifizierte Abfrage von Feldern ohne Cast bringt dich da auch oft weiter.
    Wenn bei gewachsenen Systemen die Felder z.T. unterschiedlich definiert sind (char vs nchar, decimal vs zoned, decimal vs integer, usw.).
    Statt eines simplen Joins "on a.f1 = b.f1" hilft da schon mal, das Herkunftsfeld zu casten: "on dec(a.f1, n, m) = b.f1".
    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

  6. #6
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    SQL Performance Optimierung ist nicht ganz so einfach auch wenn wir inzwischen im SQL Perfomance Center gute Tools haben, musst Du verstehen wie SQL arbeitet, um dann auch die richtigen Indices zu finden.

    Der Index-Advisor hilft schon weiter. Wenn Du allerdings noch nie in den Advisor reingeschaut hast (was ich vermute) sind da sämtliche von beiden Query Engines empfohlene Indices seit Release V5R2M0 (bzw. seit der Einführung des Index Advisors).

    Der Index-Advisor kann im übrigen auch gecleart werden. Am Besten machst Du mit ACS (Access Client Solutions) alles platt, wartest 2-3 Wochen und schaust nochmals rein.
    Im Übrigen können auch über ACS die SQL-Statements, für die die Empfehlung ausgesprochen wurde (incl. der verwendeten Variablen) angezeigt und geöffnet werden (zumindest solange kein IPL gefahren wurde).

    Des weiteren kannst Du Dir im Plan Cache alle Access Pläne für alle SQL Statements, die seit dem letzten IPL ausgeführt wurden anzeigen, incl. der durchschnittlichen und längsten Laufzeit anzeigen lassen. Die problematischen SQL-Statements kannst Du dann einzeln greifen, aufrufen, analysieren (mit Visual Explain), neue Indices erstellen und im Anschluss das SQL-Statement erneut ausführen.

    Ansonsten solltest Du Dir mal die Indexing und Statistics Strategy von Mike Cain und Kent Milligan reinziehen.

    Es gibt eigentlich nur 2 Schrauben an denen man für eine bessere SQL Perforance drehen kann:
    1. an der Syntax (wird das SQL-Statement so geschrieben, kann kein Index verwendet werden, wird es anders geschrieben kann der Optimizer einen Index verwenden)

    2. Erstellen der optimalen Indices.
    Dabei sollte man allerdings nicht nur "normale" Binary Radix Tree Indices im Auge haben, sonder auch derived und/oder sparse Indices und natürlich auch Encoded Vector Indices.

    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

  7. #7
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Nun ja, wenn man sich das so durchliest, sind EVI's eher kontraproduktiv, da sie für Joins nicht nutzbar sind und sehr viel Platz benötigen.
    Ich bin auch mit den bisherigen Methoden ganz gut gefahren. Ich habe nur selten Queries gesehen, die nicht mit Joins umgehen müssen. Auf die Typisierung bei der Abfrage wird gar nicht eingegangen.
    Da habe ich schon festgestellt, dass die automatische Anpassung (Cast) seit V6 z.T. schlechter läuft als vorher. Der Optimizer castet eher den Feldinhalt zum Abfragewert als umgekehrt, was wiederum eine Indexverwendung verhindert. Beispiel, XFeld ist vom Typ CHAR(3):

    Select * from mytable where XFeld = 001

    Bis V5R4:
    Select * from mytable where XFeld = cast(001 as char(3))

    Seit V6R1:
    Select * from mytable where cast(XFeld as decimal(3, 0)) = 001

    Wir haben das nur bemerkt, dass bei der damiligen Umstellung auf V6 plötzlich Queries/SQL's nicht mehr funktionierten (XFeld hatte schon mal nicht numerische Werte) oder extrem langsam wurden (keine Indexnutzung), nur weil
    a) die Programmierer die Hochkommas vergessen hatten
    b) die IBM den Cast umgedreht haben

    Also auch hier kann man dem Optimizer helfen, wenn man Vergleiche (Join/Where) korrekt selber per Cast anpasst um Indexnutzung zu erreichen.
    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

  8. #8
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    @Baldur:
    Wer außer Dir behauptet denn, dass EVIs sehr viel Platz benötigen?
    EVIs sind im Gegenteil sehr viel kompakter als Binary Radix Tree Indices
    ... außerdem werden Binary Radix Tree Indexes nur verwendet, wenn max. ca. 15% der Daten einer Datei gelesen werden. EVIs decken den Bereich >15% bis ca 80% der Daten ab.
    Des weiteren können in den Encoded Vector Indices Aggregat-Informationen gespeichert werden, so dass z.B. beim richtigen EVI mit den richtigen Aggregat-Informationen und der richtigen Gruppierung (Group By) die Daten direkt aus dem Symbol Table des EVI ausgelesen werden können, d.h. es ist also kein Zugriff auf eine Tabelle notwendig (EOA - Encoded Vector Index Only Access).

    Außerdem ist der Query Optimizer in der Lage für eine Abfrage für ein und die selbe Datei mehrer Indices zu verwenden (Index-ANDING und Index-ORING)

    Im Übrigen sollte man natürlich sauber kodieren und nicht die IBM für Schlampereien verantwortlich machen wollen.
    Wenn ein Feld alphanumerisch definiert ist, sollte man in der Where-Bedinung die Konstante in Hochkommata setzen bzw. in einem Programm auch die Host-Variable entsprechend definieren, auch dann wenn die Kunden-Nr. immer nummerisch ist.
    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

  9. #9
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    Bzgl. der EVI's hast du ja den Link auf das Dokument mitgeteilt. Da steht immerhin drin, dass EVI's sehr viel Platz benötigen und somit u.U. ineffektiv werden können. Von alleine wäre ich da nie drauf gekommen.

    Und was das Beispiel angeht, so wollte ich damit nur auf das Umdrehen des Cast hinweisen, der zur Nichtverwendung von Indizes führt, nachweislich.
    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

  10. #10
    Registriert seit
    Jul 2014
    Beiträge
    17
    Hallo Manuel,
    Das Performance Center ist mir auch schon aufgefallen. Gibts da irgendwo eine ausführliche Beschreibung dazu? Vielleicht anhand von Beispielen wie man damit richtig umgeht und wie man die ermittelten Werte auch richtig interpretiert?

    lg Michael

  11. #11
    Registriert seit
    Jul 2014
    Beiträge
    17
    Hallo Fuerchau,
    Mit "Visual Explain" -> "Ausführen und mit Explain bearbeiten" haben wir das bisher gemacht.
    Anzeigen empfohlener Indexes aktiviert und einfach alle Indexe erstellt die empfohlen wurden.
    Auch wenn es in vielen Fällen hilfreich war bezweifle ich ob das wirklich die richtige vorgehensweise ist.
    Ausserdem hat es nicht immer geholfen und hat auch dazu geführt das wir jede Menge Indexe haben wo keiner genau weiß ob die wirklich etwas bringen.

    lg Michael

  12. #12
    Registriert seit
    Jul 2014
    Beiträge
    17
    Vielleicht noch eine kurze Erklärung warum das derzeit für mich wichtig ist.
    Wir arbeiten gerade an einem ERP Upgrade. Dadurch ändern sich die verwendeten Biliotheken (Schemas) und in vielen Fällen auch die Tabellen bzw. deren Felder.
    Das führt dazu das wir so ziemlich alle WebQuery Reports, bzw. die SQL-Views die dafür verwendet werden, überarbeiten bzw. anpassen müssen. (und das sind wirklich viele)
    Wenn wir schon fast alles "neu" machen dann möchte ich es soweit möglich auch verbessern.
    Es gibt schon jetzt SQL-Views die für WebQuery eine zu lange laufzeit haben (führt im WebQuery zu timeouts), daher werden die Ergebnisse dieser Views in Nacht-Jobs mittels Prozeduren in Tabellen gespeichert und diese dann für die Auswertungen verwendet. Aber das lässt sich wegen untershiedlicher Anforderungen nicht immer machen.

    lg michael

Similar Threads

  1. Optimale SQL Lösung für Zugriff PF mit 12+xx Millionen Sätze
    By Peet in forum NEWSboard Programmierung
    Antworten: 16
    Letzter Beitrag: 30-04-18, 21:18

Tags for this Thread

Berechtigungen

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