Startseite Beiträge getaggt mit "OpenSQL" (Seite 2)

Schlagwort: OpenSQL

[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 ).

[OpenSQL] Fünf goldene SQL-Regeln

  1. Ergebnismenge reduzieren (kein SELECT…ENDSELECT, kein LOOP … SELECT … ENDLOOP, WHERE-clause genau spezifizieren)
  2. Transferdatenmenge minimieren (Aggregatfunktionen [MIN, MAX, AVG, SUM, COUNT …] verwenden, UPDATE mit SET, kein SELECT *)
  3. Anzahl Datentransfers minimieren (JOINs und SUBSELECTs verwenden, SELECT … FOR ALL ENTRIES verwenden, INSERT, UPDATE, MODIFY, DELETE immer mit Arrays)
  4. Suchaufwand reduzieren (Secondary Keys & Indexes verwenden, positive WHERE-Bedingungen verwenden)
  5. Reduktion des DB-Load (keine redundanten Zugriffe, Buffering/Caching verwenden, SORT auf dem Applikationsserver)

[ABAP] Case-insensitives SORT von internen Tabellen 

TYPES: BEGIN OF ty_name,
         name    TYPE string,
         name_lc TYPE string,
       END OF ty_name.

TYPES: ty_it_name TYPE STANDARD TABLE OF ty_name WITH DEFAULT KEY.

* Tabelle mit Strings, die sortiert werden soll
DATA(it_names) = VALUE ty_it_name( ( name = 'Albert' )
                                   ( name = 'Emma' )
                                   ( name = 'Zorro' )
                                   ( name = 'Donald' )
                                   ( name = 'Lion' )
                                   ( name = 'answer' )
                                   ( name = 'type' )
                                   ( name = 'second' ) ).

* zweite Spalte mit lower-case Strings füllen
LOOP AT it_names ASSIGNING FIELD-SYMBOL(<n>).
  <n>-name_lc = to_lower( <n>-name ).
ENDLOOP.

* erstmal regulär sortieren
SORT: it_names BY name.

* zweite Tabelle anlegen (kopieren)
DATA(it_names_lc) = it_names.

* Spalte lower-case sortieren
SORT: it_names_lc BY name_lc.

* Ausgabe der Ergebnisse
cl_demo_output=>write_data( value = it_names    name = 'case-sensitives SORT' ).
cl_demo_output=>write_data( value = it_names_lc name = 'SORT nach lower-case' ).
cl_demo_output=>display( ).

[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] Prüfen, ob Datensatz auf der Datenbank vorhanden 

DATA: lv_matnr_ok TYPE boolean VALUE abap_false.

PARAMETERS: p_matnr TYPE mara-matnr.

* wenn Datensatz vorhanden, wird abap_true in lv_matnr_ok geschrieben
SELECT SINGLE @abap_true FROM mara INTO @lv_matnr_ok
  WHERE matnr = @p_matnr.

WRITE: / COND string( WHEN lv_matnr_ok = abap_true THEN 'MATNR vorhanden.' ELSE 'MATNR nicht vorhanden.').

IF sy-subrc NE 0.
  WRITE: / 'Fehler:', sy-subrc.
ENDIF.