View Full Version : 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
KingofKning
15-07-20, 14:56
Na ja,
wenn Du hiermit suchst findest Du schon was.
Index-Advisor und Primary Key
GG 3972
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
manuel.marcos
15-07-20, 15:40
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
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".
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 (https://www.ibm.com/support/pages/sites/default/files/inline-files/$FILE/indexing%20and%20statistics%202011.pdf) 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
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.
@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.
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.
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