SELECT optimization for performance improvement

[Former Member]

Hi guys,

I have tried to optimize a program coding for the sake of performance improvement. The original coding was as follow

I have united SELECT statements in INNER JOIN as following

Unfortunately it didn’t bring any improvements. In opposite, it costs now more time with one SELECT. I am confused. Please advise what I did wrong. Below are screen shots from trace list (trx. ST05) for the coding BEFORE and AFTER.

For the information: 3 DB tables are involved (CRM system): ADRC, BUT020 and CRMD_ORDER_INDEX.

If I understood the trace list correctly, the system uses primary index for tables ADRC (key field ADDRNUMBER) and BUT020 (key field PARTNER + ADDRNUMBER). For the table CRMD_ORDER_INDEX the secondary index is used (screen shot is below)

Thanks in advance

Best regards

Andrej

[former_member184455]

Hello Andrej,

The database optimizer only takes the first key column of BUT020~0 index into account, which is the CLIENT field. So the join statement leads to a full scan on all entries of BUT020 in one client.

The COUNTRY information for ADRC is not taken into account.

It could be that the IN-clause for COUNTRY does not look very attractive (selective) for the database, so it decides for the full scan on the possibly smallest table, that is BUT020.

See what happens if you use an EQ condition for COUNTRY.

Best Regards, Randolf

[Former Member]

Thank you Randolf.

Could you tell me please how did you figure it out, that the BUT020~0 index taken into account, is the CLIENT field? It could be also other key field, e.g. PARTNER or ADDRNUMBER, but you said it was CLIENT. How do you know? Is it because of the statement in the trace list “# key columns: 1”?

Unfortunately I cannot create this SELECT with EQ for COUNTRY. The user might trigger a query for all different countries which I don’t know in advance. Otherwise I have to put SELECT statement in a loop over a table with countries, but this will not for sure improve the performance.

SAP delivers an index I11 for a table ADRC. This index is created for the field COUNTRY. I have activated it and optimizer picked it up. The performance was improved slightly.

[former_member184455]

Hello Andrej,

Yes, "# key columns: 1" means that only the first field of the index, in this case the client field, has been taken into account to optimize the access to BUT020.

How much did the performance improve when the database uses the index for COUNTRY on ADRC?

I guess the SELECT(s) will never become very fast on anyDB, since you do not have selective criteria. Whether JOIN or FAE is faster, might depend on the data constellation.

Best Regards, Randolf

[Former Member]

Hello Randolf,

thanks a lot for the valuable information. But how is it displayed in case two or more key fields are taken into account, for example PARTNER and  ADDRNUMBER? Something like this “# key columns: 2 & 3”?

I could see approximately 10% improvement when the database uses the index for COUNTRY on ADRC.

[former_member184455]

Hello Andrej,

the index is used from left to right. So if index fields are MANDT+ PARTNER + ADDRNUMBER,

# key columns: 1 --> MANDT

# key columns: 2 --> MANDT + PARTNER

# key columns: 3 --> MANDT + PARTNER + ADDRNUMBER

are used.

If PARTNER is missing in the WHERE-clause, ADDRNUMBER normally also cannot be used!

An exeption to this is an 'index skip scan' which some database platforms are able to, if the 'missing' field in the WHERE-clause has only a few distinct values, for example 'gender' or 'client'.

Best Regards, Randolf

[Former Member]

Now I got it. Thanks a lot. This is really very valuable information.

Best regards

Andrej

[deependra_shekhawat3]

Hi Andrej,

Could you please remove statement 'INTO CORRESPONDING FIELD OF TABLE' and use only 'INTO TABLE'. (Into corresponding field of table statement also decrease the performance).

on other hand, I would suggest please try to join first two table (ADRC & but020) and then select data from CRM table crmd_index using for all entries.

Please try as below :

TYPES : BEGIN OF lty_adrc_but,
         addrnumber TYPE ad_addrnum,
         partner    TYPE bu_partner,
        END OF lty_adrc_but,
       
        BEGIN OF lty_crmd_order,
          header TYPE crmt_object_guid,
        END OF lty_crmd_order.

DATA : lt_adrc_but TYPE STANDARD TABLE OF lty_adrc_but,
       lt_crmd_order TYPE STANDARD TABLE OF lty_crmd_order.

SELECT a~addrnumber
       b~partner
  FROM adrc AS a INNER JOIN but020 AS b ON a~addrnumber = b~addrnumber
  INTO TABLE lt_adrc_but
  WHERE country IN ls_multivalues-searchvalues.

IF NOT lt_adrc_but IS INITIAL.
  SORT lt_adrc_but BY partner.

  DELETE ADJACENT DUPLICATES FROM lt_adrc_but COMPARING partner.

  SELECT header
    FROM crmd_order_index
    INTO TABLE lt_crmd_order
    FOR ALL ENTRIES IN lt_adrc_but
    WHERE object_type = 'BUS2000111'
    AND   pft_6  = 'X'
    AND partner_no = lt_adrc_but-partner.

ENDIF.

[Former Member]

Thank you very much Deependra,

following your advice I have removed 'INTO CORRESPONDING FIELD OF TABLE'. I have also tested your coding but couldn’t see a big improvement. I have activated an index I11 for a table ADRC. This index is created for the field COUNTRY. Database optimizer picked it up and the performance was improved slightly.