masato inoue, customer solution adoption(csa) may 29, 2013 · 2019. 11. 12. · masato inoue,...

Masato Inoue, Customer Solution Adoption(CSA)

May 29, 2013

ABAP on SAP HANA Optimization of Custom ABAP Codes for SAP HANA

© 2013 SAP AG or an SAP affiliate company. All rights reserved. 2

Disclaimer

This presentation outlines our general product direction and should not be relied on in

making a purchase decision. This presentation is not subject to your license

agreement or any other agreement with SAP. SAP has no obligation to pursue any

course of business outlined in this presentation or to develop or release any

functionality mentioned in this presentation. This presentation and SAP's strategy and

possible future developments are subject to change and may be changed by SAP at

any time for any reason without notice. This document is provided without a warranty

of any kind, either express or implied, including but not limited to, the implied

warranties of merchantability, fitness for a particular purpose, or non-infringement.

SAP assumes no responsibility for errors or omissions in this document, except if

such damages were caused by SAP intentionally or grossly negligent.


Agenda

General Approach

Considering optimization level

Optimization Step 1 : Accelerate

Optimization without using SAP HANA view/procedure

Optimization Step 2: Extend

Optimization using SAP HANA view/procedure

Optimization Step 3: Innovate

Considering Totally New Use Cases

ERP on SAP HANA: Learn from the SAP standard application

Standard HANA views/procedures delivered as a part of ERP on SAP HANA

Appendix: Sample ABAP codes (Can be used for your PoC)

Sample table definitions

Test data generator / Performance measurement

General Approach Considering Optimization Level


Optimization Levels How Far Should Custom ABAP Codes Be Optimized?

Accelerate

Reduce time

windows of batch

jobs

Improve response

times for end-

users

Extend

Address more users

From background jobs

to interactive UIs

Add features and

improve usability

Innovate

New processes

New applications

New consumption

channels

Optimization Step 1 Optimization without using SAP HANA view/procedure

Accelerate

Reduce time

windows of batch

jobs

Improve response

times for end-

users


Classical five performance guidelines for ABAP DB access Source: SAP NetWeaver documentation (under Open SQL Performance notes)


• Report ZXXX_BOOKBILL

LOOP AT lt_bill INTO ls_bill.

MOVE ls_bill-bill_num TO ls_zbbnbill-bill_num_key.

MOVE ls_bill-bill_line TO ls_zbbnbill-bill_line_key.

MOVE-CORRESPONDING ls_bill TO ls_zbbnbill.

CLEAR ls_zbbnbill-burdened_cost.

MODIFY ztable1 FROM ls_zbbnbill.

ENDLOOP.

Accessing DB 70000

times.

Sample of a Real Custom ABAP Code Optimization Candidate for “Minimize the number of data transfers”


• Report ZXXX_BOOKBILL

LOOP AT lt_bill INTO ls_bill.

MOVE ls_bill-bill_num TO ls_zbbnbill-bill_num_key.

MOVE ls_bill-bill_line TO ls_zbbnbill-bill_line_key.

MOVE-CORRESPONDING ls_bill TO ls_zbbnbill.

CLEAR ls_zbbnbill-burdened_cost.

MODIFY ztable1 FROM ls_zbbnbill.

ENDLOOP.

Accessing DB 70000

times.

Issues to be optimized:

Too many DB access.

Is the table ztable1 necessary?

Optimization Step 2

Sample of a Real Custom ABAP Code Optimization Candidate for “Minimize the number of data transfers”


Table Definition

Sample of a Simplified Prototyping for Optimization Optimization Candidate for “Minimize the number of data transfers”

Generated test data in the table ZMI003


Improve performance time by doing ABAP code optimization. Example move the

Modify statement outside of the loop

Modify inside loop Modify Outside loop

Sample of a Simplified Prototyping for Optimization Optimization Candidate for “Minimize the number of data transfers”

REPORT ZZMI_PERF_TEST_MODIFY1_1.

data: ls_tab like zmi003,

lf_counter type i,

lf_t1 type i,

lf_string(10) value '1234567890'.

* Reset runtime field: lf_t1 will be set to 0.

get run time field lf_t1.

do 70000 times.

add 1 to lf_counter.

ls_tab-k1 = lf_counter.

shift lf_string circular. "this operation is light weight

ls_tab-d1 = lf_string.

* 70000 times database access inside of the loop

