Warum sind skalare UDFs so langsam?

Warum sind skalare UDFs so langsam?

Beim Laden von Daten aus Quellsystemen gibt es im SAP BW Anforderungen, die sich auf Feldebene häufig wiederholen. Dazu gehören vor allem Logiken zum Bereinigen und Aufbereiten der Daten, wie zum Beispiel:

  • Hinzufügen und entfernen von führende Nullen
  • Fallunterscheidungen
  • Entfernen von Leerzeichen am Anfang oder Ende
  • Konvertierung in Großbuchstaben
  • Ableiten eines Feldes aus einem anderen mit Substrings nach festem Muster, z.B. COAREA ==> COUNTRY
  • Ableiten von Zeitmerkmalen

Das folgende Listing zeigt ein paar Beispiele im Quellcode:

outTab = SELECT TO_VARCHAR(TO_DATE(calday), 'YYYYMM') AS calmonth,
				"/BIC/SRCSYS",
				...
				"/BIC/CURTYP",
				LEFT("/BIC/COAREA", 2) AS "/BIC/COUNTRY",
				SUBSTRING("/BIC/BI_PROFCT", 9, 2) as "/BIC/PCACCID",
				CASE SUBSTRING("/BIC/PROFCT", 8, 3)
				  when '643' then '1' 
				  when '655' then '1'
				  when '641' then '2' 
				  when '651' then '2'
				  when '643' then '3' 
				  when '655' then '3'
				  else ''
				end as  "/BIC/PRBY',
				...
		   FROM :intab;

Typische Ausdrücke in der Feldliste einer Transformationsroutine

Allen diesen Anforderung ist gemein, dass sie

  • sich in SQLScript mit den vorhandenen SQL-Funktionen einfach implementieren lassen
  • sich oft wiederholen
  • von der SAP HANA sehr schnell ausgeführt werden können, solange sie direkt in der Transformationsroutinen implementiert sind.

DRY – Don’t Repeat Yourself

Das bekannte DRY Prinzip sollte hier eigentlich zum Tragen kommen. Also das man den Quelltext nicht ständig wiederholt. In der Feldliste bieten sich hierfür die skalaren, Benutzerdefinierten Funktionen (UDF) an. Diese waren bis vor Kurzem kein Thema für die Transformationsroutinen im BW, weil diese erst seit AS ABAP Release 753 mit dem AMDP Framework erstellt werden können [1]SAP Dokumentation zu AMDP Funktionen. Aber genau für die genannten Anforderungen wären skalare UDFs eigentlich ideal. Damit kann man eine einheitliche Implementierung gewährleisten. Und man kommt im Quelltext von einer technischen Beschreibung der Ausdrücke zu einer fachlichen Sicht:

