performance for huge table

[Former Member]

Hi  Friends

I need to fill data from excel file and implement below logic for all the fields  and at last finally need to update custom table .

    A  table has  more then 35 fields  then in these fields there is one field  ' STATUS ',

first after picking data from file  need  to check all 35 fields one by one from starting 2nd field of table ,, if any field has value then its mean  1  ,if not then 0   like that  need to read table from  2nd field of table and concatenate these values and pass to  field  STATUS .

Eg   if  table  test had  5 fields

1  A =  'ttt' .

2  B =     ,

3  C =  '33'

4  D =  '343',

5  Status .      In this case  status values should be  '1011'.

now my query if  what could be the most optimized , performance side best method to write this logic as there will be multiple records and  35 fields need to read to update this logic .

[Former Member]

Why don't you give this a try yourself and get back to the group if you have specific questions?

Rob

[Former Member]

Hi

I tried this below code , it all working fine but as  MATNR is key field in table so  modify not allowing multiple records to update in table have same key field data 

where as in input file lots of records having same key fields value records which need to update

------------------------------------------------------------------------------
REPORT  zrul.
PARAMETERS       : p_ifname TYPE rlgrap-filename.
*FIELD-SYMBOLS: <fs> TYPE ANY.
DATA : v_con(20) TYPE c .
DATA : it_data TYPE TABLE OF alsmex_tabline INITIAL SIZE 0,
        is_data TYPE alsmex_tabline.

TYPES : BEGIN OF ty_tab,
           matnr TYPE matnr, "
           pernr TYPE pernr,
           name TYPE name,
           zrule TYPE char10,
           END OF ty_tab.

DATA : it_tab TYPE TABLE OF ztest10  INITIAL SIZE 0,
        is_tab LIKE LINE OF it_tab.

DATA  :  t_z TYPE TABLE OF ztest10,
          w_z TYPE  ztest10.

* If Input file name is not initial.
IF NOT p_ifname IS INITIAL.
* Upload EXCEL data into internal table

   p_ifname = 'c:\test.xlsx'.
   CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
     EXPORTING
       filename                = p_ifname
       i_begin_col             = 1
       i_begin_row             = 2
       i_end_col               = 31
       i_end_row               = 3
     TABLES
       intern                  = it_data
     EXCEPTIONS
       inconsistent_parameters = 1
       upload_ole              = 2
       OTHERS                  = 3.
   IF sy-subrc <> 0.
     MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
             WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
   ENDIF.
ENDIF.

* Append EXCEL Data into a internal table
LOOP AT it_data INTO is_data.
   AT NEW row.
     CLEAR is_tab.
   ENDAT.
   IF is_data-col = '0002'.
     MOVE is_data-value TO is_tab-matnr.
   ENDIF.
   IF is_data-col = '0003'.
     MOVE is_data-value TO is_tab-pernr.
   ENDIF.
   IF is_data-col = '0004'.
     MOVE is_data-value TO is_tab-name.
   ENDIF.

   AT END OF row.
     APPEND is_tab TO it_tab.
   ENDAT.
   CLEAR : is_data.
ENDLOOP.
* to apply logic of rule calculation
CLEAR : v_con,is_tab.
LOOP AT it_tab INTO is_tab.
   IF is_tab-matnr IS  INITIAL .
     CONCATENATE v_con  '0'  INTO v_con.
     is_tab-zrule = v_con.
   ELSE.
     CONCATENATE v_con  '1'  INTO v_con.
     is_tab-zrule = v_con.
   ENDIF.
   IF is_tab-pernr IS  INITIAL.
     CONCATENATE v_con  '0'  INTO v_con.
     is_tab-zrule = v_con.
   ELSE.
     CONCATENATE v_con  '1'  INTO v_con.
     is_tab-zrule = v_con.
   ENDIF.
   IF is_tab-name IS INITIAL.
     CONCATENATE v_con  '0'  INTO v_con.
     is_tab-zrule = v_con.
   ELSE.
     CONCATENATE v_con  '1'  INTO v_con.
     is_tab-zrule = v_con.
   ENDIF.

   MODIFY it_tab FROM is_tab.
   CLEAR : v_con,is_tab.

