Startseite SAP ABAP Archive nach Kategorien "SQL" (Seite 4)

Kategorie: SQL

[ABAP] OpenSQL: Vergleich von SELECT-Abfragen über RANGES, FOR ALL ENTRIES, JOINS

Allgemein

In den folgenden Beispielen werden drei der gängigen Datenbankzugriffe über ein SELECT nach Laufzeit sowie Vor- und Nachteilen verglichen.

Variante 1 (RANGE)

* Laufzeit: 0,086142s

* Vorteile:
*   - Aufteilen und Manipulieren von verknüpften DB-Abfragen
*   - Abfrage in einem Block -> gut für kleine Abfragen mit wenigen RANGE-Einträgen
*   - Verwendung z.B. bei Nutzung von SELECT-OPTIONS
* Nachteile:
*   - Laufzeitverhalten bei großen Abfragen
*   - keine Abhängigkeiten von zwei oder mehr Feldern in einem RANGE abbildbar (Verknüpfung in der WHERE-Clause erfolgt nur einzeln über AND/OR)
*   - RANGES werden beim SELECT in ein Statement mit vielen ORs umgewandelt
*     -> abhängig vom DB-System kommt es früher oder später zu DUMPS, wenn der RANGE zu viele Einträge beinhaltet (Speichergröße der RANGE-Table > 64kB)
DATA: lv_maktx TYPE maktx VALUE '<empty>'.

DATA(o_timer) = cl_abap_runtime=>create_hr_timer( ).
DATA(usec_start) = o_timer->get_runtime( ).

* SELECT 1: Materialnummern
SELECT m~matnr, @lv_maktx AS maktx
  INTO TABLE @DATA(it_mara)
  FROM mara AS m
  UP TO 1000 ROWS.

DATA: rg_matnr TYPE RANGE OF matnr.

* MATNR in RANGE kopieren, darauf achten, dass die Suchfelder UNIQUE sind
rg_matnr = VALUE #( FOR <m> IN it_mara
                    (
                      sign   = 'I'
                      option = 'EQ'
                      low    = <m>-matnr
                      high   = ''
                    ) ).

* SELECT 2: Kurztexte
SELECT t~matnr, t~maktx
  INTO TABLE @DATA(it_makt)
  FROM makt AS t
  WHERE t~matnr IN @rg_matnr
    AND t~spras = @sy-langu.

* Zuweisung MATNR, MAKTX
LOOP AT it_mara ASSIGNING FIELD-SYMBOL(<t>).
  IF line_exists( it_makt[ matnr = <t>-matnr ] ).
    <t>-maktx = it_makt[ matnr = <t>-matnr ]-maktx.
  ENDIF.
ENDLOOP.

DATA(usec_end) = o_timer->get_runtime( ).
DATA(usec) = CONV decfloat16( usec_end - usec_start ).
DATA(sec) = usec / 1000000.

WRITE: / 'Laufzeit: ', sec, 's'.

cl_demo_output=>display( it_mara  ).

Variante 2 (FOR ALL ENTRIES)

* Laufzeit: 0,073252s

* Vorteile:
*   - Aufteilen und Manipulieren von verknüpften DB-Abfragen
*   - mehrere FOR ALL ENTRIES können pro Statement verwendet werden
*   - Verwendung z.B. bei Verarbeitung von vielen Datensätzen
* Nachteile:
*   - Laufzeitverhalten bei großen Abfragen
*   - wenn die FOR ALL ENTRIES Liste leer ist, wir die WHERE-Clause ignoriert, was zu unerwünschten Nebeneffekten führen kann
*   - FOR ALL ENTRIES werden beim SELECT in viele einzelne Statements umgewandelt
*   - man sollte darauf achten, dass die Einträge in der FOR ALL ENTRIES-Tabelle nur einmal auftreten -> Performance
*   - Speicherverbrauch durch die vielen Abfragen
* Anmerkungen:
*   - doppelte Einträge in der FOR ALL ENTRIES Tabelle werden entfernt (SELECT DISTINCT)
*   - wichtige Parameter für die Performance von FOR ALL ENTRIES sind:
*     rsdb/max_blocking_factor
*     rsdb/min_blocking_factor
*     rsdb/max_in_blocking_factor
*     rsdb/min_in_blocking_factor
*     rsdb/prefer_in_itab_opt
*     rsdb/prefer_fix_blocking
DATA: lv_maktx TYPE maktx VALUE '<empty>'.

DATA(o_timer) = cl_abap_runtime=>create_hr_timer( ).
DATA(usec_start) = o_timer->get_runtime( ).

