ABAP - Common Table Expression (CTE)

Inhaltsverzeichnis

• WITH
• Verwendung
• Performance
• Manuelle Abfrage
• Core Data Service
• Messung
• Beispiel
• Fazit

Mit ABAP 7.52 wurde der allgemeine Tabellenausdruck eingeführt, womit lokale Views definiert werden können, über die dann ein Zugriff stattfinden kann. Dabei werden auf einer HANA Datenbank die Datenmengen performant gelesen und die Daten ausgewertet. Als Beispiel verwenden wir unser Datenmodell von den Core Data Services.

 

WITH

Wie werden solche Statements nun zusammengebaut? Eingeleitet werden die zusammengesetzten Ausdrücke mit einem WITH, gefolgt von einem Datenzugriff, der eine Ergebnismenge in Form einer Tabelle bildet. Dieser Ausdruck wird dann mit einem Alias zusammengefasst und nach einem Komma kann ein weiterer Ausdruck erfolgen, um so eine Menge an Datentöpfen zu erzeugen. Zum Abschluss des Statements kommt die Hauptabfrage, die sich dann meist auf die eingegrenzten Mengen bezieht. Schauen wir uns dazu einmal ein Beispiel an:

WITH
  +eupartners AS (
    SELECT FROM zbs_dmo_partner FIELDS partner WHERE country = 'DE'
    UNION DISTINCT
    SELECT FROM zbs_dmo_partner FIELDS partner WHERE country = 'CH'
  ),
  +partner_per_month AS (
    SELECT FROM zbs_dmo_invoice
      FIELDS DISTINCT partner, document, substring( doc_date, 1, 6 ) AS buy_month
      WHERE partner IN ( SELECT partner FROM +eupartners )
  )
  SELECT FROM +partner_per_month
    FIELDS DISTINCT buy_month, COUNT( document ) AS doc_number
    GROUP BY buy_month
    ORDER BY doc_number DESCENDING, buy_month DESCENDING
    INTO TABLE @DATA(lt_result).

 

Was passiert oben genau?

• Im ersten Schritt bilden wir mit UNION eine Menge an Partnern aus Deutschland und der Schweiz und weisen sie dem Alias "+eupartners" zu.
• Im zweiten Schritt selektieren wir alle Belege für diese ermittelten Partner und bereiten die Ausgabefelder auf, sodass neben Partner und Dokument, auch Jahr/Monat zusammenstehen, um das Belegvolumen je Monat zu ermitteln. Das Ergebnis weisen wir der Datenmenge "+partner_per_month" zu.
• Im letzten Schritt erfolgt die eigentliche Selektion des Ergebnisses, wir ermitteln die Anzahl Belege pro Monat als Ergebnis für Deutschland und die Schweiz.

 

Das Ergebnis der Abfrage sieht nun wie folgt bei uns aus, wobei je nach Datenmenge das Ganze bei dir anders aussehen kann:

 

Verwendung

In unserem Beispiel hast du einmal die verschiedenen Möglichkeiten der Verwendung gesehen, neben verschiedenen Mengenoperationen wie UNION, EXCEPT oder INTERSECT, sind auch Tabellenfunktionen wie CONCAT oder SUBSTRING möglich, um sich die Ergebnismenge zu erstellen. Solche Abfragen werden vor allem verwendet, wenn man temporäre Views in der Logik benötigt und diese nicht auf der Datenbank anlegen möchte.

Bei der Verwendung der Abfragen sind dir keine Grenzen gesetzt, du kannst die Ergebnisse der Abfragen auch selbst wieder in komplexen Statements zusammenführen und die erzeugten Datenmengen beliebig abfragen oder zusammenführen. Natürlich nur, wenn es auch für die Abfrage Sinn macht.

 

Performance

Schauen wir uns einmal die Performance des Statements an und vergleichen es mit einem manuellen Vorgang innerhalb der Klasse und einem Core Data Service. Zum Vergleich sprechen wir von einem kleinen Datenvolumen von insgesamt 122 Belegen.

 