*assign component sy-index of structure wa to <fs>
*ASSIGN COMPONENT sy-index OF STRUCTURE is_tab to <FS>.
* if sy-subrc ne 0.
*      exit.
*   endif.
*     if <fs> is initial or <fs> co '0'.
*      concatenate is_tab-zrule  '0'  INTO is_tab-zrule.
*       else .
*      concatenate is_tab-zrule  '1'  INTO is_tab-zrule.
*    endif.
ENDLOOP.

*   INSERT INTO ZTEST10 VALUES is_tab .
*  INSERT ZTEST10 FROM TABLE it_tab ACCEPTING DUPLICATE KEYS.
MODIFY ztest10 FROM TABLE it_tab.
IF sy-subrc EQ 0.
   LOOP AT it_tab INTO is_tab.
     WRITE : / is_tab-matnr, is_tab-pernr, is_tab-name ,is_tab-zrule.

   ENDLOOP.
   ULINE.
ELSE.
   WRITE : / v_con.
ENDIF.

[Former Member]

You will need another key field. Pernr looks like that may be the one.

Rob

[Former Member]

Rob you are genius , 🙂 

your suggestion helped me a  lot , could you please help me to optimize the code from performance side as well because above code I used  for a  test custom table but in actual custom table there are around 35 to 40 fields   which  I need to check for same logic  and  update the  Zrule field  accordingly

Thanks & Regards

[Former Member]

Kushi Sharma wrote:

Rob you are genius , 🙂

I have some family members who may disagree with you

If you want help with a real situation, you will have to supply some real details.

I suggest that you close this discussion and start a new one.

Rob

Message was edited by: Rob Burbank

[Former Member]

Hi,

Try below ...

TYPES : BEGIN OF test,

           matnr TYPE char10,

           mtart TYPE char10,

           mbrsh TYPE char10,

           status TYPE char3,

         END OF test.

DATA : tab TYPE TABLE OF test,

        wa_mara TYPE test.

FIELD-SYMBOLS: <wa_mara>  TYPE test,

                <fs_value> TYPE any.

wa_mara-matnr = 100.

wa_mara-mtart = 'aaa'.

wa_mara-mbrsh = 'bbb'.

APPEND wa_mara TO tab.

wa_mara-matnr = 100.

wa_mara-mtart = space.

wa_mara-mbrsh = 'bbb'.

APPEND wa_mara TO tab.

wa_mara-matnr = 100.

wa_mara-mtart = 'aaa'.

wa_mara-mbrsh = space.

APPEND wa_mara TO tab.

wa_mara-matnr = space.

wa_mara-mtart = 'aaa'.

wa_mara-mbrsh = 'bbb'.

APPEND wa_mara TO tab.

LOOP AT tab ASSIGNING <wa_mara>.

   DO 3 TIMES.

     ASSIGN COMPONENT sy-index OF STRUCTURE <wa_mara> TO <fs_value>.

     IF <fs_value> IS NOT INITIAL.

       CONCATENATE <wa_mara>-status '1' INTO <wa_mara>-status.

     ELSE.

       CONCATENATE <wa_mara>-status '0' INTO <wa_mara>-status.

     ENDIF.

   ENDDO.

   WRITE : / <wa_mara>-matnr, <wa_mara>-mtart, <wa_mara>-mbrsh, <wa_mara>-status.

ENDLOOP.

In place of 3 you can give your number(i.e., number of fields in your structure) ..

Output

----------

Hope this will be best optimised logic ..

Thanks & Regards,

Arun.

[Former Member]

Thanks  Friends  ,

Hey  Arun  thanks for  detail  code  , I  have only one  doubt  in above code ,  it  is having  nested loop  ,

Loop   and then Do ...  

so in real  scenario   if there are  100 records in input file and each row have 38  fields

then  for   each row itself  38  times   Do  loop  .will get execute .  and  for  100  rows  its

38 * 100    ..   if am right here  then it  can cause performance issue right ?

or  please  correct me if am  mistaking some thing  ...

[matt]

For safety, I'd prefer to see:

     ASSIGN COMPONENT sy-index OF STRUCTURE <wa_mara> TO <fs_value>.

     IF sy-subrc IS INITIAL AND <fs_value> IS NOT INITIAL.

       CONCATENATE <wa_mara>-status '1' INTO <wa_mara>-status.

     ELSE.

       CONCATENATE <wa_mara>-status '0' INTO <wa_mara>-status.

     ENDIF.