outTab = SELECT "ZCL_CALDAY=>TO_CALMONTH"(calday) AS calday
				"/BIC/SRCSYS",
				...
				"/BIC/CURTYP",
				"ZCL_COAREA=>TO_COUNTRY"("/BIC/COAREA") AS "/BIC/COUNTRY",
				"ZCL_PROFCT=>TO_PCACCID"(/BIC/PROFCT") as "/BIC/PCACCID",
				"ZCL_PROFCT=>TO_PRBY"(/BIC/PROFCT") as "/BIC/PRBY',
				...
		   FROM :intab;

Auslagern der Ausdrücke in eine skalare UDF

Die ausgelagerten Funktionen sehen sehr viel eleganter aus. Und auch unter dem Aspekt der Wartung sind sie haushoch überlegen. Wenn beispielsweise ein weiteres Profitcenter in der Liste auftaucht, dann gibt es genau eine Funktion, die man anpassen muss. Am Beispiel von ZCL_PROFCT=>TO_PRBY zeige ich eine solche skalare AMDP Funktion:

CLASS zcl_profct DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    METHODS to_prby IMPORTING VALUE(iv_profct) TYPE char10
                    RETURNING VALUE(rv_prby)   TYPE char1.
ENDCLASS.

CLASS zcl_profct IMPLEMENTATION.
  METHOD to_prby BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS DETERMINISTIC READ-ONLY.
    rv_prby = case SUBSTRING(:iv_profct, 8, 3)
                  WHEN '643' then '1'
                  WHEN '655' then '1'
                  WHEN '641' then '2'
                  WHEN '651' then '2'
                  WHEN '643' then '3'
                  WHEN '655' then '3'
                  ELSE ''
                END ;
  ENDMETHOD.

ENDCLASS.

An dem Beispiel sieht man gut, dass die Funktion nur eine einfache „Hülle“ für einen CASE-Ausdruck ist. Die Auslagerung in die ABAP Welt ermöglicht übrigens auch, ganz einfach UnitTests für die Funktionen zu schreiben. Da die Funktionen in der Datenbank erst beim ersten Aufruf aus dem ABAP generiert werden, bieten sich UnitTests auch dafür an. [2]Ich verwende bewusst keine von BW-InfoObjects generierten Datenelemente, damit die Abhängigkeit so gering wie möglich ist.

Elegant, aber langsam

So elegant die Auslagerung von Logik in UDFs auch ist, für große Datenmengen ist das Konzept leider nicht zu gebrauchen. Denn die UDFs beeinflussen die Laufzeit erheblich. Das möchte ich an einem anderen Ausdruck aus dem obigen Beispiel zeigen:

TO_VARCHAR(TO_DATE(calday), 'YYYYMM')

Dieser einfache Ausdruck wandelt ein ABAP DATS Wert in ein SQLScript Datum, dass dann wiederum in eine Zeichenkette im Format YYYYMM ausgegeben wird. Dieses entspricht dem Format des beliebten InfoObject CALMONTH.

Beispiel: 20200928 ==> 202009

Dafür legen ich nach dem obigen Muster eine AMDP UDF an:

  METHOD to_calmonth BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS DETERMINISTIC READ-ONLY.
    rv_result = TO_VARCHAR(TO_DATE(:iv_calday), 'YYYYMM');
  ENDMETHOD.

Ich habe zwei Abfragen in der SQL-Konsole auf eine kleine BW-Tabelle mit ca. 3 Mio Einträgen gemacht: Einmal direkt mit dem Ausdruck und einmal mit dem Funktionsaufruf.

Ohne UDF

do begin
	select budat, 
	       TO_VARCHAR(TO_DATE(budat), 'YYYYMM'),
	       account, 
	       amount, 
	       curr
	       from "/BIC/AZBR_E1_S2";     
end;

Konsole

Statement 'do begin select budat, "ZCL_CALDAY=>TO_CALMONTH"(BUDAT), account, amount, curr from ...' 
successfully executed in 2:09.418 minutes  (server processing time: 4:18.632 minutes)
Fetched 1000 row(s) in 103 ms 144 µs (server processing time: 0 ms 675 µs)
Result limited to 1000 row(s) due to value configured in the Preferences

Mit UDF

do begin
	select budat, 
	       "ZCL_CALDAY=>TO_CALMONTH"(BUDAT),
	       account, 
	       amount, 
	       curr
	       from "/BIC/AZBR_E1_S2";     
end;

Konsole

Statement 'do begin select budat, TO_VARCHAR(TO_DATE(budat), 'YYYYMM'), account, amount, curr from ...' 
successfully executed in 501 ms 237 µs  (server processing time: 801 ms 664 µs)
Fetched 1000 row(s) in 102 ms 468 µs (server processing time: 0 ms 677 µs)
Result limited to 1000 row(s) due to value configured in the Preferences

Die Laufzeiten sind dermaßen unterschiedlich, dass man eigentlich von einem Bug ausgehen muss. Denn die Komplexität bei dem Besipiel ist minimal. Mit weniger Komplexität brauche ich auch keine UDF mehr. Was ich erstaunlich finde:

  • Tatsächlich stehen in der Tabelle nur 365 unterschiedliche Werte von BUDAT drin. Damit hat das System die Information: Bei gleicher Eingabe sollte auch das gleiche Ergebnis geliefert werden. Siehe [3]SAP Dokumentation zur CREATE FUNCTION Anweisung . Damit sollte sie maximal 365 Mal ausgeführt werden und danach aus dem Puffer gelesen werden, oder?
  • Ich hätte auch erwartet, dass eigentlich nur das Wörterbuch der Spalte konvertiert wird, also jeder Wert genau ein Mal.
  • Wenn man mit einem primitiven Texteditor mit einfachem Suchen / Ersetzen den Funktionsaufruf durch den Funktionsinhalt ausgetauscht hätte, ist man über 100 x schneller. Das sollte der Optimizer einer SAP HANA 7[4]Scalar User Defined Functions in SAP HANA – Der erste Blog Beitrag zu dem Thema von Rich Heilmann von 2013 Jahre nach der Einführung skalarer UDFs doch hinbekommen, oder?

Um das ganze vollständig zu dokumentieren, habe ich auch noch die Analyse mit PlanViz gemacht.

PlanViz der Abfrage mit Ausdruck
PlanViz der Aubfrage mit UDF

Es ist gut sichtbar, dass im Großen und Ganzen der gleiche Ausführungsplan gewählt wird. Aber die Laufzeit des einen, rot markierten JECalculate Knoten enthält die gesamte Laufzeit für die UDF Aufruf. Das müsste sich doch zumindest parallelisieren lassen.

Warum ist das so? Habe ich einen Denkfehler in meinem Beispiel? Kann ich das noch optimieren? Oder sind die UDFs einfach so langsam. Bei der Recherche in Foren habe ich gesehen, dass ich mit meinen Problemen nicht alleine bin. Es gibt auch mehrere Möglichkeiten, mit ungünstigen Funktionen das System auszubremsen. [5]Beispiele für ungünstige UDF Funktion im Speicherproblem[6]Mehrfacher Aufruf skalarer UDFs mit mehreren Rückgabeparametern[7]Keine parallele Ausführung von mehreren UDFs

Es besteht auf jeden Fall noch großes Potenzial. Zumindest für einfach Ausdrücke ohne Abfragen auf andere DB-Tabellen.
Mir ist klar, dass es auch komplexe Anforderungen gibt, die sich nicht auf einen einzigen Ausdruck reduzieren lassen. Aber dann wäre zumindest eine parallele Verarbeitung wünschenswert.

Update vom 8.10.2020: Ich habe mich heute noch etwas mit dem Thema Performanceoptimierung beschäftigt. Dabei bin ich auch über die HINTS gestolpert. Mit dem HINT(INLINE) soll eigentlich genau das gewünschte Verhalten ausgelöst werden: Die Optimierung sollte global erfolgen. Aber leider steht in der Dokumentation nichts über Funktionen. Alles bezieht sich auf Prozeduren. Und das entspricht auch meiner Beobachtung: Der Hint bringt absolut keine eränderung . Es bleibt langsam.

do begin
    select budat, 
           "ZCL_CALDAY=>TO_CALMONTH"(BUDAT),
           account, 
           amount, 
           curr
           from "/BIC/AZBR_E1_S2"
           WITH HINT(INLINE);     
end;

Anmerkungen und Verweise   [ + ]

1. SAP Dokumentation zu AMDP Funktionen
2. Ich verwende bewusst keine von BW-InfoObjects generierten Datenelemente, damit die Abhängigkeit so gering wie möglich ist.
3. SAP Dokumentation zur CREATE FUNCTION Anweisung
4. Scalar User Defined Functions in SAP HANA – Der erste Blog Beitrag zu dem Thema von Rich Heilmann von 2013
5. Beispiele für ungünstige UDF Funktion im Speicherproblem
6. Mehrfacher Aufruf skalarer UDFs mit mehreren Rückgabeparametern
7. Keine parallele Ausführung von mehreren UDFs

Fordern Sie weitere Informationen an