topic Re: Avoid CDPOS Select query in loop in Application Development and Automation Discussions

Avoid CDPOS Select query in loop

Former Member — Fri, 28 Aug 2015 19:05:42 GMT

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.

Re: Avoid CDPOS Select query in loop

Former Member — Fri, 28 Aug 2015 19:51:11 GMT

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

Re: Avoid CDPOS Select query in loop

navin_karki1 — Fri, 28 Aug 2015 19:51:42 GMT

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

Re: Avoid CDPOS Select query in loop

Former Member — Fri, 28 Aug 2015 22:29:36 GMT

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

Re: Avoid CDPOS Select query in loop

Former Member — Fri, 28 Aug 2015 22:52:41 GMT

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%

Re: Avoid CDPOS Select query in loop

raphael_almeida — Sat, 29 Aug 2015 00:10:45 GMT

Manish...

The FAE is commented.

Warm regards,

Raphael Pacheco.

Re: Avoid CDPOS Select query in loop

raphael_almeida — Sat, 29 Aug 2015 00:21:49 GMT

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.