* SELECT 1: Materialnummern
* darauf achten, dass die Einträge in der FOR ALL ENTRIES Tabelle UNIQUE sind
SELECT m~matnr, @lv_maktx AS maktx
  INTO TABLE @DATA(it_mara)
  FROM mara AS m
  UP TO 1000 ROWS.

* Prüfung auf IS INITIAL ist wichtig, da sonst die WHERE-Clause im SELECT ignoriert würde
IF NOT it_mara IS INITIAL.

* SELECT 2: Kurztexte
  SELECT t~matnr, t~maktx
    INTO TABLE @DATA(it_makt)
    FROM makt AS t
    FOR ALL ENTRIES IN @it_mara
    WHERE t~matnr = @it_mara-matnr
      AND t~spras = @sy-langu.

* Zuweisung MATNR, MAKTX
  LOOP AT it_mara ASSIGNING FIELD-SYMBOL(<m>).
    IF line_exists( it_makt[ matnr = <m>-matnr ] ).
      <m>-maktx = it_makt[ matnr = <m>-matnr ]-maktx.
    ENDIF.
  ENDLOOP.
ENDIF.

DATA(usec_end) = o_timer->get_runtime( ).
DATA(usec) = CONV decfloat16( usec_end - usec_start ).
DATA(sec) = usec / 1000000.

WRITE: / 'Laufzeit: ', sec, 's'.

cl_demo_output=>display( it_mara  ).

Variante 3 (JOIN)

* Laufzeit: 0,012196s

* Vorteile:
*   - wesentlich schneller als RANGE und FOR ALL ENTRIES
*   - eine komplexe DB-Anfrage (generisch)
*   - Verwendung bei Verarbeitung von vielen Datensätzen verteilt über viele Tabellen
* Nachteile:
*   - komplexe Statements
*   - schlecht zu Debuggen
DATA(o_timer) = cl_abap_runtime=>create_hr_timer( ).
DATA(usec_start) = o_timer->get_runtime( ).

* SELECT: Materialnummern, Kurztexte
SELECT m~matnr, t~maktx
  INTO TABLE @DATA(it_mara)
  FROM mara AS m
  RIGHT OUTER JOIN makt AS t ON ( m~matnr = t~matnr )
  UP TO 1000 ROWS
  WHERE t~spras = @sy-langu.

DATA(usec_end) = o_timer->get_runtime( ).
DATA(usec) = CONV decfloat16( usec_end - usec_start ).
DATA(sec) = usec / 1000000.

WRITE: / 'Laufzeit: ', sec, 's'.

cl_demo_output=>display( it_mara  ).

Links

[ABAP] OpenSQL: Subselect / Subqueries verwenden 

* Subqueries können nicht für Pool- oder Clustertabellen verwendet werden
* ORDER BY kann nicht in einer Subquery verwendet werden
* bei der Verwendung von Subqueries wird das SAP buffering umgangen

* Beispiel 1 (Städte)
SELECT city,
       latitude,
       longitude
INTO TABLE @DATA(it_cities)
FROM sgeocity
WHERE city IN ( SELECT cityfrom FROM spfli WHERE carrid = 'LH' ).

cl_demo_output=>display( it_cities ).

* Beispiel 2 (Buchungen)
SELECT c~id,
       c~name,
       c~city,
       b~cancelled
  INTO TABLE @DATA(it_cust)
  UP TO 100 ROWS
  FROM sbook as b
  INNER JOIN scustom as c ON c~id = b~customid
  WHERE customid NOT IN ( SELECT customid FROM sbook WHERE cancelled EQ @abap_true ).

cl_demo_output=>display( it_cust ).

[ABAP] ABAP Database Connectivity (ADBC) – CASE-insensitive Suche auf der Datenbank 

* ab Version 7.51 auch im OpenSQL verfügbar (LOWER, UPPER)
* https://help.sap.com/doc/abapdocu_751_index_htm/7.51/de-DE/abenopen_sql_functions.htm
* https://archive.sap.com/discussions/thread/3470652

DATA: o_salv TYPE REF TO cl_salv_table.        " Anzeigeobjekt SALV Grid
DATA: it_cols TYPE adbc_column_tab.            " Selektierte Spalten
DATA: it_makt TYPE STANDARD TABLE OF makt.     " Ausgabetabelle

START-OF-SELECTION.

* zu verarbeitende Spalten
  it_cols = VALUE #( ( 'MATNR' )
                     ( 'SPRAS' )
                     ( 'MAKTX' )
                     ( 'MAKTG' ) ).

* Spaltennnamen durch Komma trennen
  DATA(cols) = to_lower( concat_lines_of( table = it_cols sep = ',' ) ).

