topic Re: performance for huge table in Application Development and Automation Discussions

performance for huge table

Former Member — Thu, 21 May 2015 18:09:25 GMT

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 .

Re: performance for huge table

Former Member — Thu, 21 May 2015 19:58:11 GMT

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

Rob

Re: performance for huge table

Former Member — Wed, 27 May 2015 18:37:26 GMT

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.

Re: performance for huge table

Former Member — Wed, 27 May 2015 18:43:18 GMT

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

Rob

Re: performance for huge table

Former Member — Thu, 28 May 2015 06:51:39 GMT

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

Re: performance for huge table

Former Member — Thu, 28 May 2015 12:51:12 GMT


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

Re: performance for huge table

Former Member — Thu, 28 May 2015 14:16:25 GMT

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.

Re: performance for huge table

Former Member — Thu, 28 May 2015 17:36:51 GMT

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 ...

Re: performance for huge table

matt — Fri, 29 May 2015 04:27:24 GMT

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.

Re: performance for huge table

Former Member — Fri, 29 May 2015 07:26:29 GMT

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.

Re: performance for huge table

Former Member — Fri, 29 May 2015 16:33:57 GMT

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

Re: performance for huge table

Former Member — Sat, 30 May 2015 19:39:36 GMT

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

Re: performance for huge table

matt — Sun, 31 May 2015 05:07:02 GMT

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

Re: performance for huge table

Pavan_Golesar — Sun, 31 May 2015 10:31:19 GMT

This message was moderated.

Re: performance for huge table

Pavan_Golesar — Sun, 31 May 2015 10:31:43 GMT

ME Too.. ::-)

Re: performance for huge table

Former Member — Mon, 01 Jun 2015 10:36:23 GMT

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.

Re: performance for huge table

Former Member — Mon, 01 Jun 2015 13:41:42 GMT

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 🙂

Re: performance for huge table

Former Member — Mon, 01 Jun 2015 13:45:21 GMT

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.

Re: performance for huge table

Former Member — Thu, 11 Jun 2015 17:54:49 GMT

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 ?

Re: performance for huge table

Former Member — Fri, 12 Jun 2015 16:41:49 GMT

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

Rob