modify zmi003 from ls_tab.

enddo.

* Get actual execution time to the field lf_t1.


write:/ lf_t1, 'micro seconds'.



lt_tab like zmi003 occurs 0,

lf_counter type i,

lf_t1 type i,

lf_string(10) value '2345678901'.



do 70000 times.



shift lf_string circular. "This operation is light weight.


append ls_tab to lt_tab.

enddo.

* One time database access outside of the loop

modify zmi003 from table lt_tab.





SAP HANA

ANYRDB

Sample of a Simplified Prototyping for Optimization Result of ABAP Code Optimization

Optimization Step 2 Optimization using SAP HANA view/procedure

Extend

Address more users

From background jobs

to interactive UIs

Add features and

improve usability


Based on the program review ZXXX_BOOKBILL

Standard ERP Tables

VBRK VBAK

VBRP

MARA FPLT

Batch

Job

Custom ABAP

programs

Custom tables to store

data temporarily

ZBBNBILL ZTABLE1

Reporting Client

Long

execution.

Needs physical DB

tables. These tables

are required in addition

to ERP tables.

Sample of a Real Custom ABAP Code A typical case of a performance critical batch job


Possible Optimization Using HANA

HANA Views require no additional DB tables.

Standard ERP Tables

VBRK VBAK

VBRP

MARA FPLT Batch

Job

Custom ABAP

programs


data temporarily

ZBBNBILL ZTABLE1

Reporting Client HANA View / HANA

Procedure

HANA

Procedure

HANA

Procedure

HANA View HANA View

No need to run

batch programs

because of

HANA View.

More

flexible

reporting

design.

HANA

View

accesses

standard

ERP tables.


Demo ERP Tables and Demo HANA Views

Demo ERP tables and HANA views created to demonstrate the performance of

HANA

Reporting Client HANA View / HANA

Procedure

HANA View HANA View

DEMO ERP Tables

ZHDR01 ZDMN01

ZITM01

ZDMN02

100000 records

600000 records

Batch

Job

Custom ABAP

programs


data temporarily

ZBBNBILL ZTABLE1

More

flexible

reporting

design.


Simplified Data Model in HANA – Analytical View


Code optimization with Consuming HANA View

Consuming HANA view in ABAP as external view

REPORT ZZMI_DEMO_HANA_VIEW1.

types:

begin of ts_v1,

hdr1d03 type char3,

sum type DEC_16_02_S,

end of ts_v1.

data:

lt_v1 type ts_v1 occurs 0,

lt_zexztim01 type zexztim01 occurs 0,

lf_t1 type i.

field-symbols: <v1> type ts_v1.



select hdr1d03 SUM( itm1d04 ) as sum

from zexztim01

into table lt_v1

where dmn2d03 = 'CCC'

group by hdr1d03.



write:/ lf_t1, 'micro second'.

sort lt_v1.

loop at lt_v1 assigning <v1>.

write: / <v1>-hdr1d03, <v1>-sum.

endloop.

Accessing

HANA View

via external

view


New in ABAP (SAP_BASIS 740)

External View

A special view in ABAP Dictionary

Functions as a proxy for an SAP HANA view

– HANA view: Attribute View, Analytic View, Calculation View)

Can be accessed using HANA and Open SQL

Can only be created using the Eclipse-based ABAP Development Tools.

New ABAP Statement: CALL DATABASE PROCEDURE

Can be used only in systems with an SAP HANA database

It calls a database procedure written in SQLScript

A database procedure will be created in HANA Studio.


External View

Design Time

An external view must be created in Eclipse-based ABAP Development Tools for SAP

NetWeaver


CALL DATABASE PROCEDURE

Syntax

CALL DATABASE PROCEDURE dbproc_proxy

[EXPORTING p1 = a1 p2 = a2 ...]

[IMPORTING p1 = a1 p2 = a2 ...].

Design Time

A procedure must be created in HANA Studio

– Default schema is _SYS_BIC and not changeable

A database procedure proxy must be created for each database procedure

– Database procedure proxy is a repository object in ABAP Dictionary that represents a database

procedure with respect to ABAP programs.

One database procedure proxy can be created for each database procedure in ABAP

perspective in Eclipse

The attribute IS_IN_MEMORY_DB of the class CL_DB_SYS can be used to query whether

the current system uses an SAP HANA database

Optimization Step 3 Innovation – Considering Totally New Use Cases