Manuelle Abfrage

Hier einmal die manuelle Abfrage über die Klasse, dabei lesen wir alle Daten ein und arbeiten mit der Datenmenge lokal. Dabei verwenden wir für die letzte Abfrage die Möglichkeit eines SELECT auf @itab:

SELECT FROM zbs_dmo_partner
  FIELDS 'I' AS sign, 'EQ' AS option, partner AS low, partner AS high
  WHERE country IN ('CH','DE')
  INTO TABLE @DATA(lt_r_partner).

SELECT FROM zbs_dmo_invoice
  FIELDS DISTINCT partner, document, substring( doc_date, 1, 6 ) AS buy_month
  WHERE partner IN @lt_r_partner
  INTO TABLE @DATA(lt_invoice).

SELECT FROM @lt_invoice AS invoice
  FIELDS DISTINCT buy_month, COUNT( document ) AS doc_number
  GROUP BY buy_month
  ORDER BY doc_number DESCENDING, buy_month DESCENDING
  INTO TABLE @DATA(lt_result).

 

Core Data Service

Bilden wir nun die Abfragen als CDS Views im System ab, dazu müssen wir zwei Views anlegen, um die Grunddaten zusammenzufassen. Der erste CDS View bildet die Grundmenge an Partnern, ebenfalls über einen UNION:

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Partner from EU'
@Metadata.ignorePropagatedAnnotations: true
define view entity ZBS_I_DmoCTEPartnerEU
  as select from zbs_dmo_partner
{
  key partner
}
where
  country = 'DE'
union select from zbs_dmo_partner
{
  key partner
}
where
  country = 'CH'

 

Der zweite View stellt die Belege pro Monat für die vorher abgegrenzten Belege zur Verfügung:

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Sum per Month'
@Metadata.ignorePropagatedAnnotations: true
define view entity ZBS_I_DmoCTESumPerMonth
  as select from ZBS_I_DmoCTEPartnerEU as Partner
    join         zbs_dmo_invoice       as Invoice on Invoice.partner = Partner.partner
{
  key Invoice.partner,
  key Invoice.document,
      substring( Invoice.doc_date, 1, 6 ) as buy_month
}

 

Im letzten Schritt benötigen wir noch die Datenabfrage in unserer Testklasse, die auf den Core Data Service verweist:

SELECT FROM ZBS_I_DmoCTESumPerMonth
  FIELDS DISTINCT buy_month, COUNT( document ) AS doc_number
  GROUP BY buy_month
  ORDER BY doc_number DESCENDING, buy_month DESCENDING
  INTO TABLE @DATA(lt_result).

 

Messung

Die Daten für die Messung sind sehr überschaubar, weshalb wir das Ganze in einer Schleife laufen lassen und jedes Fallbeispiel 1000 Mal ausgeführt wird.

 

Wie du aus der Dokumentation entnehmen kannst, umgeht die CTE den Tabellenpuffer, deshalb haben wir sie ganz zum Schluss ausgeführt, sodass es keine Abhängigkeit zu gepufferten Abfragen gibt. Ungefähr kann man als Ergebnis festhalten, dass sich CTE nicht hinter den CDS Views verstecken muss. Dadurch, dass die Daten nicht in die Klasse kopiert werden müssen, ist es auch um Einiges schneller als die manuelle Ausführung.

Hinweis: Die Genauigkeit der Messung spielt in diesem Beispiel keine Rolle für uns, allein die groben Messgrößen sollen uns als Vergleich reichen.

 

Beispiel

Hier noch einmal das vollständige Beispiel für den Performancevergleich und die Klasse zum Ausführen:

