Schräges Phänomen bei verschiedenen SELECT-Versionen

Schräges Phänomen bei verschiedenen SELECT-Versionen

Hallo zusammen,

ich wollte mal untersuchen, was es performancemäßig bringt, Datenbanktabelleninhalte im Hauptspeicher zu puffern, um anschließend daraus statt von der Datenbank zu lesen und damit die Zahl der SELECTs runterzubekommen. Dabei sehe ich auch wieder zwei mögliche Ansätze:

o Komplettpufferung: Man liest sich alle entsprechenden Werte mit einem großen SELECT aus der Datenbank und speichert sie in einer Puffertabelle.

o Teilpufferung: Man liest sich mit einem FOR ALL ENTRIES IN nur die Werte aus der Datenbank, die man später tatsächlich wird lesen wollen. Dazu braucht es auch nur eines SELECTs, der aber (wegen des FOR ALL ENTRIES) von ABAP in mehrere Teil-SELECTs umgesetzt wird. Wie und wie viele hängt von bestimmten Systemparametern ab, wie ich kürzlich in einem anderen Thread hier im Forum erfahren habe. Auf unserem System ist das so eingestellt, dass FOR ALL ENTRIES in eine IN-Klausel mit maximal 128 Einzelwerten pro SELECT umsetzt.

o gar keine Pufferung: Man liest die Werte einzeln aus der Datenbank

Zum Testen habe ich gesagt, dass die ich O-Werte, für die ich später den S-Wert wissen will, in der internen Tabelle O festhalte und später über die drei obenstehenden Ansätze die Daten von der Datenbank lese. Mich interessieren dabei nur die Datenbankzugriffe, daher fehlt im Code der anschließende LOOP WHERE über die sortierten Puffertabellen, mit dem ich mir meine Werte dann tatsächlich beschaffe. Es geht also nur darum, wie schnell bei Ansatz 1 und 2 die entsprechende Puffertabelle gefüllt wird und wie lange es im Vergleich dazu dauern würde, ohne Puffertabelle die Sätze einzeln von der Datenbank zu lesen.

Um eine repräsentative Menge O der Quellwerte zu haben, die umfangreich, aber doch deutlich kleiner als die auf der Gesamtdatenbank vorhandene Anzahl Einträge ist, fülle ich am Anfang O mit jedem dritten Eintrag aus der Datenbank. Das ist die Menge, für die ich später jeweils das zugehörige S ermitteln möchte.

Dabei ist folgendes Programm entstanden:
Über die Ausgabe war ich allerdings sehr erstaunt:

686 Zeilen in der Tabelle O
Teilpufferung: Dauer 33.456 Zeilen: 9.971
Komplettpufferung: Dauer 100.605 Zeilen: 31.021
Gar keine Pufferung: Dauer 182.469 Zeilen: 10.129

Dass ich bei der Komplettpufferung mehr Zeilen bekomme, ist klar. Aber wieso habe ich bei der Teilpufferung weniger Zeilen, als bei der Variante mit gar keiner Pufferung?!? Die WHERE-Statements sind doch letztlich komplett identisch, nur dass ich einmal einen LOOP AT O mit SELECT für jede Zeile mache und einmal einen SELECT FOR ALL ENTRIES IN O. Damit müsste ich doch genau dieselben Zeilen bekommen?!?

Wahrscheinlich könnte ich es herausfinden, indem ich einen sorgsamen Vergleich der Ergebniswerte mache und mir anschaue, welche bei gar keiner Pufferung zusätzlich anfallen. Aber ich dachte mir, das ist doch mal eine hervorragende Knobelaufgabe für das Forum hier - zumal ich gestehen muss, dass ich im Moment selber nicht die Antwort weiß.

Re: Schräges Phänomen bei verschiedenen SELECT-Versionen

Ich denke, Du hast recht, vielen Dank. Die Tabelle PUFFER ist ja komplett sortiert, und wenn ich nach der Füllung ohne Pufferung einen DELETE ADJACENT DUPLICATES FROM PUFFER drüberlaufen lasse, dann ist die Anzahl Zeilen hinterher identisch.

Die inhaltlichen Ergebnisse fand ich auch interessant. Ich hätte vermutet, dass die Komplettpufferung schneller ist, denn da werden zwar mehr Daten übertragen, dies jedoch in einem Rutsch mit nur einer einzigen Suche, wohingegen bei der Teilpufferung der IN-Operator jede einzelne Zeile über den Tabellenindex sucht, datenbankseitig also Hunderte von Indexsuchen ausführt. Zumindest bei unserer Sybase-Datenbank scheint dem jedoch nicht so zu sein. Obwohl ich ein ganzes Drittel der Datenbankzeilen auf diese Weise suche und lese, ist der Zugriff dennoch erheblich effizienter als die Komplettpufferung. Muss ich mir für zukünftige Programmierung merken - kann aber natürlich bei anderen Datenbanken völlig anders aussehen.

