Problem for Accessing the Table BDCP..CDPOS..CDHDR..

[prabhu_rengaraju4]

Hi Guys,

i have the problem of accessing the BDCP table.it is working fine in development and testing server.it is taking more time in background in Production server..can anyone help me to optimize this code and please suggest me if i can change anyof the below logic..

Thanks a lot in advance.

LOOP AT t_cdobjid.

objectid-low = t_cdobjid-cdobjid.

objectid-sign = 'I'.

objectid-option = 'EQ' .

.

APPEND objectid.

CLEAR objectid.

count = count + 1.

IF count = 50.

CLEAR count.

SELECT * FROM bdcp APPENDING TABLE t_bdcp

WHERE cretime IN r_cretime AND

tabname = 'DMEAN' AND

fldname = 'KEY' AND

cdobjid IN objectid.

CLEAR : objectid.

REFRESH: objectid.

ENDIF.

ENDLOOP.

IF NOT objectid[] IS INITIAL.

SELECT * FROM bdcp APPENDING TABLE t_bdcp

WHERE cretime IN r_cretime AND

tabname = 'DMEAN' AND

fldname = 'KEY' AND

cdobjid IN objectid.

CLEAR : objectid.

REFRESH: objectid.

ENDIF.

IF NOT t_bdcp[] IS INITIAL.

t_bdcp_val[] = t_bdcp[].

DESCRIBE TABLE t_bdcp_val LINES l_lines.

l_max = 50.

l_mod = l_lines MOD l_max.

IF l_mod > 0.

l_loopcount = ( l_lines DIV l_max ) + 1.

ELSE .

l_loopcount = l_lines DIV l_max.

ENDIF.

DO l_loopcount TIMES.

CLEAR t_bdcp_emt.

REFRESH t_bdcp_emt.

IF sy-index = l_loopcount.

t_bdcp_emt[] = t_bdcp_val[].

ELSE.

APPEND LINES OF t_bdcp_val FROM 1 TO l_max TO t_bdcp_emt.

DELETE t_bdcp_val FROM 1 TO l_max.

ENDIF.

SELECT * FROM cdpos APPENDING TABLE t_cdpos_upc

FOR ALL ENTRIES IN t_bdcp_emt

WHERE

objectclas = 'MATERIAL' AND

objectid = t_bdcp_emt-cdobjid AND

changenr = t_bdcp_emt-cdchgno AND

tabname IN ('DMEAN', 'MARA' ) AND

fname IN ('KEY', 'EAN11' ) .

ENDDO.

CLEAR : l_lines ,

l_mod ,

l_loopcount.

• IF sy-subrc EQ 0.

IF NOT t_cdpos_upc[] IS INITIAL.

t_cdpos_del[] = t_cdpos_upc[] .

DELETE t_cdpos_del WHERE tabname EQ 'MARA' .

SORT t_cdpos_del BY changenr .

LOOP AT t_cdpos_upc .

READ TABLE t_cdpos_del WITH KEY

changenr = t_cdpos_upc-changenr

BINARY SEARCH .

IF sy-subrc EQ 0 AND

t_cdpos_upc-chngind = 'U' .

DELETE t_cdpos_upc WHERE changenr = t_cdpos_upc-changenr

AND chngind = 'D' .

ENDIF.

t_upc_matnr-matnr = t_cdpos_upc-objectid .

APPEND t_upc_matnr .

ENDLOOP.

SORT t_upc_matnr BY matnr .

DELETE ADJACENT DUPLICATES FROM t_upc_matnr COMPARING matnr .

IF NOT t_cdpos_upc[] IS INITIAL.

t_cdpos_upc_val[] = t_cdpos_upc[].

DESCRIBE TABLE t_cdpos_upc_val LINES l_lines.

l_max = 50.

l_mod = l_lines MOD l_max.

IF l_mod > 0.

l_loopcount = ( l_lines DIV l_max ) + 1.

ELSE .

l_loopcount = l_lines DIV l_max.

ENDIF.

DO l_loopcount TIMES.

CLEAR t_cdpos_upc_emt.

REFRESH t_cdpos_upc_emt.

IF sy-index = l_loopcount.

t_cdpos_upc_emt[] = t_cdpos_upc_val[].

ELSE.

APPEND LINES OF t_cdpos_upc_val FROM 1 TO l_max TO

t_cdpos_upc_emt.

DELETE t_cdpos_upc_val FROM 1 TO l_max.

ENDIF.

SELECT * FROM cdhdr APPENDING TABLE it_cdhdr_upc

FOR ALL ENTRIES IN t_cdpos_upc_emt

WHERE objectclas EQ 'MATERIAL'

AND objectid = t_cdpos_upc_emt-objectid

AND changenr = t_cdpos_upc_emt-changenr.

ENDDO.

ENDIF.

Prabhu

[prabhu_rengaraju4]

Hi Prabhu!

Just define your own change pointer (WE81) and assign only the fields (BD52) you are interested in. (Don't forget to activate the message (BD50). Then you only get change pointers for DMEAN / KEY and MARA / EAN11 and you can use the standard FM CHANGE_POINTERS_READ for reading and CHANGE_POINTERS_STATUS_WRITE for setting the process flag (otherwise you would read the pointers not only once).

Regards,

Christian

[christian_wohlfahrt]

Hi Prabhu!

Just define your own change pointer (WE81) and assign only the fields (BD52) you are interested in. (Don't forget to activate the message (BD50). Then you only get change pointers for DMEAN / KEY and MARA / EAN11 and you can use the standard FM CHANGE_POINTERS_READ for reading and CHANGE_POINTERS_STATUS_WRITE for setting the process flag (otherwise you would read the pointers not only once).

Regards,

Christian

[Former Member]

Hi,

One way to optimize this code is to move the select queries outside the loop. You can do it by fetching the data in an internal table n then use FOR ALL ENTRIES in SELECT query.

Its taking more time in production because you might have large data in production.

[Former Member]

Use the PACKAGE SIZE oprion of the SELECT statement:

LOOP AT t_cdobjid.

  objectid-low = t_cdobjid-cdobjid.
  objectid-sign = 'I'.
  objectid-option = 'EQ' .

  APPEND objectid.
  CLEAR objectid.

ENDLOOP.

SELECT * FROM bdcp APPENDING TABLE t_bdcp
  PACKAGE SIZE 5000
  WHERE cretime IN r_cretim AND
        tabname = 'DMEAN'   AND
        fldname = 'KEY'     AND
        cdobjid IN objectid.

ENDSELECT.

IF NOT objectid[] IS INITIAL.
.
.
.