The DO is fixed for a specific test table, so it doesn't change the algorithmic order. You have to check each field of the test table. The only other way you can do it is by

LOOP AT tab ASSIGNING <wa_mara>.

   IF <wa_mara>-matnr IS INITIAL.

       CONCATENATE <wa_mara>-status '1' INTO <wa_mara>-status.

     ELSE.

       CONCATENATE <wa_mara>-status '0' INTO <wa_mara>-status.

     ENDIF.

   IF <wa_mara>-mtart IS INITIAL.

       CONCATENATE <wa_mara>-status '1' INTO <wa_mara>-status.

     ELSE.

       CONCATENATE <wa_mara>-status '0' INTO <wa_mara>-status.

     ENDIF.

...etc.

   ENDDO.

   WRITE : / <wa_mara>-matnr, <wa_mara>-mtart, <wa_mara>-mbrsh, <wa_mara>-status.

ENDLOOP.

The difficulty with this is that you've got awful code (even with some kind of modularisation), that you have to edit the program if the structure of tab ever changes. The performance hit with the DO is slight - I've used this kind of construct many times and never needed to change it. Also, if you use DESCRIBE FIELD... COMPONENTS... you get the number of iterations required for your DO, which will mean your program will not need to be edited no matter how the driving table change in the background.

[Former Member]

Hi Kushi,

Your doubt regarding performance is correct ... But I think, 30 IF .. ENDIFs and 30 times DO will not have much difference .. May be its a nested loop but both does the same right! .. DO might have very small performance than IFs .. For example if you have 100 fields you can't imagine how many lines we should write for that ...

As MATTHEW suggested, it is better to keep SY-SUBRC check and populate the number for DO statement dynamically ..

I think you can close the thread, this will be the best optimised and good code for your requirement ...

Thanks & Regards,

Arun.

[Former Member]

Yup  Arun   Thanks  for your help .. am developing for all , give me little time  and will close this soon .

[Former Member]

Hi

This is how am developing but after updating the logic  when am updating internal table again

using   ( 
   MODIFY TABLE it_tab FROM <is_tab> TRANSPORTING PRIORITY.    )  its not happening  but its making internal table empty ..   inside form rule_logic ..

REPORT  ZTEST.

*SELECTION-SCREEN.
PARAMETERS       : p_ifname TYPE rlgrap-filename.



DATA : it_data TYPE TABLE OF alsmex_tabline INITIAL SIZE 0,
        is_data TYPE alsmex_tabline.

FIELD-SYMBOLS: <is_tab> TYPE ymklararul1,
                <fs_value> TYPE ANY.
DATA : it_tab TYPE TABLE OF ymklararul1  INITIAL SIZE 0,
        is_tab LIKE LINE OF it_tab.

PERFORM file_pickup.
PERFORM rule_logic.


MODIFY ymklararul1 FROM TABLE it_tab.


*&---------------------------------------------------------------------*
*&      Form  FILE_PICKUP
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM file_pickup .
* If Input file name is not initial.
   IF NOT p_ifname IS INITIAL.
* Upload EXCEL data into internal table

     p_ifname = 'c:\test.xlsx'.
     CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       EXPORTING
         filename                = p_ifname
         i_begin_col             = 1
         i_begin_row             = 2
         i_end_col               = 40
         i_end_row               = 100
       TABLES
         intern                  = it_data
       EXCEPTIONS
         inconsistent_parameters = 1
         upload_ole              = 2
         OTHERS                  = 3.
     IF sy-subrc <> 0.
       MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
     ENDIF.
   ENDIF.