Re: Schräges Phänomen bei verschiedenen SELECT-Versionen

Das ist sicherlich richtig, aber ich finde es ein Ding, dass die Lesezeit bei einem Dritten der Datenbankeinträge auch kaum mehr als ein Drittel des Komplettselects ausmacht, mit anderen Worten, die Suchzeit pro Zeile ist annähernd Null. Dementsprechend lohnt es sich sogar wenn man 70% der Tabellenzeilen tatsächlich braucht, diese gezielt über FOR ALL ENTRIES auszuwählen, statt alles in die Puffertabelle einzulesen und dann darin zu suchen.

Re: Schräges Phänomen bei verschiedenen SELECT-Versionen

Dein Benchmark ist so nicht aussagekräftig, weil nach dem ersten Select die Datenbank die Daten im Cache hat und das reduziert die Zugriffszeit für nachfolgende vergleichbarer Selects ganz enorm.

Doch, das habe ich durchaus im Auge gehabt. Deshalb habe ich das Programm mehrfach hintereinander ausgeführt, um mir anzuschauen, wie sich die Ergebnisse dann verhalten, und auch die Reihenfolge der drei Varianten vertauscht (damit nicht die, die später drankommen, von der Datenbankpufferung der vorhergehenden profitieren und sich dadurch das Ergebnis verfälscht). Von daher kann ich sagen, dass Deine Einwände nicht gültig sind, jedenfalls nicht für die Sybase-Datenbank. Wie es mit Oracle aussieht, vermag ich natürlich nicht zu sagen.

Es steht Dir aber frei, das Programm auch auf Deinem System anzutesten. Wenn es kein HCM-System ist, dann wirst Du natürlich die Tabelle HRP1001 durch eine auf Deinem System mit ausreichend vielen Einträgen ausgestattete Tabelle ersetzen müssen (MARA oder welche auch immer).

Ein paar „Select SINGLES“ spielen keine Rolle. Aber so ab ca. 1.000 kann sich eine Pufferung in internen Tabellen lohnen.

Wie mein Testergebnis zeigt, lohnt sich die Pufferung schon bei weit geringeren Zugriffszahlen. Jedenfalls relativ zum SELECT SINGLE. Du kannst natürlich argumentieren, dass absolut betrachtet die Zugriffszeit von SELECT SINGLE dann auch noch so gering ist, dass die Programmierung der Pufferung den Aufwand nicht lohnt. Das ist Geschmacksache - ich bin Perfektionist und habe noch die alte Faustregel im Kopf, dass ein Anwender ein System ab einer Sekunde Antwortzeit als unangenehm träge empfindet. Ich wünschte, die SAP wüsste das auch noch, vor allem bei dem ganzen HTTP-Geraffel (Fiori etc.). Dann würde das SAPGui vielleicht wieder in der Gunst steigen.

Auch wenn man mit einem „Select INTO TABLE“ mehr Einträge liest, als man Anzahl „Select Singles“ hat, kann man da enorme Performancegewinne erzielen. Denn ein einzelner sequentieller Select mit passendem Schlüsselzugriff, wird von der DB viel effizienter erledigt, als viele einzelen „Select Singles“.

Das war auch meine Vermutung, doch zumindest soweit es den IN-Operator betrifft, der ja letztlich auch so etwas ähnliches macht wie ein mehrfacher SELECT SINGLE, hat mein Testergebnis diese Vermutung eindrucksvoll widerlegt.

„Select .. for all entries“ ist nicht schlecht, aber in den meisten Fällen sind das auch nur „Select Singles“ und eben ein implizierte DISTINCT

Ich rechne damit, dass heutzutage die Umsetzung von "FOR ALL ENTRIES" in den IN-Operator nicht nur bei Sybase der übliche Standard ist, auch wenn Sybase mit 128 eine überdurchschnittlich hohe empfohlene Blockgröße hat.

und eben ein implizierte DISTINCT und damit auf jeden Fall am SAP-Tabellenpuffer vorbei.

Den sollte man nicht überbewerten; das bringt nämlich nur was bei Tabellen, bei denen die Pufferung überhaupt aktiv ist. Bei großen Tabellen wie der HRP1001 oder der MARA ist die SAP-Tabellenpufferung ohnehin abgeschaltet. Da cachet allenfalls noch die Datenbank, und davon haste auch bei DISTINCT was.

