Package: CRMS4_TEST_DATA_GENERATION - Carsten's original report

(1) API: BAPI_MATERIAL_SAVEREPLICA
(2) BP report: CRMS4_HOME_CREATE_BPS
(3) Service Order: CRMS4_HOME_CREATE_ORDERS

涉及到的一些数据库表

(1) TVKOV: Org. Unit: Distribution Channels per Sales Organization (1) VKORG: Sales Organization (2) VTWEG: Distribution Channel

(2) T001: company code (1) BUKRS: SELECT FROM t001 FOR ALL ENTRIES IN lt_tvko WHERE bukrs = lt_tvko-bukrs (2) LAND1: country key

(3) TVKO: Organizational Unit: Sales Organizations (1) VKORG: Sales Organization (2) include SI_TVKO: Organizational Unit: Sales Organizations detail (3) bukrs: Company code of the sales organization

(4) T134: Material type (1) prod_type_code: determine material or service - 02

(5) TVTA: Organizational Unit: Sales Area(s)

(6) T137: Industries for materials

(7) TSTL: Taxes: Valid Tax Categories for Each Country (1) TALND: Country is relevant for taxes (2) TATYP: Tax category (sales tax, federal sales tax,...) char4

(8) MATERIAL_MAINTAIN_DARK (1) MAKT: long description (2) MARC: Plant Data for Material (3) MARD: Storage Location Data for Material (4) MFHM: Production Resource Tool (PRT) Fields in the Material Master (5) MARM: Units of Measure for Material (6) MEAN: International Article Numbers (EANs) for Material (7) MBEW: Material Valuation (8) STEU: For Taxes: Data Transfer

single order creation touched table:

(1) CRMS4D_BTX_H: shadow table containing guid, BUS type, object_id and process_type

(2) CRMS4D_SERV_H: Header of service transactions, a collection of many include structure for each extension / set

(3) CRMS4D_SCHEDLIN: Scheduled Lines Table

(4) CRMS4D_BTX_I: item shadow table

(5) CRMS4D_PARTNER: Customer Mgmt S4 DB Table for Business Partner Information

(6) CRMS4D_SERV_I: Items of service transactions

Initial performance data QGP/504

(1) Create single service order with single line item: 0.5 ~ 1 seconds

(2) Create single service order with random number of line item between 1 and 30: 2 ~ 3 seconds

(3) Create 100 service order with single line item: 16 seconds

(4) Create 100 service order with random number of line item between 1 and 30: 150 ~ 156 seconds

2018-01-15 Sync after new year holiday

Latest data size till 2018-01-15:

Step1: eliminate out-of-memory runtime error

In the very beginning my mass data generation report could not be running for a long time, because as time goes by, the allocated memory increases and finally ends up with an "out-of-memory" runtime dump.

For example, I have totally 1000 service order to create, and the package size is 100. When I have finished the creation for the first 900 service orders, the internal buffer for these 900 service orders should be cleaned up, or else there might be no enough memory for the last 100 order creation.
The internal buffer needs to be cleanup includes:

(1) product API buffer
(2) organization unit buffer
(3) partner buffer
(4) pricing buffer
(5) one order API buffer

One example of pricing buffer could be found below:

It holds some internal buffer which will not be released so as time goes by, these two buffer table turns bigger and bigger and finally eat up all memory.

Unfortunately the pricing API does not have any method currently to cleanup these two buffer table, so I have to use a workaround in QGP:

buffer cleanup:

Step2: figure out where the drop down list entries for Subject come from?

Step3: figure out subject data storage design
Suppose a given Service request has maintained this kind of category data, what exactly data is stored in database table?

2018-01-30 A very draft performance measurement

Scenario: search by header description
REPORT zorder_query.
PARAMETERS: descr TYPE crmd_orderadm_h-description OBLIGATORY DEFAULT 'Jerry',

        conta  TYPE char1 AS CHECKBOX DEFAULT abap_false,
        ttype  Type crmd_orderadm_h-process_type DEFAULT 'SRVO',
        ctype  type char1 AS CHECKBOX DEFAULT abap_false.

DATA: lt_selection_parameter TYPE genilt_selection_parameter_tab,

  ls_query_parameters    TYPE genilt_query_parameters,
  ls_selection_parameter TYPE genilt_selection_parameter.

DATA(lo_core) = cl_crm_bol_core=>get_instance( ).
lo_core->load_component_set( 'ONEORDER' ).

IF conta = abap_true.
ls_selection_parameter = VALUE #( attr_name = 'DESCRIPTION' sign = 'I' option = 'CP'
low = |{ descr }| ).
ELSE.
ls_selection_parameter = VALUE #( attr_name = 'DESCRIPTION' sign = 'I' option = 'EQ'
low = descr ).
ENDIF.

APPEND ls_selection_parameter TO lt_selection_parameter.

IF ctype = abap_true.
ls_selection_parameter = VALUE #( attr_name = 'PROCESS_TYPE' sign = 'I' option = 'EQ' low = ttype ).
APPEND ls_selection_parameter TO lt_selection_parameter.
ENDIF.

ls_query_parameters-max_hits = 100.

