Skip to content

ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Native SQL →  AMDP - ABAP Managed Database Procedures →  AMDP - Examples 

AMDP, Comparison of SQLScript with Open SQL

This example demonstrates the performance of SQL in AMDP when compared with Open SQL.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA(out) = cl_demo_output=>new( ).

    IF cl_db_sys=>is_in_memory_db = abap_false.
      out->display(
        `Example can be executed on SAP HANA Database only` ).
      LEAVE PROGRAM.
    ENDIF.

    DATA time_stamps TYPE TABLE OF timestampl.
    SELECT changed_at
           FROM snwd_so_inv_head UP TO 100 ROWS
           INTO TABLE time_stamps.                     "#EC CI_NOWHERE
    IF time_stamps IS INITIAL.
      out->display( 'You must create database entries' &
                    ' with program RS_EPM_DGC_HANA first ...' ).
      RETURN.
    ENDIF.
    DATA(rnd) =  cl_abap_random_int=>create(
                   seed = CONV i( sy-uzeit )
                   min  = 1
                   max  = lines( time_stamps ) )->get_next( ).
    CONVERT TIME STAMP time_stamps[ rnd ] TIME ZONE 'UTC'
                       INTO DATE DATA(payment_date).
    cl_demo_input=>request( CHANGING field = payment_date ).

    DATA(oref) = NEW cl_demo_amdp_vs_open_sql( ).

    TRY.
        GET RUN TIME FIELD DATA(t01).
        oref->amdp(
          EXPORTING iv_payment_date   = payment_date
                    iv_clnt           = sy-mandt
          IMPORTING et_invoice_header = DATA(invoice_header)
                    et_invoice_item   = DATA(invoice_item)
                    et_customer_info  = DATA(customer_info) ) .
        GET RUN TIME FIELD DATA(t02).
      CATCH cx_amdp_error INTO DATA(amdp_error).
        out->display( amdp_error->get_text( ) ).
        RETURN.
    ENDTRY.

    GET RUN TIME FIELD DATA(t11).
    oref->open_sql_nested_select(
      EXPORTING iv_payment_date   = payment_date
      IMPORTING et_invoice_header = DATA(invoice_header1)
                et_invoice_item   = DATA(invoice_item1)
                et_customer_info  = DATA(customer_info1) ) .
    GET RUN TIME FIELD DATA(t12).

    GET RUN TIME FIELD DATA(t21).
    oref->open_sql_for_all_entries(
      EXPORTING iv_payment_date   = payment_date
      IMPORTING et_invoice_header = DATA(invoice_header2)
                et_invoice_item   = DATA(invoice_item2)
                et_customer_info  = DATA(customer_info2) ) .
    GET RUN TIME FIELD DATA(t22).

    GET RUN TIME FIELD DATA(t31).
    oref->open_sql_subquery(
      EXPORTING iv_payment_date   = payment_date
      IMPORTING et_invoice_header = DATA(invoice_header3)
                et_invoice_item   = DATA(invoice_item3)
                et_customer_info  = DATA(customer_info3) ) .
    GET RUN TIME FIELD DATA(t32).

    IF lines( invoice_header ) IS INITIAL.
      out->display( 'Nothing found' ).
      RETURN.
    ENDIF.

    out->begin_section( `Lines of tables filled`
      )->write( |INVOICE_HEADER: {
                   lines( invoice_header ) }\n\n| &&
                |INVOICE_ITEM:   {
                   lines( invoice_item ) }\n\n|  &&
                |CUSTOMER_INFO:  {
                   lines( customer_info ) }| ).

    out->next_section( `Runtime AMDP `
      )->write( |{ CONV decfloat34(
                  ( t02 - t01 ) / 1000000 ) } seconds| ).

    IF invoice_header  = invoice_header1 AND
       invoice_item    = invoice_item1   AND
       customer_info   = customer_info1.
      out->next_section( `Runtime Open SQL with Nested SELECT`
        )->write( |{ CONV decfloat34(
                      ( t12 - t11 ) / 1000000 ) } seconds| ).
    ELSE.
      out->write( 'Different results in Open SQL with Nested SELECT' ).
    ENDIF.

    IF invoice_header  = invoice_header2 AND
       invoice_item    = invoice_item2   AND
       customer_info   = customer_info2.
      out->next_section( `Runtime Open SQL with FOR ALL ENTRIES`
        )->write( |{ CONV decfloat34(
                      ( t22 - t21 ) / 1000000 ) } seconds| ).
    ELSE.
      out->write(
        'Different results in Open SQL with FOR ALL ENTRIES' ).
    ENDIF.

    IF invoice_header  = invoice_header3 AND
       invoice_item    = invoice_item3   AND
       customer_info   = customer_info3.
      out->next_section( `Runtime Open SQL with Subquery`
        )->write( |{ CONV decfloat34(
                      ( t32 - t31 ) / 1000000 ) } seconds| ).
    ELSE.
      out->write( 'Different results in Open SQL with Subquery' ).
    ENDIF.


    out->display( ).

