topic Re: How to split SELECT to slices (parallel processing) in Application Development and Automation Discussions

How to split SELECT to slices (parallel processing)

Former Member — Tue, 04 Aug 2009 10:38:46 GMT

Hi experts,

I've have following select statements and I want to split them to some slices so I can apply parallel processing correctly. I should do that because I want follow[ this logic|http://help.sap.com/saphelp_nw04/helpdata/en/c4/3a7f1f505211d189550000e829fbbd/content.htm].

I followed this [guide|http://help.sap.com/saphelp_nw04/helpdata/en/fa/096e92543b11d1898e0000e8322d00/frameset.htm].

But as I said I don't know how to build these packages.

My idea about package size

package size = Lines of itab in SELECT / available wp.

But this idea is incorrect. Because first I must do this Select without parallel processing to get the lines of my itab.

So,

how can i build packages and how can I set their size dynamically?

Unfortunately, SDN search or Help.sap.com do not have a solution.

Please do not give me hundred lines of code. Try to explain me.

SELECT *
    FROM ltak
    INTO TABLE lt_ltak_all
    WHERE lgnum IN so_lgnum
      AND tanum IN so_tanum
      AND bdatu IN so_bdatu
      AND kquit = 'X'  
      AND betyp = 'L'.

  CHECK sy-subrc = 0.

    SELECT *
    FROM ltap
    INTO TABLE lt_ltap_all
    FOR ALL ENTRIES IN lt_ltak_all
    WHERE lgnum = lt_ltak_all-lgnum
      AND tanum = lt_ltak_all-tanum
      AND nltyp = '916'.

Regards,

Zaya

Re: How to split SELECT to slices (parallel processing)

Former Member — Tue, 04 Aug 2009 10:50:29 GMT

this logic below.

SELECT *

INTO CORRESPONDING FIELDS OF TABLE lt_ltak_ltap

FROM ltak INNER JOIN ltap

ON ltap_{lgnum eq ltak}lgnum AND

ltap_{tanum eq ltak}tanum

WHERE ltak~lgnum IN so_lgnum AND

ltak~tanum IN so_tanum AND

ltak~bdatu IN so_bdatu AND

ltap~nltyp EQ '916'.

I hope to help you.

Re: How to split SELECT to slices (parallel processing)

Former Member — Tue, 04 Aug 2009 10:57:38 GMT

Please use.

SELECT * FROM ltak

INTO TABLE lt_ltak_all

INNER JOIN ltap on ltak_{LGNUM = ltap}LGNUM

ltak_{TANUM = ltap}TANUM

WHERE ltak~lgnum IN so_lgnum

AND ltak~tanum IN so_tanum

AND ltak~bdatu IN so_bdatu

AND ltak~kquit = 'X'

AND ltak~betyp = 'L'

AND nltyp = '916'.

Regards,

Rajesh

Re: How to split SELECT to slices (parallel processing)

Former Member — Tue, 04 Aug 2009 11:06:00 GMT

HI Akkadech,

firstly, thank u for ur help!

Is it OK to insert ur select into an aRFC and insert below code to an report, so this select would be parallel processed?

Report code:

CALL FUNCTION 'Z_GET_PARALLEL'
          STARTING NEW TASK taskname
          DESTINATION IN GROUP mygroup
          CALLING me->on_return ON END OF TASK
          TABLES
            lt_ltak   = gt_ltak
            lt_ltap   = gt_ltap
 

WAIT UNTIL rcv_jobs >= snd_jobs.

method on_return.
...
endmethod.

I don't think so because if I execute this report it starts one new task and it calls this aRFC. This aRFC will process it sequentially not parallel.

Any suggestion?

Best Regards,

Zaya

Re: How to split SELECT to slices (parallel processing)

Former Member — Tue, 04 Aug 2009 11:33:04 GMT

you can try this logic agin.

DATA: LT_LTAK LIKE STANDARD TABLE OF LTAK

WITH HEADER LINE ,

LT_LTAP LIKE STANDARD TABLE OF LTAP

WITH HEADER LINE ,

LT_LTAK_LTAP LIKE STANDARD TABLE OF 'LTAK & LTAP,

SELECT *

INTO CORRESPONDING FIELDS OF TABLE LT_LTAK_LTAP

FROM LTAK INNER JOIN LTAP

ON LTAP_{LGNUM EQ LTAK}LGNUM AND

LTAP_{TANUM EQ LTAK}TANUM

WHERE LTAK~LGNUM IN SO_LGNUM AND

LTAK~TANUM IN SO_TANUM AND

LTAK~BDATU IN SO_BDATU AND

LTAK~kquit EQ 'X' AND

LTAK~betyp EQ 'L' AND

LTAP~NLTYP EQ '916'.

LOOP AT LT_LTAK_LTAP.

MOVE-CORRESPONDING LT_LTAK_LTAP TO LT_LTAK.

APPEND LT_LTAK.

MOVE-CORRESPONDING LT_LTAK_LTAP TO LT_LTAK.

APPEND LT_LTAP.

ENDLOOP.

I think this logic is saving performance for you.

Re: How to split SELECT to slices (parallel processing)

Former Member — Tue, 04 Aug 2009 12:05:37 GMT

Yes, ur logic is right. But I need to know how should I implement this for parallel processing?

Re: How to split SELECT to slices (parallel processing)

RaymondGiuseppi — Tue, 04 Aug 2009 12:23:18 GMT

First read the first table by package

  OPEN CURSOR WITH HOLD s_cursor FOR
    SELECT *
      FROM ltak
      INTO TABLE lt_ltak_all
      WHERE lgnum IN so_lgnum
        AND tanum IN so_tanum
        AND bdatu IN so_bdatu
        AND kquit = 'X'  
        AND betyp = 'L'.
  DO. " read by package
    FETCH NEXT CURSOR s_cursor
               INTO TABLE itab1
               PACKAGE SIZE nnnnn.
  IF SY-SUBRC NE 0. EXIT. ENDIF.
" here parallelize
    ADD 1 TO nbr_process.
    CALL FUNCTION 'XXXXX'
       STARTING NEW TASK taskname DESTINATION IN GROUP DEFAULT
       PERFORMING return_info ON END OF TASK
       TABLES
            first_table = itab1
       ...

You must use some limitation in the number of tasks running, so count number of tasks submitted, and decrement in the FORM performed ON END OF TASK. When the maximum number is reached you WAIT til a "slot" is free.

Regards,

Raymond

Re: How to split SELECT to slices (parallel processing)

Former Member — Tue, 04 Aug 2009 12:54:24 GMT

Hi Raymond,

ure answer is awesome! It helped me a lot to solve my problem!!

So, if I want to split my SELECT into Packages with linesize of 8000 I need to fill in this in PACKAGE SIZE?

Where are these packages being saved?

All In itab1? No, I think, itab1 is the first package with 8000.

So, do I need another OPEN CURSOR WITH HOLD s_cursor FOR .. and FETCH NEXT CURSOR s_cursor INTO TABLE itab1 PACKAGE SIZE 8000 for getting and processing my next package?

Best Regards,

Tuncay

Re: How to split SELECT to slices (parallel processing)

RaymondGiuseppi — Tue, 04 Aug 2009 13:06:48 GMT

In each iteration in the DO/ENDDO you FETCH another package of say 8000 records in itab1, then you pass this itab1 to a function module in a new task, in the returning FORM executed at end of the FM, you get some internal table returned, which you add to the internal table of the main program.

OPEN CURSOR.
DO.
  FETCH a package into <work itab>
  Detect end of data, if yes exit DO/ENDDO
  WAIT until number of number of running tasks is correct
  CALL FUNCTION <ztask> STARTING NEW TASK <id> PERFORMING <return> ON END OF TASK TABLES <work itab>.
  Add 1 to number of running tasks
ENDDO.
WAIT until number of running task is zero.
end of job

FORM <return> USIND <id>
  RECEIVE RESULTS FROM FUNCTION TABLES local <returned itab>
  insert lines of <returned itab> to main internal table
  subtract 1 to number of running tasks
ENDFORM

FUNCTION <ztask>
  Execute other selection for all entries of <work itab> received
  fill <returned itab>
ENDFUNCTION

Regards,

Raymond

Re: How to split SELECT to slices (parallel processing)

Former Member — Tue, 04 Aug 2009 13:15:00 GMT

Dear Raymond,

I appreciate ur professional approach for helping me!

Ur answers are very precise and easy to understand.

Now I have my solution!

Full points for u!

Thank u again!

Best Regards,

Zaya