Avoid CDPOS Select query in loop

[Former Member]

Hi,

Working on Performance Tuning on the old custom report and came across the below code is taking more time.Can you please help me on this.

LOOP AT it_bsis_bseg.

     CLEAR: l_srchstr.

     CONCATENATE '%'  it_bsis_bseg-werks '%' INTO l_srchstr.

     SELECT objectid tabname fname tabkey value_old cuky_old value_new cuky_new changenr

            APPENDING TABLE it_cdpos

            FROM cdpos

*          FOR ALL ENTRIES IN it_bsis_bseg

           WHERE objectclas EQ 'MATERIAL'

                AND objectid     EQ it_bsis_bseg-matnr

                AND tabname    EQ 'MBEW'

                AND fname        EQ 'STPRS'

                AND tabkey       LIKE l_srchstr.

ENDLOOP.

   IF NOT it_cdpos[] IS INITIAL.

     SELECT objectid udate utime username tcode changenr

        FROM cdhdr

        INTO TABLE it_cdhdr

        FOR ALL ENTRIES IN it_cdpos

      WHERE objectclas EQ 'MATERIAL'

           AND objectid     EQ it_cdpos-objectid

            AND changenr  EQ it_cdpos-changenr.

     ENDIF.

[Former Member]

Why are you select-looping to select CDPOS?

You could just loop and modify it_bsis_bseg to include the field l_srchstr, and then you select CDPOS with for all entries (testing of course if it_bsis_bseg is not empty).

Just to mention an easy and fast solution.

Regards

Luis Becker

[navin_karki]

You can use TCode - ST05 for sql trace for further analysis as to which section of your Query is taking long time..

[Former Member]

Can you please briefly describe your requirement as well. SELECT statement on CDPOS is designed very poorly and it will create performance issue. For each iteration of loop, FOR ALL ENTRIES has been triggered. A better approach will be to check against MATNR for current iteration. However keep in mind that SELECT statements on a large table like CDPOS inside loop will create performance issue. It is recommended to use it outside loop and based on index fields if possible and also to avoid generic search statements while select. You can use ST05 for further analysis of queries.

Regards,

Manish

[raphael_almeida]

Manish...

The FAE is commented.

Warm regards,

Raphael Pacheco.

[Former Member]

Hi All,

Thank you for your response.

LOOP AT it_bsis_bseg.

     CLEAR: l_srchstr.

     CONCATENATE '%'  it_bsis_bseg-werks '%' INTO l_srchstr.

     SELECT objectid tabname fname tabkey value_old cuky_old value_new cuky_new changenr

            APPENDING TABLE it_cdpos

            FROM cdpos

           WHERE objectclas EQ 'MATERIAL'

                AND objectid     EQ it_bsis_bseg-matnr

                AND tabname    EQ 'MBEW'

                AND fname        EQ 'STPRS'

                AND tabkey       LIKE l_srchstr.

ENDLOOP.

@Luis: If we modify the table it_bsis_bseg with field  l_srchstr then can we able to pass multiple entries in tabkey with like keyword in select query of CDPOS because TABKEY has a combination of  WERKS and other field data also? please provide some sample code.

Table after modification example:

MATNR     WERKS     l_srchstr

100              4215          %4215%

200              4225         %4225%

300              4235          %4235%

[raphael_almeida]

Mohammad,

You REALLY need review that your code, is not cool this select within the loop, this is a straight punch on SAP performance. What I suggest you is to carry out this consultation CDPOS out of the loop, and inside it make a READ TABLE of this internal table.

Ahhh, adding more... Thank you for participating in the SCN, I ask you to also check out the links below to better use and approach here .

The SCN Rules of Engagement

How to close a discussion and why

Warm regards,

Raphael Pacheco.