cl_crm_order_timer_home=>start( ).
TRY.

DATA(lo_collection) = lo_core->dquery(
    iv_query_name               = 'BTQSrvOrd'
    it_selection_parameters            = lt_selection_parameter
    is_query_parameters                = ls_query_parameters ).

CATCH cx_root INTO DATA(cx_root).

WRITE:/ cx_root->get_text( ).
RETURN.

ENDTRY.
cl_crm_order_timer_home=>stop( 'Search by Description' ).
WRITE:/ |Number of Service Orders found: { lo_collection->size( ) }| COLOR COL_NEGATIVE.

Result

around 16 seconds.
total records which match this criteria in DB: 65,660

DB data volume:

7.86 seconds for CDS view query:

CDS view: CRMS4_SERV_H_SEARCH

Test report used by Jerry in year 2017 for One order prototype performance test
You can find those report in QGS：ZCRMS4_ORDER_SEARCH_PARTY_NAME
Search service orders which contain specified business partner ID with given partner function

ZCRMS4_ORDER_SEARCH_PRODUCT_ID
Search service order which contain the line item product with given product id.

ZCRMS4_ORDER_SEARCH_PROD_SOLD
Search with combination of sold to party name and line item product id.

ZCRMS4_ORDER_SEARCH_SOLDTO_NAM
search by sold to party name.

Jerry will copy them to QGP soon.
Tip: Do SAT trace on applications which could not be launched by SAT

Extensible CDS view done by China team for reference
package and CDS view listed below:

I quickly go through your view CRMS4_SERV_H_SEARCH and it seems fine for me. I am not clear why it does not work for extensibility.

Please read this article knowledge article written by Jerry on 2016-12-8 to check whether it helps. Sorry for Chinese because it is written for colleagues in China. You can try to use Google translation.

Search criteria supported in prototype done in year 2017, for your reference

check area under BUS2000116 (CRMST_QUERY_SRVO_BTIL).

(1) The cell with green means those criteria is supported in our prototype.
(2) The cell with silver means in theory they will be very easy to be supported.
(3) The cell with red means they are not supported in prototype due to complexity.

2018-02-05 Sync about Order Header description field search performance issue

What Jerry has learned from HANA DB colleague

When I paste it and execute in HANA studio, it is executed statically. On the other hand when I execute my ABAP report, the SQL statement is executed dynamically. The word dynamically means the search parameter for SQL is not known by HANA engine until runtime.

and paste the statement arguments into HANA studio as well:
504,BUS2000116,BUS2000116,BUS2000140,BUS2000105,BUS2000137,BUS2000131,BUS2000146,BUS2000159,BUS2000153,BUS2000199,,Y,,%2017-12-21%,SRVO,100

this time we get the consistent performance result executed in HANA studio and in ABAP:

approach 1
What is SAP HANA Hint?
From SAP note 2142945
Hints in the context of this SAP Note are instructions for the SAP HANA database server.
Hints can influence the way how a database request is generated or processed.
They don't change the result set of the database requests.
Hints are typically used in order to optimize SAP HANA performance or memory consumption. They may be used temporarily for testing purposes or permanently.
before hint is applied

after hint is applied

Less than one second

What is hint no_cs_join?

Which problems and pitfalls exist in the area of hints?

Behavior in case of invalid hints

Fuzzy search in S4 Material search implementation

consumption view:

hint is also used in SADL query implementation.

incident 1880139488 is created for "SAP DBTech JDBC: [258]: insufficient privilege: Not authorized".
To discard Performance Project incidents from Rel1 Monitoring we approved in our today Product team meeting to enter in “Info” field of the incident: “S4CRM-200” please update this incident accordingly.

2018-02-13 sync meeting
Some test data:
product id： SANSUMGC7PRO000000
description: Jerry 2018-02-10 9:35PM
sold to party id: VIP000001
sold to past mc name1: Ali website 1 the involved CDS view is determined based on object type:

Search by product id

select from:
CRMS4_SERVHSRCH INNER JOIN CRMS4_SERVISRCH ON CRMS4_SERVISRCH~OBJECT_ID = CRMS4_SERVHSRCH~OBJECT_ID
where statement:

performance: 1.54 seconds

When I change to use another product id: ANDROID0000000001, Performance turns poor:

but only 0.2 second is spent on CDS view fetch?

Search by sold to party ID
select from:
CRMS4_SERVHSRCH INNER JOIN CRMS4D_PARTNER ON CRMS4D_PARTNER~OBJECT_ID = CRMS4_SERVHSRCH~OBJECT_ID

performance: 1.47 seconds

search by sold to party name

The second where - CRMS4_SERVHSRCH:

The third where - CRMS4_SERVISRCH:

One question here:
I have specified "Wang" as sold to party name, however according to the above screenshot, another String "HYBRIS" is used to fire the CDS view search.

till now the value is still "Wang":

pay attention to the parameter: iv_mc_name1

So HYBRIS is partner ID: finally the specified SOLD_TO_PARTY_NAME is converted to SOLD_TO_PARTY(ID) and searched against CRMS4_SERVISRCH.
performance: 1.28 seconds