Innovate

New processes

New applications

New consumption

channels


Don’t Forget

ABAP is one of HANA clients

A HANA view can be very complex.

A well designed HANA view can be consumed not only by ABAP application.

ERP on SAP HANA Standard HANA views/procedures delivered as a part of ERP on SAP HANA

Learn from the SAP standard application

SAP HANA views/procedures Look from ABAP Side (As of ERP 616 SP1)


Storage Type

Column Store or Row Store


New View Type External View

Only 12 External Views in ERP on HANA

Where-used

function for the

views couldn’t

find anything!


Database Procedure Proxies

Only 18 Database Procedure

Proxies in ERP on HANA

New object type

‘SQSC’ for

Database

Procedure Proxy


Special Case of HANA View/Procedure Generation from

ABAP

No Delivery of HANA

view/procedure in HANA

package

All HANA views/procedures

will be generated from ABAP

Advantage when delivering

correction via SAP note.



ABAP

ABAP Include

programs which don’t

include ABAP code but

HANA statements

HANA statements can

be dynamically changed

during ABAP execution.



ABAP

ABAP Include



HANA statements

HANA statements can




Example of ADBC: Calling HANA Procedure

CALL “_SYS_BIC”.”sap.erp.sappl.lo.md.bom/DIR_EXPLODE_WITH_ATTRIBUTES”


Example of CALL DATABASE PROCEDURE

SAP HANA views/procedures Look from HANA Side (As of ERP 616 SP1)


Runtime System View “VIEWS” on HANA side

select view_type, count(view_type) from "SYS"."VIEWS" group by view_type

VIEWS



VIEWS

on

schema SAP<sid>

select schema_name, view_name, view_type from "SYS"."VIEWS"

where schema_name = 'SAPEOH' and ( view_type = 'JOIN' or view_type =

'OLAP' or view_type = 'CALC' )

order by view_name



select schema_name, view_name, view_type from "SYS"."VIEWS"

where schema_name = '_SYS_BIC' and ( view_type = 'JOIN' or view_type =

'OLAP' or view_type = 'CALC' )

order by view_type, view_name

VIEWS

on

schema _SYS_BIC


Runtime System View “PROCEDURES” on HANA side

select procedure_type, count(procedure_type) from "SYS"."PROCEDURES"

group by procedure_type

PROCEDURES



select schema_name, procedure_name, procedure_type from

"SYS"."PROCEDURES"

where schema_name = 'SAPEOH'

order by procedure_name

PROCEDURES

on

schema SAP<sid>




"SYS"."PROCEDURES"

where schema_name = '_SYS_BIC’


PROCEDURES

on

schema _SYS_BIC

1/3



PROCEDURES

on

schema _SYS_BIC

2/3


"SYS"."PROCEDURES"





PROCEDURES

on

schema _SYS_BIC

3/3


"SYS"."PROCEDURES"




Design Time System Table “ACTIVE_OBJECT” on HANA

side

select object_type_id, count(object_type_id) from

"_SYS_REPO"."ACTIVE_OBJECT" group by object_type_id order by

object_type_id

VIEWS and

PROCEDURES

on package

ACTIVE_OBJECT



side

select package_id, object_name, object_type_id from

"_SYS_REPO"."ACTIVE_OBJECT"

where ( object_type_id = 'attributeview' or object_type_id = 'analyticview' or

object_type_id = 'calculationview' )

order by package_id, object_type_id, object_name

VIEWS

on

Package

ACTIVE_OBJECT



side



where object_type_id = 'procedure'

order by package_id, object_name

PROCEDURES

on

Package

ACTIVE_OBJECT

1/3



side





PROCEDURES

on

Package

ACTIVE_OBJECT

2/3



side





PROCEDURES

on

Package

ACTIVE_OBJECT

3/3

Appendix

Sample ABAP Codes From the chapter “Optimization Step 1”


Table Structure




lf_counter type i,

lf_t1 type i,

lf_string(10) value '1234567890'.



do 70000 times.



shift lf_string circular. "this operation is light weight


* 70000 times database access inside of the loop

modify zmi003 from ls_tab.

enddo.






lt_tab like zmi003 occurs 0,

lf_counter type i,

lf_t1 type i,

lf_string(10) value '2345678901'.



do 70000 times.



shift lf_string circular. "This operation is light weight.


append ls_tab to lt_tab.