Im übrigen wette ich, dass eine Pufferung in eine eigene interne Tabelle, die nur aus den benötigten Feldern besteht und genau nach dem Schlüssel sortiert ist, nach dem man später zu lesen gedenkt, noch mal ein ganzes Stück schneller ist als ein SELECT-Befehl, den der ABAP-Interpreter abfangen und auf einen Tabellenpufferzugriff umsetzen muss. Wieviel das ausmacht, wäre mal ein weiteres Testprojekt.

Was ich enttäuschend finde, ist das HASHED-Tabellen in ABAP so gut wie nichts taugen. Auch das habe ich mal durchgemessen und bin zu dem Ergebnis gekommen, dass das Füllen einer HASHED-Tabelle so viel länger dauert als das Füllen einer SORTED-Tabelle, dass man anschließend irrsinnig viele Lesezugriffe bräuchte, um den Rechenzeitnachteil zu amortisieren. Dabei ist zu beachten, dass die Suche in SORTED-Tabellen nur minimal langsamer ist als in HASHED-Tabellen. HASHED-Tabellen nehmen für sich in Anspruch, eine konstante Suchzeit zu haben, wohingegen SORTED-Tabellen mit zunehmender Zeilenzahl doch eine langsam ansteigende Suchzeit haben. In meinen Tests war die Suchzeit aber auch bei HASHED-Tabellen nicht ganz konstant, sondern bei hohen Zeilenzahlen auch leicht erhöht, und überdies fällt bei sehr großen Zeilenanzahlen der Rechenzeitnachteil bei der Erstbefüllung besonders ins Gewicht, so dass man Abertausende von Lesezugriffen (ein hohes Vielfaches der Zeilenanzahl) bräuchte, um diesen Nachteil gegenüber SORTED zu amortisieren. Nachdem ich das gemessen habe, habe ich mir die Nutzung von HASHED-Tabellen komplett abgewöhnt.

Re: Schräges Phänomen bei verschiedenen SELECT-Versionen

Mit solchen Wetten wäre ich vorsichtig! Ich habe gelernt, dass vieles, von dem man denkt, es müsste unglaublich langsam sein, weil es kompliziert ist, nicht so ist. Teilweise sind so ausgetüftelte Techniken im Hintergrund, da kommt man mit "gesundem Menschenverstand" nicht gegen an.
XML-Transformationen zum Beispiel gehen so dermaßen schnell, das bekommt nicht ansatzweise so nachprogrammiert.

Ja, aber die SAP-Optimierer kochen auch nur mit Wasser. Egal wie sie puffern, sie müssen hinterher über einen passenden Schlüssel suchen, und nichts anderes mache ich auch, nur dass ich klar im Vorteil bin, a) weil ich meine Puffertabelle nur mit den tatsächlich benötigten Zeilen fülle und b) weil ich meinen Sortierschlüssel viel optimaler wählen kann als der Datenbankoptimierer. Der kann ja im Prinzip nicht viel mehr machen, als nach einem Datenbankindex sortieren (mutmaßlich dem, über den der Zugriff stattgefunden hat), wohingegen ich mir nicht nur das Feld MANDT schon mal sparen kann, sondern auch einen Index nehme, der genau zu meinem Zugriffsmuster passt. Der muss mit den vorhandenen Datenbankindizes gar nichts zu tun haben. Wenn da der SAP-Puffer schneller wäre, dann würde das im Umkehrschluss bedeuten, dass die Verwaltung der internen Tabellen durch ABAP schlampert umgesetzt ist. Und das kann ich mir nicht vorstellen, dazu sind die zu wichtig (was die SAP ja auch mit dem hohem Aufwand an verfügbaren Befehlen und Sortiermechanismen unterstreicht).

Bei deinem Beispiel könnte übrigens noch eine Rolle spielen, dass die PUFFER-Tabelle nur beim ersten SELECT allokiert werden muss. Bei den nachfolgenden SELECTS wurde der Speicherbereich bereits zur Verfügung gestellt.

Deswegen habe ich, wie gesagt, die Reihenfolge der Testzugriffe im Code probehalber vertauscht und mir angeschaut, wie sich das auf das Ergebnis auswirkt. Der Unterschied war gering und hatte auf die grundsätzliche Aussage des Ergebnisses keinen Einfluss.

Allenfalls wäre denkbar, dass beim ersten Programmlauf die Datenbankpuffer gefüllt wurden und bei weiteren Läufen nur daraus bedient wurde, so dass datenbankseitig weitere im Anschluss durchgeführte Programmstarts nichts bringen, weil es keinen mir bekannten Weg gibt, aus ABAP heraus die Datenbankcaches zu leeren. Dann müsste man sich nur am erstmaligen Testlauf orientieren, was natürlich blöd ist, denn bei einem einmaligen Lauf können immer verfälschende Seiteneffekte dominieren (gerade in der Sekunde irgendein Job auf dem Server angelaufen mit 1 Sek Laufzeit oder was auch immer). Da müsste man eigentlich eine Woche lang jeden Morgen das Programm ausführen, dann sollte der Puffer jeweils leer sein, und dann die Ergebnisse mitteln. Dabei auch Reihenfolgen vertauschen. Vielleicht mache ich mir den Spaß.

