Skip to Content
author's profile photo Former Member
Former Member

Program running too long

Requirement : Get all entries from Custom tabale and from LQUA and compare both tables and write rest of the entries from each table to excel files.There are 200,000 records in each table ZPHY_INV_HIS and LQUA. Please see below code, I couldn;t do more interms of performence and Program is taking almost 2 hours to give output. Please help me and let me know that how can i increase performence of this program.

SELECT * FROM ZPHY_INV_HIS INTO TABLE IT_PHY_INV_HIS.

SORT IT_PHY_INV_HIS BY STO_UNIT.

*Get all entries from LQUA table

SELECT LGNUM MATNR LGTYP LGPLA MEINS GESME LENUM LGORT

FROM LQUA INTO CORRESPONDING FIELDS OF TABLE IT_LQUA

WHERE LGNUM = I_LGNUM.

IF NOT IT_LQUA IS INITIAL.

SORT IT_LQUA BY LENUM ASCENDING.

LOOP AT IT_PHY_INV_HIS INTO WA_PHY_INV_HIS.

IF NOT WA_PHY_INV_HIS-STO_UNIT IS INITIAL.

READ TABLE IT_LQUA WITH KEY

LENUM = WA_PHY_INV_HIS-STO_UNIT BINARY SEARCH

INTO WA_LQUA

TRANSPORTING LGNUM MATNR LGTYP LGPLA GESME LENUM LGORT.

ENDLOOP.

I am not sure if this is something to do with READ statement because when i run in debugging mode in LOOP statment itself program is taking so much time to come out and for Select statements are fine as those tables have secondary indexs in it.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 09, 2011 at 09:40 AM

    Hi,

    Can you please tell us how much time (roughly) is taken for each select statement and for looping.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Rob thanks for the mail, I did runtime anaysis and i even put Messages so that i can check those when i run program in background then only i figured Loop has the problem. well I have already tried taking out DELETE statement and I didn't see any performence improvement. I even tried TRANSPORTING NO FIELDS Option and no change in performence

      Do you think this COncatenating is problem. I have to do this because in excel i would like to see value starting with ' so that formating is not problem

      CONCATENATE '''' WA_LQUA-LGPLA INTO WA_LQUA-LGPLA.

  • Posted on Dec 09, 2011 at 12:51 AM

    What do you mean by rest of the entries ..???

    Do you want all the 200,000 records to be in output or need to filter some of those, if want to filter. Please clearly explain that.

    You can use Inner join or For all entries to filter data by comparing two tables, (if database table use inner join, internal tables use for all entries)

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 09, 2011 at 01:26 AM

    Thanks for reply playsuji, Below are the 2 custom tables involved

    1.Custom table (ZPHY_INV_HIS) - 200,000 entries

    2.Custom table ZLQUA*(Copy of LQUA table including data) - 200,000 entries

    Requirement: Compare above tables each other and need to write rest of entries into 2 excel file.

    Means lets say take one Handling Unit from Custom table and look into ZLQUA table if its not there write to Excel file, vice versa with other table too.

    Below is the process

    1. Get all the entries from Custom table (ZPHY_INV_HIS) into Internal table IT_PHY_INV_HIS

    2.Get all the entries from ZLQUA(Copy of LQUA table including data) into internal table IT_LQUA

    3. Loop at Custom table (ZPHY_INV_HIS) and compare ZPHY_INV_HIS-STO_UNIT with ZLQUA-LENUM

    4. If you find entry delete that entry from LQUA table (IT_LQUA).

    5. If you don't find entry move to Internal table (IT_BAD_RECORDS)

    6. for both above internal tables get all the prices from MBEW table (Extra details)

    7. Write Both Files into Excel.

    I am sure I am doing wrong with READ statement. Please help me in achieving performence.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 09, 2011 at 10:45 AM

    1. First is select only those fields which are needed along with key fields.

    2. Check if select is taking a long time if yes in case no key fields are there then we create secondary index or use concept of package size.

    3. Read with binary search.

    Nabheet

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.