Program running too long

[Former Member]

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.

[surajarafath]

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)

[Former Member]

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.

[former_member198275]

Hi,

Try this , and let me know.

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.

delete IT_PHY_INV_HIS WHERE STO_UNIT is INITIAL.

  SORT IT_LQUA BY LENUM ASCENDING.

  LOOP AT IT_PHY_INV_HIS INTO WA_PHY_INV_HIS.
 
      read TABLE it_lqua INTO wa_lqwa with KEY LENUM = WA_PHY_INV_HIS-STO_UNIT BINARY SEARCH.
 
    ENDLOOP.

[Former Member]

Hi Kaushik,

I see below the difference between my code and your code.

delete IT_PHY_INV_HIS WHERE STO_UNIT is INITIAL.

Above statement is not going to make any change in number of entries as i know that custom table data don't have blanks or Duplicates.

[Former Member]

Use field symbols for better performance and use Binary search in read statement

[Former Member]

Hi,

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

[Former Member]

Hi,

Select statements are not taking too much time or only taking less than 2 minutes, Both the tables have Secondary indexes. So SELECT Statements are not the problem.

the problem is where LOOPING happens and I am using BINARY SEARCH for READ Command in LOOP Statement.

Select statements taking less than 2 minutes.

LOOP taking almost hour.

[Former Member]

There is nothing wrong with the code you have posted. I suspect the problem is elsewhere. Have you posted all the code being executed in the LOOP. Is this code being called from within a nested SELECT or LOOP?

Rob

[Former Member]

Rob,

here is the code in LOOP. NO below code is not getting called in any of other LOOPS or Nested SELECTES

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.

IF SY-SUBRC = 0.

WA_RECORD_GOOD-SER_NO = WA_PHY_INV_HIS-SER_NO.

WA_RECORD_GOOD-LGNUM = WA_LQUA-LGNUM.

WA_RECORD_GOOD-STO_UNIT = WA_PHY_INV_HIS-STO_UNIT.

WA_RECORD_GOOD-CUST_MATNR = WA_PHY_INV_HIS-MATNR.

WA_RECORD_GOOD-LQUA_MATNR = WA_LQUA-MATNR.

WA_RECORD_GOOD-UNIT_OF_MEASURE = WA_PHY_INV_HIS-UNIT_OF_MEASURE.

WA_RECORD_GOOD-CUST_STO_BIN = WA_PHY_INV_HIS-STO_BIN.

WA_RECORD_GOOD-CUST_STO_TYPE = WA_PHY_INV_HIS-STO_TYPE.

WA_RECORD_GOOD-CUST_STO_LOC = WA_PHY_INV_HIS-STO_LOC.

WA_RECORD_GOOD-CUST_QUANTITY = WA_PHY_INV_HIS-QUANTITY.

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

WA_RECORD_GOOD-LQUA_STO_BIN = WA_LQUA-LGPLA.

WA_RECORD_GOOD-LQUA_STO_TYPE = WA_LQUA-LGTYP.

WA_RECORD_GOOD-LQUA_STO_LOC = WA_LQUA-LGORT.

WA_RECORD_GOOD-LQUA_QUANTITY = WA_LQUA-GESME.

WA_RECORD_GOOD-QUANTITY_DIFF = WA_LQUA-GESME - WA_PHY_INV_HIS-QUANTITY.

WA_RECORD_GOOD-LICE_PLATE = WA_PHY_INV_HIS-LICE_PLATE.

WA_RECORD_GOOD-SCANNER_ID = WA_PHY_INV_HIS-SCANNER_ID.

WA_RECORD_GOOD-UPLOAD_DATE = WA_PHY_INV_HIS-UPLOAD_DATE.

WA_RECORD_GOOD-USER_NAME = WA_PHY_INV_HIS-USER_NAME.

APPEND WA_RECORD_GOOD TO IT_RECORD_GOOD.

CLEAR WA_RECORD_GOOD.

*Delete compared line from Internal table

DELETE IT_LQUA WHERE LENUM = WA_LQUA-LENUM.

ELSE.

WA_RECORD_BAD-SER_NO = WA_PHY_INV_HIS-SER_NO.

WA_RECORD_BAD-STO_UNIT = WA_PHY_INV_HIS-STO_UNIT.

WA_RECORD_BAD-MATNR = WA_PHY_INV_HIS-MATNR.

WA_RECORD_BAD-STO_BIN = WA_PHY_INV_HIS-STO_BIN.

WA_RECORD_BAD-QUANTITY = WA_PHY_INV_HIS-QUANTITY.

WA_RECORD_BAD-STO_TYPE = WA_PHY_INV_HIS-STO_TYPE.

WA_RECORD_BAD-LICE_PLATE = WA_PHY_INV_HIS-LICE_PLATE.

WA_RECORD_BAD-UNIT_OF_MEASURE = WA_PHY_INV_HIS-UNIT_OF_MEASURE.

WA_RECORD_BAD-UPLOAD_DATE = WA_PHY_INV_HIS-UPLOAD_DATE.

WA_RECORD_BAD-USER_NAME = WA_PHY_INV_HIS-USER_NAME.

WA_RECORD_BAD-SCANNER_ID = WA_PHY_INV_HIS-SCANNER_ID.

WA_RECORD_BAD-STO_LOC = WA_PHY_INV_HIS-STO_LOC.

APPEND WA_RECORD_BAD TO IT_RECORD_BAD.

CLEAR WA_RECORD_BAD.

ENDIF.

ENDIF.

ENDLOOP.

[Former Member]

Basically, you have:

LOOP AT it_phy_inv_his INTO wa_phy_inv_his.
  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.
  IF sy-subrc = 0.
*Delete compared line from Internal table
    DELETE it_lqua WHERE lenum = wa_lqua-lenum.      " <=====
  ENDIF.
ENDLOOP.

The DELETE WHERE inside the LOOP is your problem. You should either just delete this record or keep a list of LENUM that need to be deleted from the internal table and do that in one shot after the LOOP.

You posted essentially the same question in the Performance and Tuning Forum yesterday. I moved it to the test and playground forum because you hadn't done a runtime analysis to help you find the problem. If you had done that, you would have had your answer yesterday.

Rob

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

[nabheetscn]

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