Background job running for 16 hours

[Former Member]

hi,

I have written an abap code and i run that code as background job. what this code does is it takes data from MARD table and inserts into a ztable. There is also a table MSKA from where i get a field into internal table of MARD.

The background job to run this program was still active even after 16 hours. The data in MARD table is 4.5 million and the select statement will try to insert 4.5 million record in internal table of MARD.

I don't know why the program run is not completing in background. Is there something wrong i am doing ?

There is also a select statement as below made n MSKA table which has 2.2 lakh records.

1. Select statement on MSKA table - 2.2 lakh records

SELECT MATNR WERKS LGORT KALAB INTO ITMSKA FROM MSKA WHERE MATNR IN MATERIAL AND

     WERKS IN  PLANT AND LGORT IN SLOC .

     COLLECT ITMSKA.

    ENDSELECT.

2. Select statement on MARD table - 4.5 million records

SELECT MATNR WERKS LGORT LABST UMLME INSME EINME SPEME RETME INTO TABLE ITMARD FROM MARD.

Please help.

[former_member400695]

Hi Abood,

Prior to use a SELECT query to retrieve data from databse, create a secondary index with fields MATNR, WERKS and LGORT for database table MSKA.

The first SELECT query on the database table MSKA can be replaced as below

SELECT MATNR WERKS LGORT SUM( KALAB )

INTO TABLE ITMSKA

FROM MSKA

GROUP BY MATNR WERKS LGORT

WHERE MATNR IN MATERIAL AND WERKS IN PLANT AND LGORT IN SLOC.

Using the Arithmetic operation on the field KALAB along with the GROUP BY addition in the SELECT query will do the job of COLLECT ITMSKA statement. This query will have significant effect on data retrieval and data formatting.

Here is the alternative for the second SELECT query, it should be replaced as below

SORT ITMSKA.

DELETE ADJACENT DUPLICATES FROM ITMSKA COMPARING ALL FIELDS.

IF NOT ITMSKA IS INITIAL.

SELECT MATNR WERKS LGORT LABST UMLME INSME EINME SPEME RETME

  INTO TABLE ITMARD

  FROM MARD

FOR ALL ENTRIES IN ITMSKA

WHERE MATNR = ITMSKA-MATNR AND WERKS = ITMSKA-WERKS AND LGORT = ITTMSKA-  LGORT.

ENDIF.

Note: Sorting, deleting duplicates and internal table empty check are the prerequisites for using SELECT FOR ALL ENTRIES.

Thank you,

Karteek.K

[Former Member]

Hi Abood,

Try to make some mandatory filed on selection screen. Try to avoid SELECT......ENDSELECT.

Check futher if you are using anywhere for all entries and Internal table intial check not done.

Regards,

Pravin

[Former Member]

Thanks everyone for your help. I also have a loop statement like below

is there a way i can write a better code by using field symbols?Thanks for your help.

LOOP AT ITMSKA.

    LOOP AT ITMARD WHERE WERKS = ITMSKA-WERKS AND MATNR = ITMSKA-MATNR.

    ITMARD-LGORT = ITMSKA-LGORT.

    ITMARD-LABST = 0.

    ITMARD-INSME = 0.

    ITMARD-SPEME = 0.

    ITMARD-RETME = 0.

    ITMARD-KALAB = 0.

    COLLECT ITMARD.

    ENDLOOP.

  ENDLOOP.

[former_member391265]

Hi Use parallel cursor technique to improve the performance..you will get an example in saptechnical.

[former_member196213]

Hi,

     As you said the no of records which is 4.5 million and 2.2 lacks records. So the nested loop will produce 2.2 lack * 4.5 million records. This is a huge number of iteration. Also if there are so many records then select...endselect is not recommended as it will hit the database repeatedly.

You can use below tcode to analyze further.

1. ST05(SQL Trace)

Identify which statement is taking how much time. Also check the difference between two adjacent statements wherever the time is high that means there is some ABAP processing taking place.

After this if you are confirmed that you cannot do much with SQL statements, then target ABAP Processings (loops, read).

2. SAT(ABAP runtime analysis)

identify which method FM is taking longer time

Based on the above problems the you can try out these solution

Sometimes we come up with assumption that because we had kept a where clause so the number of iteration will be reduced. Which appears true to the developer(Not to ABAP processor). Because inorder to apply the condition in the where clause the ABAP processor compares all the records as per the condition(displays only which satisfy criteria- Even to developer)

You can use parallel cursor technique.

Using this your code will use indexes to loop instead of where clause. Here is the link where you can get details ABAP Code for Parallel Cursor - Loop Processing - Code Gallery - SCN Wiki

Also you can...

1. Make sure Itabs are sorted correctly

2. Try using read itab into watab with key .... using binary search()

If you still need some improvement go for ABAP Parallel Processing. This requires strong hardware as it will occupy multiple processor at a time. This is will fasten your program but will consume multiple resources. It will require basis configrations as well.(<-Your 2nd last option)

Last Option(HANA cloud or BI)

Regards,

Vishal Kumar Yadav

Happy ABAPing!!

[thanga_prakash]

Hello Abood,

Obviously it will take more time as the records in table are huge, it goes through huge number of iterations.

1) Use some where conditions to select the data.

2) Create a secondary index for those fields used in where query.

3) Use inner joins or For all entrie when fetching data from two relevant tables.

Regards,

TP