CLASS zcl_bs_demo_cte DEFINITION PUBLIC FINAL CREATE PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

  PROTECTED SECTION.
  PRIVATE SECTION.
    CONSTANTS:
      c_times TYPE i VALUE 1000.

    METHODS:
      select_in_code
        IMPORTING
          io_out TYPE REF TO if_oo_adt_classrun_out,

      select_with_cte
        IMPORTING
          io_out TYPE REF TO if_oo_adt_classrun_out,

      select_from_cds
        IMPORTING
          io_out TYPE REF TO if_oo_adt_classrun_out.
ENDCLASS.



CLASS zcl_bs_demo_cte IMPLEMENTATION.
  METHOD if_oo_adt_classrun~main.
    DATA:
      ld_start TYPE timestampl,
      ld_end   TYPE timestampl.

    GET TIME STAMP FIELD ld_start.
    select_from_cds( out ).
    GET TIME STAMP FIELD ld_end.
    out->write( |With CDS: { ld_end - ld_start }| ).

    GET TIME STAMP FIELD ld_start.
    select_in_code( out ).
    GET TIME STAMP FIELD ld_end.
    out->write( |With Code: { ld_end - ld_start }| ).

    GET TIME STAMP FIELD ld_start.
    select_with_cte( out ).
    GET TIME STAMP FIELD ld_end.
    out->write( |With CTE: { ld_end - ld_start }| ).
  ENDMETHOD.


  METHOD select_with_cte.
    DO c_times TIMES.
      WITH
        +eupartners AS (
          SELECT FROM zbs_dmo_partner FIELDS partner WHERE country = 'DE'
          UNION DISTINCT
          SELECT FROM zbs_dmo_partner FIELDS partner WHERE country = 'CH'
        ),
        +partner_per_month AS (
          SELECT FROM zbs_dmo_invoice
            FIELDS DISTINCT partner, document, substring( doc_date, 1, 6 ) AS buy_month
            WHERE partner IN ( SELECT partner FROM +eupartners )
        )
        SELECT FROM +partner_per_month
          FIELDS DISTINCT buy_month, COUNT( document ) AS doc_number
          GROUP BY buy_month
          ORDER BY doc_number DESCENDING, buy_month DESCENDING
          INTO TABLE @DATA(lt_result).
    ENDDO.
  ENDMETHOD.



  METHOD select_in_code.
    DO c_times TIMES.
      SELECT FROM zbs_dmo_partner
        FIELDS 'I' AS sign, 'EQ' AS option, partner AS low, partner AS high
        WHERE country IN ('CH','DE')
        INTO TABLE @DATA(lt_r_partner).

      SELECT FROM zbs_dmo_invoice
        FIELDS DISTINCT partner, document, substring( doc_date, 1, 6 ) AS buy_month
        WHERE partner IN @lt_r_partner
        INTO TABLE @DATA(lt_invoice).

      SELECT FROM @lt_invoice AS invoice
        FIELDS DISTINCT buy_month, COUNT( document ) AS doc_number
        GROUP BY buy_month
        ORDER BY doc_number DESCENDING, buy_month DESCENDING
        INTO TABLE @DATA(lt_result).
    ENDDO.
  ENDMETHOD.


  METHOD select_from_cds.
    DO c_times TIMES.
      SELECT FROM ZBS_I_DmoCTESumPerMonth
        FIELDS DISTINCT buy_month, COUNT( document ) AS doc_number
        GROUP BY buy_month
        ORDER BY doc_number DESCENDING, buy_month DESCENDING
        INTO TABLE @DATA(lt_result).
    ENDDO.
  ENDMETHOD.
ENDCLASS.

 

Fazit

CTE oder auch Common Table Expression ist eine weitere Möglichkeit komplexe Operationen aus dem ABAP heraus auf der Datenbank zu machen. Die temporären Abfragen sparen die Anlage von Core Data Services oder anderen Views und zerlegen komplexe Statements im Programm für eine bessere Lesbarkeit.

 

Quelle:
SAP Dokumentation - WITH