* Spaltennamen und Parameter in SQL einfügen, alle Bezeichner suchen, die mit 'sch*' beginnen
  DATA(lv_query) = |SELECT { cols } FROM makt WHERE spras = 'D' AND upper(maktx) LIKE 'SCH%'|.

  TRY.
* SQL-Connection öffnen
      DATA(o_sql_connection) = NEW cl_sql_connection( ).

      IF abap_true <> o_sql_connection->is_closed( ).
* SQL-Statement erzeugen
        DATA(o_sql) = NEW cl_sql_statement( con_ref = o_sql_connection ).

* Query ausführen
        DATA(o_result) = o_sql->execute_query( lv_query ).

* Ergebnismenge soll in interne Tabelle it_makt und nur die Spalten, welche in it_cols stehen
        o_result->set_param_table( itab_ref             = REF #( it_makt )
                                   corresponding_fields = it_cols ).

* Ergebnismenge in interne Tabelle lesen
* cnt enthält die Anzahl der gelesenen Datensätze
        DATA(cnt) = o_result->next_package( ).
* Ergebnismenge schließen
        o_result->close( ).
* SQL-Connection schließen
        o_sql_connection->close( ).

        IF cnt > 0.
          cl_salv_table=>factory( IMPORTING
                                    r_salv_table = o_salv
                                  CHANGING
                                    t_table = it_makt ).

          o_salv->get_display_settings( )->set_list_header( |DBMS: { o_sql_connection->get_dbms( ) }| ).
          o_salv->get_functions( )->set_all( abap_true ).
          o_salv->get_display_settings( )->set_striped_pattern( abap_true ).
          o_salv->display( ).
        ENDIF.
      ENDIF.
    CATCH cx_root INTO DATA(e_text).
      WRITE: / e_text->get_text( ).
  ENDTRY.

[ABAP] OpenSQL: Abhängigkeit (CASE)

Variante 1

SELECT matnr,
       CASE meins
          WHEN 'ST' THEN 'Stück'
          WHEN 'KG' THEN 'Kg'
          ELSE 'andere Einheit'
       END AS meins_desc,
       maktx
  INTO TABLE @DATA(it_mara)
  FROM marav.

cl_demo_output=>display( it_mara ).

Variante 2

SELECT matnr,
       CASE
          WHEN meins = 'ST' THEN 'Stück'
          WHEN meins = 'KG' THEN 'Kg'
          ELSE 'andere Einheit'
       END AS meins_desc,
       maktx
  INTO TABLE @DATA(it_mara)
  FROM marav.

cl_demo_output=>display( it_mara ).

[ABAP] OpenSQL: Summenbildung (SUM) mit Abhängigkeit (CASE)

Variante 1

* Summenbildung, abhängig von Spalte Soll/Haben
DATA: lv_bsid_sum type bsid-dmbtr.

SELECT SUM( CASE shkzg
* Haben
                WHEN 'H' THEN dmbtr
* Soll, negieren für Subtraktion
                WHEN 'S' THEN dmbtr * -1
            END )
  INTO @lv_bsid_sum
  FROM bsid
  WHERE bukrs = '0040'.

IF sy-subrc = 0.
* Summierten Wert ausgeben
  WRITE: / lv_bsid_sum.
ENDIF.

Variante 2

* Summenbildung, abhängig von Spalte Soll/Haben
DATA: lv_bsid_sum type bsid-dmbtr.

SELECT SUM( CASE
* Haben
                WHEN shkzg = 'H' THEN dmbtr
* Soll, negieren für Subtraktion
                WHEN shkzg = 'S' THEN dmbtr * -1
            END )
  INTO @lv_bsid_sum
  FROM bsid
  WHERE bukrs = '0040'.

IF sy-subrc = 0.
* Summierten Wert ausgeben
  WRITE: / lv_bsid_sum.
ENDIF.

[ABAP] Erzeugte Spool-Aufträge eines Jobs anzeigen 

DATA: it_tbtcp TYPE STANDARD TABLE OF tbtcp WITH DEFAULT KEY.

* TBTCP - Step-Uebersicht eines Batch-Jobs
SELECT * FROM tbtcp INTO TABLE @it_tbtcp
  WHERE jobname = '<JOBNAME>'.

