performance issue in do statement is there any other options to write the do statement

[former_member831369]

aftab-sir.txt

DO.

FETCH NEXT CURSOR gv_cursor

APPENDING TABLE lt_mseg PACKAGE SIZE 25000.

IF sy-subrc <> 0.

EXIT.

ENDIF.

ENDDO.

CLOSE CURSOR gv_cursor.

[former_member27]

Welcome to the SAP Community. Thank you for visiting us to get answers to your questions.

Since you're asking a question here for the first time, I'd like to offer some friendly advice on how to get the most out of your community membership and experience.

First, please see https://community.sap.com/resources/questions-and-answers, as this resource page provides tips for preparing questions that draw responses from our members.

Second, feel free to take our Q&A tutorial at https://developers.sap.com/tutorials/community-qa.html, as that will help you when submitting questions to the community.

I also recommend that you include a profile picture. By personalizing your profile, you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html.

Now for some specific suggestions on how you might improve your question:

* Outline what steps you took to find answers (and why they weren't helpful) -- so members don't make suggestions that you've already tried.

* Share screenshots of what you've seen/done (if possible), as images always helps our members better understand your problem.

* Make sure you've applied the appropriate tags -- because if you don't apply the correct tags, the right experts won't see your question to answer it.

* Use the "insert code" feature when sharing your code, so members have an easier time reading. Your code can be added with the "insert code" to make it easier to follow.

Should you wish, you can revise your question by selecting Actions, then Edit.

The more details you provide (in questions tagged correctly), the more likely it is that members will be able to respond. As it stands, I don't know if there is enough information here for members to understand your issue. So please consider revising your question because I'd really like to see you get a solution to your problem!

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

Regards,

Dedi

[Sandra_Rossi]

If there's any performance issue, it's about the database operation (FETCH here), not DO.

Please clarify what the database performance issue is.

[former_member831369]

The main issue is the package size

[matt]

So make it bigger and see what happens.

Or make it smaller and see what happens.

[Sandra_Rossi]

You'd better post your whole query:

  OPEN CURSOR WITH HOLD gv_cursor FOR
        SELECT a~mblnr a~mjahr a~zeile a~bwart a~matnr a~werks a~lgort a~charg
               a~lifnr a~kunnr a~shkzg a~dmbtr a~menge a~meins a~ebeln a~ebelp
               a~aufnr a~kzbew
               b~budat b~vgart 
          FROM mseg AS a INNER JOIN mkpf AS b ON b~mblnr = a~mblnr AND
                                                 b~mjahr = a~mjahr
          FOR ALL ENTRIES IN it_temp1
          WHERE a~matnr EQ it_temp1-matnr AND
                a~werks EQ it_temp1-werks AND
                a~charg EQ it_temp1-charg AND
                a~shkzg EQ 'S'.

  DO.

    FETCH NEXT CURSOR gv_cursor
           APPENDING TABLE lt_mseg PACKAGE SIZE 25000.

    IF sy-subrc <> 0.
      EXIT.
    ENDIF.
  ENDDO.

[former_member831369]

To much of thanks for this sir ,

Also can you show me how to write parallel cursor method for this neseted loop .

How to write parallel cursor method for 3 4 nested loops.

[Sandra_Rossi]

1) Theoretically, using FOR ALL ENTRIES means that ABAP behaves the same as if DISTINCT is used, so I guess that with a CURSOR all the lines will be extracted at the first FETCH so that to be aggregated by ABAP, so it would make the CURSOR useless.

I'm surprised to find nothing in the ABAP documentation about this very special case.

2) When using FOR ALL ENTRIES, you should always test that the internal table is not empty before using SELECT. If the internal table is empty, your OPEN CURSOR will be equivalent to not using FOR ALL ENTRIES, as shown below, and it's probably not what you want:

  OPEN CURSOR WITH HOLD gv_cursor FOR
        SELECT a~mblnr a~mjahr a~zeile a~bwart a~matnr a~werks a~lgort a~charg
               a~lifnr a~kunnr a~shkzg a~dmbtr a~menge a~meins a~ebeln a~ebelp
               a~aufnr a~kzbew
               b~budat b~vgart 
          FROM mseg AS a INNER JOIN mkpf AS b ON b~mblnr = a~mblnr AND
                                                 b~mjahr = a~mjahr
          WHERE a~shkzg EQ 'S'.