* Append EXCEL Data into a internal table
   LOOP AT it_data INTO is_data.
     AT NEW row.
       CLEAR is_tab.
     ENDAT.
     IF is_data-col = '0001'.
       MOVE is_data-value TO is_tab-mandt.
     ENDIF.
     IF is_data-col = '0002'.
       MOVE is_data-value TO is_tab-tablename.
     ENDIF.
     IF is_data-col = '0003'.
       MOVE is_data-value TO is_tab-field.
     ENDIF.
     IF is_data-col = '0004'.
       MOVE is_data-value TO is_tab-mtart.
     ENDIF.
     IF is_data-col = '0005'.
       MOVE is_data-value TO is_tab-stdgbd.
     ENDIF.
     IF is_data-col = '0006'.
       MOVE is_data-value TO is_tab-vkorg.
     ENDIF.
     IF is_data-col = '0007'.
       MOVE is_data-value TO is_tab-plttyp.
     ENDIF.
     IF is_data-col = '0008'.
       MOVE is_data-value TO is_tab-pltso.
     ENDIF.
     IF is_data-col = '0009'.
       MOVE is_data-value TO is_tab-werks.
     ENDIF.
     IF is_data-col = '0010'.
       MOVE is_data-value TO is_tab-bwtar.
     ENDIF.
     IF is_data-col = '0011'.
       MOVE is_data-value TO is_tab-spart.
     ENDIF.
     IF is_data-col = '0012'.
       MOVE is_data-value TO is_tab-prdha.
     ENDIF.
     IF is_data-col = '0013'.
       MOVE is_data-value TO is_tab-mvgr1.
     ENDIF.
     IF is_data-col = '0014'.
       MOVE is_data-value TO is_tab-vrptyp.
     ENDIF.
     IF is_data-col = '0015'.
       MOVE is_data-value TO is_tab-vrpstp.
     ENDIF.
     IF is_data-col = '0016'.
       MOVE is_data-value TO is_tab-yindx.
     ENDIF.
     IF is_data-col = '0017'.
       MOVE is_data-value TO is_tab-mindensity.
     ENDIF.
     IF is_data-col = '0018'.
       MOVE is_data-value TO is_tab-maxdensity.
     ENDIF.
     IF is_data-col = '0019'.
       MOVE is_data-value TO is_tab-matkl.
     ENDIF.
     IF is_data-col = '0020'.
       MOVE is_data-value TO is_tab-oitaxgrp.
     ENDIF.
     IF is_data-col = '0021'.
       MOVE is_data-value TO is_tab-oihmtxgr.
     ENDIF.
     IF is_data-col = '0022'.
       MOVE is_data-value TO is_tab-land1.
     ENDIF.
     IF is_data-col = '0023'.
       MOVE is_data-value TO is_tab-phlinebusi.
     ENDIF.
     IF is_data-col = '0024'.
       MOVE is_data-value TO is_tab-phsyntind.
     ENDIF.
     IF is_data-col = '0025'.
       MOVE is_data-value TO is_tab-yyvloctype.
     ENDIF.
     IF is_data-col = '0026'.
       MOVE is_data-value TO is_tab-priority.
     ENDIF.
     IF is_data-col = '0027'.
       MOVE is_data-value TO is_tab-value.
     ENDIF.
     IF is_data-col = '0028'.
       MOVE is_data-value TO is_tab-del.
     ENDIF.
     IF is_data-col = '0029'.
       MOVE is_data-value TO is_tab-rcdat.
     ENDIF.
     IF is_data-col = '0030'.
       MOVE is_data-value TO is_tab-raudit.
     ENDIF.
     AT END OF row.
       APPEND is_tab TO it_tab.
     ENDAT.
     CLEAR : is_data.
   ENDLOOP.
ENDFORM.                    " FILE_PICKUP
*&---------------------------------------------------------------------*
*&      Form  RULE_LOGIC
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM rule_logic .

   DATA : v_con(20) TYPE c .

   CLEAR : v_con,is_tab.
   LOOP AT it_tab ASSIGNING <is_tab>.

     DO 30 TIMES.
       ASSIGN COMPONENT sy-index OF STRUCTURE <is_tab> TO <fs_value>.
       IF sy-subrc IS INITIAL AND <fs_value> IS NOT INITIAL  .
         CONCATENATE <is_tab>-priority '1' INTO <is_tab>-priority.
       ELSEIF <fs_value> IS INITIAL OR <fs_value> EQ '*'.
         CONCATENATE <is_tab>-priority '0' INTO <is_tab>-priority.
       ENDIF.
     ENDDO.

   MODIFY TABLE it_tab FROM <is_tab> TRANSPORTING PRIORITY.
   CLEAR : v_con,<is_tab>.
   ENDLOOP.