Description

The AMDP class CL_DEMO_AMDP_VS_OPEN_SQL has an AMDP method AMDP and the regular methods OPEN_SQL_NESTED_SELECT, OPEN_SQL_FOR_ALL_ENTRIES, and OPEN_SQL_SUBQUERY. All take the same data from database tables in the EPM model. These database tables can be filled using the program RS_EPM_DGC_HANA. Create the master data again here (X) and enter, for example, the values 100000 for Number of Sales Orders and 90000 for Number Delivered, plus a time interval of approximately three months.

The example program selects a random date from the existing change data and uses it to make the selections. The selections pick invoices paid on this date, plus the associated business partners and product information.

The method OPEN_SQL_NESTED_SELECT uses nested SELECT statements and provides the worst result, as expected. However, the use of FOR ALL ENTRIES in OPEN_SQL_FOR_ALL_ENTRIES or the same subqueries in OPEN_SQL_SUBQUERY as in AMDP demonstrates that AMDP does not provide any performance benefits as long as only SQL is implemented that can also be expressed in Open SQL.

SQLScript implementation of the method AMDP

METHOD amdp BY DATABASE PROCEDURE FOR HDB
       LANGUAGE SQLSCRIPT OPTIONS READ-ONLY
       USING snwd_ad snwd_bpa snwd_so_inv_head snwd_so_inv_item.

  --  Selection of invoices paid on a specified date
  --  plus business partner and product information

  -- Retrieve all invoice header which were paid on the requested date
  et_invoice_header = select
      node_key            as invoice_guid,
      created_at as created_at,
      changed_at as paid_at,
      buyer_guid
    from
      snwd_so_inv_head
    where
      client         = :iv_clnt
      and payment_status = 'P' -- only paid invoices
      and left(changed_at, 8) = :iv_payment_date
      order by invoice_guid;

  -- Get the items of those invoices
  et_invoice_item = select
      node_key   as item_guid,
      parent_key as invoice_guid,
      product_guid,
      gross_amount,
      currency_code
    from snwd_so_inv_item
    where parent_key in
        ( select invoice_guid
            from :et_invoice_header )
      order by item_guid, invoice_guid, product_guid;

  -- Get the information about the customers
  et_customer_info = select
      bpa.node_key     as customer_guid,
      bpa.bp_id        as customer_id,
      bpa.company_name as customer_name,
      ad.country,
      ad.postal_code,
      ad.city
    from snwd_bpa as bpa
    join snwd_ad as ad on ad.node_key = bpa.address_guid
    where bpa.node_key in ( select distinct buyer_guid
                              from :et_invoice_header )
    order by customer_id;
ENDMETHOD.

ABAP implementation of the method OPEN_SQL_NESTED_SELECT

METHOD open_sql_nested_select.

  "Selection of invoices paid on a specified date
  "plus business partner and product information

  DATA ls_invoice_head  TYPE ty_invoice_header.
  DATA lt_invoice_item  TYPE tt_invoice_item.
  DATA lt_customer_info TYPE tt_customer_info.

  DATA lv_payment_date_min TYPE timestamp.
  DATA lv_payment_date_max TYPE timestamp.

  CONVERT DATE iv_payment_date TIME '0001'
    INTO TIME STAMP lv_payment_date_min TIME ZONE 'UTC'.
  CONVERT DATE iv_payment_date TIME '2359'
    INTO TIME STAMP lv_payment_date_max TIME ZONE 'UTC'.

  "Retrieve all invoice header which were paid on the requested date
  SELECT
    node_key       AS invoice_guid,
    created_at     AS created_at,
    changed_at     AS paid_at,
    buyer_guid
  FROM
    snwd_so_inv_head
  INTO @ls_invoice_head
  WHERE                                               "#EC CI_NOFIELD
    payment_status = 'P'
    AND changed_at BETWEEN @lv_payment_date_min AND @lv_payment_date_max
  ORDER BY invoice_guid.

    CLEAR lt_invoice_item.
    CLEAR lt_customer_info.

    "Get the items of invoice
    SELECT
      node_key   AS item_guid,
      parent_key AS invoice_guid,
      product_guid,
      gross_amount,
      currency_code
    FROM snwd_so_inv_item
    INTO TABLE @lt_invoice_item
    WHERE parent_key = @ls_invoice_head-invoice_guid
    ORDER BY item_guid, invoice_guid, product_guid.

    "Get the information about the customers
    SELECT
     bpa~node_key     AS customer_guid,
     bpa~bp_id        AS customer_id,
     bpa~company_name AS customer_name,
     ad~country,
     ad~postal_code,
     ad~city
   FROM snwd_bpa AS bpa
   JOIN snwd_ad AS ad ON ad~node_key = bpa~address_guid
   INTO TABLE @lt_customer_info
   WHERE bpa~node_key = @ls_invoice_head-buyer_guid.

    APPEND ls_invoice_head           TO et_invoice_header.
    APPEND LINES OF lt_invoice_item  TO et_invoice_item.
    APPEND LINES OF lt_customer_info TO et_customer_info.
  ENDSELECT.

  "Remove duplicates
  SORT et_customer_info BY customer_guid.
  DELETE ADJACENT DUPLICATES FROM et_customer_info.
