topic Re: Need help regarding processing large data with multiple approach.cursor in Application Development and Automation Discussions

Need help regarding processing large data with multiple approach.cursor

Former Member — Mon, 13 Jul 2015 07:11:30 GMT

Hi Experts,

We need you help regarding one of the approach for more performance effective as it involves multiple data.

We have the following requirement,

We need to retrieve large amount of data form four consecutive table and then processing needs to be done by looping to the initial table and

number of processing of data will be around 4/5 milllion.

Now we are taking the approach to open cursor for retrieving data from the tables, as i am new in using this so bit confused whoch approach will be more performance effective,

First approach is we will open the cursor and retrieve data from rest of the three table also and append those and the close the cursor, and the loop processing will be done without cursor, basically if the tables are A, B Aand C then will do the following ..

OPEN CURSOR WITH HOLD s_cursor FOR
SELECT

----

----
FROM A

WHERE conditions

DO.
FETCH NEXT CURSOR s_cursor appending TABLE lt_A PACKAGE SIZE 60000.
IF sy-subrc <> 0.
EXIT.
ENDIF.

SELECT

----

----
FROM B

for all entries in A ----- ? I guess we can use for all entries..
WHERE conditions

DO.
FETCH NEXT CURSOR s_cursor appending TABLE lt_B PACKAGE SIZE 60000.
IF sy-subrc <> 0.
EXIT.
ENDIF.

and then similar retrieval for 3rd table and then

ENDDO.

close cursor.

and then will process through lt_A and perform actions and all those.

second approach is we will open the cursor and retrieve data from rest of the three table by package size but here our processing for main table and all those will be done within the cursor,means we will do the processing as package size 60000 and same way the whole will be done..please

find them below..

OPEN CURSOR WITH HOLD s_cursor FOR
SELECT

----

----
FROM A

WHERE conditions

DO.
FETCH NEXT CURSOR s_cursor into TABLE lt_A PACKAGE SIZE 60000.
IF sy-subrc <> 0.
EXIT.
ENDIF.

SELECT

----

----
FROM B for all entries in A ----- ? I guess we can use for all entries..
WHERE conditions

DO.
FETCH NEXT CURSOR s_cursor into TABLE lt_B PACKAGE SIZE 60000.
IF sy-subrc <> 0.
EXIT.
ENDIF.

and then similar retrieval for 3rd table and then.

and then will process through lt_A and perform actions and all those foe each bulk of 60000 entries..

loop at lt_a into..

functions...

ENDLOOP.

ENDDO.

close cursor.

Please help us regarding the approaches, which should be followed ? it will really help a lot. I think we should follow second but as i am new to this so need your advice. Also, package size 60000 is ok or it should be reduced ?

Thanks a lot in advance.

Re: Need help regarding processing large data with multiple approach.cursor

deepan_v_s — Mon, 13 Jul 2015 07:22:33 GMT

Hello Rinki,

If processing needs to be done based on initial table, then it will be better to use Join in the OPen-Cursor Select.

If processing needs to be done based on records from unique fields of all four tables then you can use all four tables in the Join.

And please reduce the package size.. As far as we are using , we would consider 10000 as the size. Since if the number of columns are more, then we will get dump due to spacing issue.

Regards,

Deepan Swaminathan

Re: Need help regarding processing large data with multiple approach.cursor

Former Member — Mon, 13 Jul 2015 11:34:21 GMT

Hi Rinki,

Did not understand your second 'FECTCH NEXT', need this ?

Generally, we do like this:

OPEN CURSOR s_cursor FOR (ON HOLD is not necessary, no explicit db commit here)
SELECT *

FROM A

WHERE conditions.

DO.
FETCH NEXT CURSOR s_cursor into TABLE itab_a PACKAGE SIZE p_size(from selection screen,default 10000, depend on your data size. If in this time of 'FETCH NEXT', the summary size of itab_a, itab_b and itab_c over the abap memory size, it will occurs memory dump, then you need reduce this size number).
IF sy-subrc <> 0.
EXIT.
ENDIF.

SELECT * INTO itab_b

FROM B for all entries in A
WHERE conditions

SELECT * INTO itab_c

FROM C for all entries in A
WHERE conditions.

LOOP itab_A...

functions.

ENDLOOP.

ENDDO.

regards,
Archer