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
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
"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
"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
"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.