ENDMETHOD.

ABAP implementation of the method OPEN_SQL_FOR_ALL_ENTRIES

METHOD open_sql_for_all_entries.

  "Selection of invoices paid on a specified date
  "plus business partner and product information

  DATA lv_payment_date_min TYPE timestamp.
  DATA lv_payment_date_max TYPE timestamp.

  CONVERT DATE iv_payment_date TIME '0001'
    INTO TIME STAMP lv_payment_date_min TIME ZONE 'UTC'.
  CONVERT DATE iv_payment_date TIME '2359'
    INTO TIME STAMP lv_payment_date_max TIME ZONE 'UTC'.

  "Retrieve all invoice header which were paid on the requested date
  SELECT
    node_key       AS invoice_guid,
    created_at     AS created_at,
    changed_at     AS paid_at,
    buyer_guid
  FROM
    snwd_so_inv_head
  INTO TABLE @et_invoice_header
  WHERE                                               "#EC CI_NOFIELD
    payment_status = 'P'
    AND changed_at BETWEEN @lv_payment_date_min AND @lv_payment_date_max
  ORDER BY invoice_guid.

  "Get the items of those invoices
  SELECT
      node_key   AS item_guid,
      parent_key AS invoice_guid,
      product_guid,
      gross_amount,
      currency_code
   from snwd_so_inv_item
   into table @et_invoice_item
   for all entries in @et_invoice_header
   where  parent_key = @et_invoice_header-invoice_guid
   order by primary key.

  "Get the information about the customers
  SELECT
      bpa~node_key     AS customer_guid,
      bpa~bp_id        AS customer_id,
      bpa~company_name AS customer_name,
      ad~country,
      ad~postal_code,
      ad~city
    FROM snwd_bpa AS bpa
    JOIN snwd_ad AS ad ON ad~node_key = bpa~address_guid
    INTO TABLE @et_customer_info
    FOR ALL ENTRIES IN @et_invoice_header
    WHERE bpa~node_key = @et_invoice_header-buyer_guid.

  SORT et_customer_info BY customer_guid.

ENDMETHOD.

ABAP implementation of the method OPEN_SQL_SUBQUERY

METHOD open_sql_subquery.

  "Selection of invoices paid on a specified date
  "plus business partner and product information

  DATA lv_payment_date_min TYPE timestamp.
  DATA lv_payment_date_max TYPE timestamp.

  CONVERT DATE iv_payment_date TIME '0001'
    INTO TIME STAMP lv_payment_date_min TIME ZONE 'UTC'.
  CONVERT DATE iv_payment_date TIME '2359'
    INTO TIME STAMP lv_payment_date_max TIME ZONE 'UTC'.

  "Retrieve all invoice header which were paid on the requested date
  SELECT
   node_key       AS invoice_guid,
   created_at     AS created_at,
   changed_at     AS paid_at,
   buyer_guid
FROM
   snwd_so_inv_head
INTO TABLE @et_invoice_header
     WHERE
   payment_status = 'P'
   AND changed_at BETWEEN @lv_payment_date_min AND @lv_payment_date_max
ORDER BY invoice_guid.

  "Get the items of those invoices
  SELECT
      node_key   AS item_guid,
      parent_key AS invoice_guid,
      product_guid,
      gross_amount,
      currency_code
   FROM snwd_so_inv_item
   INTO TABLE @et_invoice_item
   WHERE  parent_key IN
        ( SELECT node_key FROM snwd_so_inv_head
             WHERE payment_status = 'P'
               AND changed_at
                     BETWEEN @lv_payment_date_min AND @lv_payment_date_max )
   ORDER BY item_guid, invoice_guid, product_guid.

  "Get the information about the customers
  SELECT
      bpa~node_key     AS customer_guid,
      bpa~bp_id        AS customer_id,
      bpa~company_name AS customer_name,
      ad~country,
      ad~postal_code,
      ad~city
    FROM snwd_bpa AS bpa
    JOIN snwd_ad AS ad ON ad~node_key = bpa~address_guid
    INTO TABLE @et_customer_info
    WHERE bpa~node_key IN ( SELECT buyer_guid FROM snwd_so_inv_head
             WHERE payment_status = 'P'
               AND changed_at
                     BETWEEN @lv_payment_date_min AND @lv_payment_date_max )
    ORDER BY customer_id.

ENDMETHOD.