LOOP AT it_tbtcp ASSIGNING FIELD-SYMBOL(<s>).
  WRITE: / '                              Name eines Hintergrundjobs:', <s>-jobname.   " Jobname
  WRITE: / '                                    Kennummer eines Jobs:', <s>-jobcount.
  WRITE: / '                               Kennummer eines Job-Steps:', <s>-stepcount.
  WRITE: / 'Name eines Programms innerhalb eines Steps (z.B. Report):', <s>-progname.  " Programm
  WRITE: / '                   Datum einer Job- bzw. Step-Einplanung:', <s>-sdldate.
  WRITE: / '                 Uhrzeit einer Job- bzw. Step-Einplanung:', <s>-sdltime.
  WRITE: / '               Initiator einer Job- bzw. Step-Einplanung:', <s>-sdluname.  " Batch-User
  WRITE: / '               Name einer Variante innerhalb eines Steps:', <s>-variant.
  WRITE: / '    Kennung einer Ausgabeliste eines Batch-Jobs im Spool:', <s>-listident. " Spool-Auftrags-Nummer
  WRITE: / 'Status eines Steps innerhalb der Hintergrundverarbeitung:', <s>-status.
  WRITE: / '                      Exitkode eines externen Programmes:', <s>-exitcode.
  WRITE: / '                                      Spool-Ausgabegerät:', <s>-pdest.     " Drucker
  WRITE: / '                                 Spool-Beschreibungstext:', <s>-prtxt.
  WRITE: / '                         DRUCKEN: Typ des Spool-Auftrags:', <s>-ptype.
  WRITE: / '                                      Spool-Aufbereitung:', <s>-paart.

  ULINE.

ENDLOOP.

[ABAP] Rückgabedaten eines SELECTs in einzelne Variablen übergeben 

DATA: lv_name_first TYPE ad_namefir.
DATA: lv_name_last TYPE ad_namelas.
DATA: lv_smtp_addr TYPE ad_smtpadr.

SELECT SINGLE p~name_first, p~name_last, a~smtp_addr
  FROM usr21 AS u
  INNER JOIN adrp AS p ON p~persnumber = u~persnumber
  INNER JOIN adr6 AS a ON a~addrnumber = u~addrnumber AND a~persnumber = u~persnumber
  INTO (@lv_name_first, @lv_name_last, @lv_smtp_addr)
  WHERE u~bname = @sy-uname.

IF sy-subrc = 0.
  ...
ENDIF.

[ABAP] Open SQL: WHERE-Bedingung mit Hilfe einer internen Tabelle (RANGE, STANDARD TABLE)

Variante 1 (RANGE)

* Range für MATNR definieren
TYPES: ty_rg_matnr TYPE RANGE OF matnr.

* MATNR für Suche
DATA(lv_matnr) = |12345|.

* RANGE für die Suche definieren
DATA(it_matnr) = VALUE ty_rg_matnr( ( sign   = 'I'
                                      option = 'EQ'
                                      low    = |{ lv_matnr WIDTH = 18 ALPHA = IN }|
                                      high   = '' ) ).

DATA: it_mara TYPE STANDARD TABLE OF mara WITH DEFAULT KEY.

SELECT * FROM mara INTO TABLE @it_mara WHERE matnr IN @it_matnr.

IF sy-subrc = 0.
  LOOP AT it_mara ASSIGNING FIELD-SYMBOL(<mat>).
    WRITE: / <mat>-matnr.
  ENDLOOP.
ELSE.
  WRITE: / 'Keine Daten vorhanden.'.
ENDIF.

Variante 2 (STANDARD TABLE)

* Zeilentyp für RANGE (STANDARD TABLE) für MATNR
TYPES: BEGIN OF ty_range,
         sign   TYPE ddsign,
         option TYPE ddoption,
         low    TYPE char18, " char18 für 18-stellige MATNR
         high   TYPE char18, " char18 für 18-stellige MATNR
       END OF ty_range.

* Tabellentyp für RANGE (STANDARD TABLE) für MATNR
TYPES: ty_rg_matnr TYPE STANDARD TABLE OF ty_range WITH DEFAULT KEY.

* MATNR für Suche
DATA(lv_matnr) = |12345|.

* RANGE für die Suche definieren
DATA(it_matnr) = VALUE ty_rg_matnr( ( sign   = 'I'
                                      option = 'EQ'
                                      low    = |{ lv_matnr WIDTH = 18 ALPHA = IN }|
                                      high   = '' ) ).

DATA: it_mara TYPE STANDARD TABLE OF mara WITH DEFAULT KEY.

SELECT * FROM mara INTO TABLE @it_mara WHERE matnr IN @it_matnr.

IF sy-subrc = 0.
  LOOP AT it_mara ASSIGNING FIELD-SYMBOL(<mat>).
    WRITE: / <mat>-matnr.
  ENDLOOP.
ELSE.
  WRITE: / 'Keine Daten vorhanden.'.
ENDIF.