*assign component sy-index of structure wa to <fs>
*ASSIGN COMPONENT sy-index OF STRUCTURE is_tab to <FS>.
* if sy-subrc ne 0.
*      exit.
*   endif.
*     if <fs> is initial or <fs> co '0'.
*      concatenate is_tab-zrule  '0'  INTO is_tab-zrule.
*       else .
*      concatenate is_tab-zrule  '1'  INTO is_tab-zrule.
*    endif.

ENDFORM.                    " RULE_LOGIC

[matt]

You haven't understood how LOOP AT ASSIGNING <is_tab> works, have you? <is_tab> contains the record of the table directly. So when you modify a field in it, you're modifying that field in the table directly. No need for MODIFY.

To be honest, and I hope you take this the right way, you'd have figured this out (or should have) if you had tried running your program in debug first, before posting here with a "doesn't work". These skills will really help you in your career.

And why have you got v_con defined when you don't use it? This looks like a bit like programming by guesswork

[Pavan_Golesar]

ME Too.. ::-)

[Former Member]

Thanks  Matthew  for your  fruitful  reply   :-),

Now my problem  is  I have to start calculation for my logic of  1 / 0   from third field  which name is  FIELD  here and not from the first field,

I have to start calculation from Field on 3rd number in above code   till the field yyvloctype  which is on 25th number   and update all calculation on 26th priority  field

so how can I  control that immediate after  loop and before Do . ? because in loop it bring all  fields in one row .

LOOP AT it_tab ASSIGNING <is_tab>.

????????????

     DO 30 TIMES.
       ASSIGN COMPONENT sy-index OF STRUCTURE <is_tab> TO <fs_value>.
       IF sy-subrc IS INITIAL AND <fs_value> IS NOT INITIAL  .
         CONCATENATE <is_tab>-priority '1' INTO <is_tab>-priority.
       ELSEIF <fs_value> IS INITIAL OR <fs_value> EQ '*'.
         CONCATENATE <is_tab>-priority '0' INTO <is_tab>-priority.
       ENDIF.
     ENDDO.

[Former Member]

Friends    I  got expected output from my below logic

LOOP AT it_tab ASSIGNING <is_tab>.


     DO 26 TIMES.
       if sy-index GE 3.
       ASSIGN COMPONENT sy-index OF STRUCTURE <is_tab> TO <fs_value>.
       IF sy-subrc IS INITIAL AND <fs_value> IS NOT INITIAL AND <fs_value> NE '*' .
         CONCATENATE <is_tab>-priority '1' INTO <is_tab>-priority.
       ELSEIF <fs_value> IS INITIAL OR <fs_value> EQ '*'.
         CONCATENATE <is_tab>-priority '0' INTO <is_tab>-priority.
       ENDIF.
       ENDIF.
     ENDDO.

just wondering if this is the best or correct way for long run   🙂

[Former Member]

Hi Kushi,

Its too simple ..

LOOP AT it_tab ASSIGNING <is_tab>.

????????????     " This is not the correct place ..

     DO 30 TIMES.

          IF SY-INDEX GE 3 AND SY-INDEX LE 25.

          " Now, below code will be triggered only from 3 - 25th filed ...

                 ASSIGN COMPONENT sy-index OF STRUCTURE <is_tab> TO <fs_value>.
                 IF sy-subrc IS INITIAL AND <fs_value> IS NOT INITIAL  .
                 CONCATENATE <is_tab>-priority '1' INTO <is_tab>-priority.
                 ELSEIF <fs_value> IS INITIAL OR <fs_value> EQ '*'.
                 CONCATENATE <is_tab>-priority '0' INTO <is_tab>-priority.
                 ENDIF.

          ENDIF.              

     ENDDO.

Hope this will solve your problem ...

Thanks & Regards,

Arun.

[Former Member]

Hello Friends

I  came to know this table update and code for logic  can be handle  at  Table Maintainc Generator  level also   inside some Event ..

Its like if user after filling data via copy paste  from sm30   and then when press Enter  ,then that

event will  trigger and code will work and automatic that field get updated .

any body have worked on such thing before ?

[Former Member]

Yes - and if you'll search the forum, you'll see how.

Rob

[Pavan_Golesar]

This message was moderated.