200.000 Zeilen sind auch keine Größe mehr für heutige Systeme. Für einen Performancetest würde ich deutlich größere Datenmengen verwenden.

Nein, denn ich will realitätsnah bleiben. In meinen Programmen kommen so große interne Tabellen so gut wie nicht vor; ich brauche nur ein paar tausend Zeilen. Die aber aus einer ganzen Reihe von Datenbanktabellen, so dass es sich läppert. Wenn ich da ein vernünftiges Ergebnis haben will, muss ich also testen, wie man bei dieser Größenordnung am besten optimiert, anstatt eine viel größere anzusetzen, die in meinen echten Programmen keine Rolle spielt.

Insgesamt ist das Thema DB extrem komplex und mit viel Halbwissen behaftet (insbesondere bei mir... ).

Ganz genau. Da erzählt jeder was anderes, und auch was in einer Doku oder einem Hinweis steht, muss noch lange nicht stimmen oder könnte veraltet sein oder nicht zur eigenen Datenbank oder den eigenen Systemparametern passen. Das ist wie mit Motoröl beim Auto; Ölkunde ist nicht Bestandteil der Kfz-Mechanikerausbildung (auch nicht beim Meister), und daher wissen die Werkstätten nur, was per Mundpropaganda von einem zum nächsten getragen oder von Vertretern der Motorölfirmen, deren Priorität auf ihrem Absatz liegt, bei Werbebesuchen kolportiert wird. Dementsprechend desaströs ist, was der durchschnittliche Autobesitzer einfüllt. Die Werkstätten machen es nicht viel besser und halten sich als Untergrenze nur an die vom Autohersteller vorgeschriebenen Normen (die aber in vielen Fällen keineswegs optimal für das Fahrzeug sind).

Deshalb teste ich selber ein bisschen rum; dann habe ich reale Zahlen in der Hand. Nur so konnte ich z.B. nachweisen, dass die HASHED-Tabellen (wie oben geschildert) nichts taugen. Das steht sonst nirgends geschrieben.

Re: Schräges Phänomen bei verschiedenen SELECT-Versionen

Es ist allerdings manchmal gefährlich, von bestimmten Annahmen auszugehen. Von einem Monat auf den anderen kommt ein Buchungskreis hinzu oder es wird nicht nur ein Jahr ausgewertet, sondern zwei etc.
Insofern ist es aber auch wieder sinnvoll, sich auf die bekannte realistische Größe zu konzentrieren, denn es ist kaum sinnvoll, ein Programm tagelang für eine vielleicht in der Zukunft auftretende Datenkonstellation zu optimieren.

Überdies habe ich auch nicht den Anspruch genau vorherzuwissen, wieviel Zeilen ich in Zukunft brauchen werde. Das wird sich auch von Programm zu Programm und von Tabelle zu Tabelle unterscheiden. Mir reicht es, wenn die Größenordnung stimmt. Wenn ich statt einem Jahr zwei auswerte, dann lese ich bei einer Tabelle vielleicht 12.000 statt 6.000 Zeilen. Damit bin ich aber immer noch nicht im Bereich von 200.000 Zeilen; das ist eine ganz andere Kategorie.

Dementsprechend habe ich auch nicht den Anspruch, mit meinen Tests ganz genaue Aussagen machen zu können. Ich wollte einfahc mal einen Eindruck gewinnen, welche Aspekte welchen Stellenwert haben. Ich stelle fest: Alles einzulesen dauert (trotz der vielen durch den IN-Operator bedingten impliziten Einzel-SELECTS) ein Stück länger als Teilpufferung, und Lesen ohne Puffer dauert ganz grob gesagt dreimal so lange. Damit weiß ich, dass ich nicht falsch liege, wen ich bei meinen zukünftigen Programmen auf Teilpufferung setze. Vielleicht sind mal ein paar Parameter anders, so dass der Vorteil mal geringer oder gar negiert ist, aber im Großen und Ganzen verfolge ich damit einen sinnvollen Ansatz.

Auf jeden Fall ist so eine selbst gewonnene Erkenntnis besser, als unkritisch irgend etwas nachzuplappern, was man mal von irgendwem gehört oder gelesen hat.

In jedem Fall merkt man, sobald man sich mit dem Thema Performance beschäftigt, wie viele Rädchen da ineinander greifen und sich gegenseitig bedingen.

Das ist so.