enddo.

* One time database access outside of the loop

modify zmi003 from table lt_tab.




Sample Programs Performance Comparison (MODIFY statement inside/outside of LOOP)

Sample ABAP Codes From the chapter “Optimization Step 2”


Table Structures

DEMO ERP Tables

ZHDR01 ZDMN01

ZITM01

ZDMN02

100000 records

600000 records


REPORT ZZMI_GENERATE_DATA_MPPX01.

delete from zitm01.

delete from zhdr01.

delete from zdmn01.

delete from zdmn02.

constants:

c_max_hdr type i value '100000', “Number of header

c_max_itm type i value '6', “Number of items for each header

c_max_dmn1 type i value '10000', “Dimension table 1

c_max_dmn2 type i value '10000', “Dimension table 2

c_max_days type i value '365', "Max days substructing from

current date Random date in the past one year

c_max_amount type i value '10000'.

data:

lf_int4 type i,

lf_date type d,

lf_amount type p,

lf_char128(128) type c,

lf_hdr_cnt type i value 0,

ls_zhdr1 type zhdr01,

lt_zhdr1 type zhdr01 occurs 0,

lf_itm_cnt type i value 0,

ls_zitm1 type zitm01,

lt_zitm1 type zitm01 occurs 0,

lf_dmn1_cnt type i value 0,

ls_zdmn1 type zdmn01,

lt_zdmn1 type zdmn01 occurs 0,

lf_dmn2_cnt type i value 0,

ls_zdmn2 type zdmn02,

lt_zdmn2 type zdmn02 occurs 0.

do c_max_dmn1 times.

add 1 to lf_dmn1_cnt.

ls_zdmn1-dmn1k02 = lf_dmn1_cnt.

* Random number of days substracting from system date

CALL FUNCTION 'RANDOM_I4'

EXPORTING

RND_MIN = 1

RND_MAX = c_max_days

IMPORTING

RND_VALUE = lf_int4.

ls_zdmn1-dmn1d02 = sy-datum - lf_int4.

* Random Combination of 3 characters between 'A', 'B' and 'C'

CALL FUNCTION 'RANDOM_C'

EXPORTING

LEN_MIN = 3

LEN_MAX = 3

CHAR_MIN = 1

CHAR_MAX = 3

IMPORTING

RND_VALUE = lf_char128.

ls_zdmn1-dmn1d03 = lf_char128.

append ls_zdmn1 to lt_zdmn1.

enddo.

insert zdmn01 from table lt_zdmn1.

Sample Programs Test Data Generator (Page 1/3)


do c_max_dmn2 times.

add 1 to lf_dmn2_cnt.

ls_zdmn2-dmn2k02 = lf_dmn2_cnt.



EXPORTING

RND_MIN = 1


IMPORTING


ls_zdmn2-dmn2d02 = sy-datum - lf_int4.



EXPORTING

LEN_MIN = 3

LEN_MAX = 3

CHAR_MIN = 1

CHAR_MAX = 3

IMPORTING


ls_zdmn2-dmn2d03 = lf_char128.

append ls_zdmn2 to lt_zdmn2.

enddo.

insert zdmn02 from table lt_zdmn2.

do c_max_hdr times.

add 1 to lf_hdr_cnt.

ls_zhdr1-hdr1k02 = lf_hdr_cnt.

* Value for the field D01 --> Key for dimension table 1


EXPORTING

RND_MIN = 1

RND_MAX = c_max_dmn1

IMPORTING


ls_zhdr1-hdr1d01 = lf_int4.



EXPORTING

RND_MIN = 1


IMPORTING


ls_zhdr1-hdr1d02 = sy-datum - lf_int4.



EXPORTING

LEN_MIN = 3

LEN_MAX = 3

CHAR_MIN = 1

CHAR_MAX = 3

IMPORTING


ls_zhdr1-hdr1d03 = lf_char128.

append ls_zhdr1 to lt_zhdr1.

enddo.

insert zhdr01 from table lt_zhdr1.



clear lf_hdr_cnt.

do c_max_hdr times.

add 1 to lf_hdr_cnt.

clear: lf_itm_cnt, ls_zitm1, lt_zitm1.

do c_max_itm times.

add 1 to lf_itm_cnt.

ls_zitm1-itm1k02 = lf_hdr_cnt.

ls_zitm1-itm1k03 = lf_itm_cnt.


