End routine Look up Performance Issue

[Former Member]

Hi All!

I tried to optimize a code for a long time but haven't been able to do so. I have made secondary indexes, created internal tables but the performance still has't improved. It takes 5 hours for 138,000 to look-up from a DSO containing a million records.

Tried tracing too from st05 found it takes time to open and fetch the data.

Following is the code:

if RESULT_PACKAGE is not INITIAL .

       SELECT

              /BIC/ZEMAIL

              /BIC/ZMOBILE

              HE_YRSES FROM /BIC/AZLMS_D0300

         INTO TABLE LT_AZLMS_D0300 FOR ALL ENTRIES IN RESULT_PACKAGE

         WHERE /BIC/ZEMAIL eq RESULT_PACKAGE-/BIC/ZEMAIL or

               /BIC/ZMOBILE eq RESULT_PACKAGE-/BIC/ZMOBILE .

if sy-subrc = 0.

         sort LT_AZLMS_D0300 by HE_YRSES .

       endif.

LOOP at RESULT_PACKAGE ASSIGNING <RESULT_FIELDS> .

         READ TABLE LT_AZLMS_D0300 INTO LS_AZLMS_D0300 WITH KEY

                  /BIC/ZEMAIL = <RESULT_FIELDS>-/BIC/ZEMAIL .

         if sy-subrc = 0 .

           <RESULT_FIELDS>-/BIC/ZHE_LCYSE = LS_AZLMS_D0300-HE_YRSES .

           if <RESULT_FIELDS>-/BIC/ZHE_LCYSE EQ <RESULT_FIELDS>-HE_YRSES

           .

             <RESULT_FIELDS>-/BIC/ZSTATU  =  'N' .

           else.

             <RESULT_FIELDS>-/BIC/ZSTATU  =  'L' .

           endif.

         else.

           READ TABLE LT_AZLMS_D0300 INTO LS_AZLMS_D0300 WITH KEY

                                 /BIC/ZMOBILE =

                                 <RESULT_FIELDS>-/BIC/ZMOBILE .

           if sy-subrc = 0.

             <RESULT_FIELDS>-/BIC/ZHE_YRSES = LS_AZLMS_D0300-HE_YRSES .

           if <RESULT_FIELDS>-/BIC/ZHE_LCYSE EQ <RESULT_FIELDS>-HE_YRSES

           .

             <RESULT_FIELDS>-/BIC/ZSTATU  =  'N' .

           else.

             <RESULT_FIELDS>-/BIC/ZSTATU  =  'L' .

           endif.

           endif.

         ENDIF  .

         CLEAR : LS_AZLMS_D0300 .

       ENDLOOP .

     endif .

Thanks,

Kunal

[former_member186740 · Accepted solution]

Hello Kunal   in all your read statements try using binary search   (READ TABLE LT_AZLMS_D0300 INTO LS_AZLMS_D0300 WITH KEY                                  /BIC/ZMOBILE =                                  -/BIC/ZMOBILE  binary search.)  This will minimize the read time .

[matt · Accepted solution]

Standard table with sort and binary search are old technology. Use HASHED tables where possible, otherwise SORTED tables.

[umashankar_poojar]

Hi Kunal,

       SELECT statements are the one which degrade the performance, Could you please check on that?

Just try below concept,

• SELECT Mobile and Email from DSO and store it in a two Arrays, Mobile[] and Email[]. Since you need where clause, just keep where Version is Active.
• Split Result Package into Two, Result package1 should have only data related to Mobile[], Result Package2 should only have data related to Email[].

          You can achieve above step as

1. Move all data from Result Package to Result Package1 and Result Package2.
2. Delete records from Result Package1 where Result Package1-Mobile Not In Mobile[]
3. IIIly Delete records from Result Package2 where Resul Package2-Email Not In Email[].

This is just a algorithm, ask an ABAP consultant to replace your existing SELECT with above steps.

By doing this, you will avoid the current SELECT statement looking and searching your whole Result Package

At the end, you can collate all and append to RESULT PACKAGE.

Hope it gives an Idea! let me know if above steps are not clear.

Thanks,

Umashankar

[matt]

Why do you say select version active? He's selecting from the 00 DSO table. All the data in it is active.

Your solution does look remarkably similar to what I posted yesterday, including the advice to consult an experienced abapper.

[umashankar_poojar]

Hi Matthew,

            Select statement would need a where clause to pick records, so have mentioned about version. Not necessary to use that.

Yes it is similar, but instead of using two select statements have asked to move data into Two temporary Result Packages. Then delete unnecessary data, i.e., Keep only relevant data in packages.

My concern here is not to use more select statements.

Hope it is clear!

Thanks,

Umashankar

[matt]

It is the single selection that is likely to be killing performance, since it contains an OR. Select statements do not have to have any WHERE clause.