So you should do:

IF it_temp1 IS NOT INITIAL. "<======= here
  OPEN CURSOR WITH HOLD gv_cursor FOR
        SELECT a~mblnr a~mjahr a~zeile a~bwart a~matnr a~werks a~lgort a~charg
               a~lifnr a~kunnr a~shkzg a~dmbtr a~menge a~meins a~ebeln a~ebelp
               a~aufnr a~kzbew
               b~budat b~vgart 
          FROM mseg AS a INNER JOIN mkpf AS b ON b~mblnr = a~mblnr AND
                                                 b~mjahr = a~mjahr
          FOR ALL ENTRIES IN it_temp1
          WHERE a~matnr EQ it_temp1-matnr AND
                a~werks EQ it_temp1-werks AND
                a~charg EQ it_temp1-charg AND
                a~shkzg EQ 'S'.
  DO.
    FETCH CURSOR ...
    ...
  ENDDO.
  CLOSE CURSOR ...
ENDIF.

3) You should avoid mixing JOIN and FOR ALL ENTRIES.

4) If it doesn't solve, you need to ask someone experienced in ABAP and SQL performance (traces, indexes and so on).

[former_member831369]

To much of thanks for this sir ,

Also can you show me how to write parallel cursor method for this neseted loop .

How to write parallel cursor method for 3 4 nested loops

[former_member27]

Hello again, mubashirbennishirur,

I'm glad to see you got an answer to your question! If it helps you, please make sure to accept (you'll see the "accept" option beneath the question) -- so members with similar issues will be able to find a solution to their problems too.

If it doesn't help, you can always leave a comment providing more details that can guide members who wish to assist you.

I hope that this guidance is useful -- and we look forward to your next question for the community!

All the best,

Dedi

[Sandra_Rossi]

You should not go for another solution without trying to solve the current question. "Parallel cursor" is a lot misleading, it comprises lots of different possible solutions, better ask directly the person who proposed that. Please ask a new question if you need complements for this different question.

[roberto_forti]

Hi, you can take a picture from this scenario running SAP performance tools (SQL).

[former_member831369]

Sry sir i didn't get you

[roberto_forti]

Hi, SAP transaction SAT.

[sagarprusty1988]

Hello Mohammed,

Using parallel cursor for the huge selection somehow work. but there is also a limitation for the internal table operation.

1. It is better to use where condition properly to avoid the performance.
2. if you are using S4 HANA system please try to create CDS view or find standard CDS view and you can consume it rather than consuming directly from MSEG and try to pull data in one shot.
3. You can use parallel processing using FM in separate unit of task splitting the where condition and get the data. To do so,

• create RFC FM with range tables you want to filter.
• Then collect the data for which you need to create a subroutine or method.
• Please refer Different type of parallel processing in procedural programming

Please let me know if your issue is resolved.

[Sandra_Rossi]

The "internal table operation" is usually better with "parallel cursor" (OPEN CURSOR/FETCH) than SELECT, because usually the internal table is limited to the size of a package (except that here the OP is possibly missing the advantage with using APPENDING TABLE, rather than using INTO TABLE, although maybe in the code the internal table is maybe later cleared at each loop).

CDS view is intrinsically not better than a cursor or SELECT, it's just possibly better on data modeling aspect.

Using parallel processing with parallel RFC will maybe not solve the OP issue.

Lots of assumptions about what can be the OP issue, as we don't have any information. The question about using DO is not answered. The OP issue is probably more related to how the OPEN CURSOR looks like and what the database contains.

[former_member831369]

Thanks for this help,

Its not a Hana version i am not getting how to change to cursor method ,