EXPORTING

RND_MIN = 1

RND_MAX = c_max_dmn2

IMPORTING


ls_zitm1-itm1d01 = lf_int4.



EXPORTING

RND_MIN = 1


IMPORTING


ls_zitm1-itm1d02 = sy-datum - lf_int4.



EXPORTING

LEN_MIN = 3

LEN_MAX = 3

CHAR_MIN = 1

CHAR_MAX = 3

IMPORTING


ls_zitm1-itm1d03 = lf_char128.

* Amount


EXPORTING

RND_MIN = 1

RND_MAX = c_max_amount

IMPORTING


ls_zitm1-itm1d04 = lf_int4.

append ls_zitm1 to lt_zitm1.

enddo.

insert zitm01 from table lt_zitm1.

enddo.



REPORT ZZMI_DEMO_HANA_VIEW1.

types:

begin of ts_v1,

hdr1d03 type char3,


end of ts_v1.

data:



lf_t1 type i.




select hdr1d03 SUM( itm1d04 ) as sum

from zexztim01

into table lt_v1

where dmn2d03 = 'CCC'

group by hdr1d03.



write:/ lf_t1, 'micro second'.

sort lt_v1.



endloop.

Sample Programs Accessing HANA view and SQL

REPORT ZZMI_DEMO_SQL1.

types:

begin of ts_v1,

hdr1d03 type char3,


end of ts_v1.

data:



lf_t1 type i.




select h~hdr1d03 SUM( i~itm1d04 ) as sum

from ( ( ( zitm01 as i

inner join zhdr01 as h

on i~itm1k02 = h~hdr1k02 )

inner join zdmn01 as m

on m~dmn1k02 = h~hdr1d01 )

inner join zdmn02 as n

on n~dmn2k02 = i~itm1d01 )

into table lt_v1

where n~dmn2d03 = 'CCC'

group by h~hdr1d03.



write:/ lf_t1.

sort lt_v1.



endloop.

HANA

View

SQL

For ANYDB you need

to define a

corresponding

structure (see next

page)


Sample Programs Structure for zexztim01 (Only for ANYDB)

Further Info


Further Info

Relevant Training

HA400

– ABAP Programming for SAP HANA (2-day course)

o Addresses the most urgent enablement need around adopting and optimizing program code of

ABAP-based applications (Suite on HANA, including: ERP, CRM, SCM, and BW on HANA) when

implementing on or migrating to SAP HANA.

Delivery: Instructor led in physical or virtual classroom.

o This course will be reworked as a 3-day version on SAP HANA SPS06 / NW 7.4 and will be available

in August.

https://wiki.wdf.sap.corp/wiki/display/aie/Home



Further Info

Relevant SAP Notes

1785057

– Recommendations for migrating suite system to SAP HANA

o Automatic conversion of pool and cluster tables into transparent tables

o Check your customer-specific source code for whether implicit sorting is assumed for pool or cluster

table access. Check tool: Code Inspector (SAPKB74001) See attached screen shots in the note.

1794297

– Secondary Indexes for the business suite on HANA

1662726

– Optimizing select with FOR ALL ENTRIES in SAP HANA

1622681

– DBSL hints for SAP HANA

48230

– Parameterization for SELECT … FOR ALL ENTRIES







Further Info

Useful URLs

SAP Public Web

SAP Developer Center

– http://scn.sap.com/community/developer-center/

ABAP for HANA

– http://scn.sap.com/community/abap-for-hana

ABAP in Eclipse

– http://scn.sap.com/community/abap/eclipse

SAP Education and Certification Opportunities

– http://www.sap.com/training-and-education/

http://scn.sap.com/community/developer-center/



http://scn.sap.com/community/abap-for-hana





http://scn.sap.com/community/abap/eclipse

http://www.sap.com/training-and-education/






Further Info

Useful ABAP Tools

Transaction codes

ST05 Performance Trace

SCI Code Inspector (Extended for HANA)

SAT/SE30 Runtime Analysis


© 2013 SAP AG or an SAP affiliate company. All rights reserved.

Thank you

Contact information:

Masato Inoue

Senior Product Specialist

SAP HANA & ABAP

Customer Solution Adoption (CSA)

SAP Japan Co., Ltd.

[email protected]

masato inoue, customer solution adoption(csa) may 29, 2013 · 2019. 11. 12. · masato inoue,...

Documents