For the OP - you should trace via ST05 to check how the SQL optimiser is actually constructing the select from the database.

[Former Member]

Thank you Matthew and Umashankar..

Will come back once we have implemented the steps...

[Former Member]

Hi Matthew!

You were spot on with the "OR" in the where clause. We made two internal tables with one selection each for mobile and email and the performance has increased drastically. Thank you to other guys too who pitched in. Rewarded points accordingly.

Kunal

[matt]

Good. Did you use HASHED tables as well? I've seen enormous improvements in performance simply from using the correct type of internal table.

[ravi_chandra3]

Hi

Sort the internal table and then perform the binary search,

and please once check the availability of BGD job in sm50, try to increase the BGD job with the help of BASIS team.

[Former Member]

Hi Ravi!

We already have 6 background processes in our server. The problem seem to be coming from fetching data from the DSO in the beginning.

Kunal

[matt]

Standard table with sort and binary search are old technology. Use HASHED tables where possible, otherwise SORTED tables.

[ravi_chandra3]

Hi

You can go ahead by creating the HASHE D or SORTED as suggested by Matthew.

[matt]

Instead of one internal table, define two, and select zemail into one and the zmobile into the other, using separate SELECT statements. The internal tables should be hashed - one with unique key email, the other with unique key mobile. You may need to use the key word DISTINCT in the select to ensure there are no duplicates. Create two separate indexes for the dso you are selecting from, one for email one for select.

Use TABLE KEY instead of key when reading from the new internal tables.

If you are unable to follow what I've written, I suggest getting an ABAP expert in to do it.

By the way, I notice a common misunderstanding in your BW set up. User defined DSOs, cubes, InfoObjects do not have to begin with Z!

[Former Member]

Hi Matthew!

There are some records with both email and mobile no. and some with just mobile number or email id. So need to bring all of them into the internal table.

But I will work on the two internal table thing and get a abap expert for other points suggested. Thanks for the prompt response. Will come back once everything has been done.

Kunal

[matt]

According to the logic you've posted, you only care about the mobile if the email isn't found. In any case, the email table will contain records that have email only, and email and mobile, and the mobile table will contain records that contain mobile only, and email and mobile, so you are covered.

In case you have trouble locating a good abapper, and in the interests of educating the general population, this is what I would use.

TYPES: BEGIN OF email_ty,

         /bic/zemail TYPE /bic/oizemail,

         he_yrses    TYPE something,

      END OF email_ty.

TYPES: BEGIN OF mobile_ty,

         /bic/zmobile TYPE /bic/oimobile,

         he_yrses    TYPE something,

      END OF mobile_ty.

DATA: lth_email  TYPE HASHED TABLE OF email_ty  WITH UNIQUE KEY /bic/zemail,

      ls_email   type email_ty,

      lth_mobile TYPE HASHED TABLE OF mobile_ty WITH UNIQUE KEY /bic/zmobile,

      ls_mobile  type mobile_ty.

IF result_package IS NOT INITIAL .

  SELECT DISTINCT

         /bic/zemail

         he_yrses FROM /bic/azlms_d0300

    INTO TABLE lth_email FOR ALL ENTRIES IN result_package

    WHERE /bic/zemail EQ result_package-/bic/zemail.

  SELECT DISTINCT

         /bic/zmobile

         he_yrses FROM /bic/azlms_d0300

    INTO TABLE lth_mobile FOR ALL ENTRIES IN result_package

    WHERE /bic/zmobile EQ result_package-/bic/zmobile .

  LOOP AT result_package ASSIGNING <result_fields> .

    " Look for an email record

    READ TABLE lth_email INTO ls_email WITH TABLE KEY

             /bic/zemail = <result_fields>-/bic/zemail .

    IF sy-subrc EQ 0 .

      " Found one

      <result_fields>-/bic/zhe_lcyse = ls_email-he_yrses.

    ELSE.

      " No email record, so look for a mobile record

      READ TABLE lth_mobile INTO ls_mobile WITH TABLE KEY

                            /bic/zmobile =

                            <result_fields>-/bic/zmobile .

      IF sy-subrc EQ 0.

        " Found one

        <result_fields>-/bic/zhe_yrses = ls_azlms_d0300-he_yrses . 

      ELSE.

        " Neither email nor mobile, so just go to next record

        continue.

      ENDIF.

    ENDIF.

    " Set the status

    IF <result_fields>-/bic/zhe_lcyse EQ <result_fields>-he_yrses.

      <result_fields>-/bic/zstatu  =  'N' .

    ELSE.

      <result_fields>-/bic/zstatu  =  'L' .

    ENDIF.

  ENDLOOP .

Together with indexes on the source DSO as I described earlier, this will be fairly efficient. There is another approach, but if this works well enough, then you don't need to know about that. The other approach requires more complex programming.

matt