acces CDPOS optimize

[adil]

Hi,

i use this code but it takes a lot of time to acces to CDPOS

Any suggestion to optimize

*************************************************

REPORT ZCDHDR .

parameters p_date like sy-datum.

start-of-selection.

data wt_cdhdr like cdhdr occurs 0 with header line.

data wt_cdpos like cdpos occurs 0 with header line.

Data wl_show type tplnr.

DATA : w_deb like sy-uzeit, w_fin like sy-uzeit.

w_deb = sy-uzeit.

refresh : wt_cdhdr , wt_cdpos.

select * from cdhdr into table wt_cdhdr

where objectclas = 'IFLO'

and udate = p_date.

select * from cdhdr APPENDING CORRESPONDING FIELDS OF TABLE wt_cdhdr

where objectclas = 'EQUI'

and udate = p_date.

if not wt_cdhdr[] is initial.

select * from cdpos into table wt_cdpos " take a lot of time

for all entries in wt_cdhdr

where changenr = wt_cdhdr-changenr.

*and ( tabname = 'IFLOT' OR tabname = 'IFLOTX' OR tabname = 'ILOA' )

*AND ( fname = 'TPLMA' OR fname = 'PLTXT' OR fname = 'GROES' OR fname = 'EQART' OR fname = 'TPLNR' ).

endif.

w_fin = sy-uzeit - w_deb.

write /: w_fin.

loop at wt_cdpos.

clear wl_show.

CALL FUNCTION 'CONVERSION_EXIT_TPLNR_OUTPUT'

EXPORTING

input = wt_cdpos-objectid

IMPORTING

output = wl_show.

read table wt_cdhdr with key objectid = wt_cdpos-objectid.

write: / wl_show , wt_cdhdr-username .

endloop.

[adil]

i forget : AND condition is without comment

select * from cdpos into table wt_cdpos " take a lot of time

for all entries in wt_cdhdr

where changenr = wt_cdhdr-changenr.

and ( tabname = 'IFLOT' OR tabname = 'IFLOTX' OR tabname = 'ILOA' )

AND ( fname = 'TPLMA' OR fname = 'PLTXT' OR fname = 'GROES' OR fname = 'EQART' OR fname = 'TPLNR' ).

[Former Member]

(oops, wrong user)

Edited by: Rui Pedro Dantas on Jul 31, 2009 5:02 PM

[Rui_Dantas]

Hello yassine82,

CDHDR / CDPOS are designed to get all the changes for a given object (for example, all the changes for one equipment). That is how they are used in standard transactions, and they are fast when used correctly.

They are not designed to get all the objects that were changed in a given period (for example, all equipments changed today).

If you need to know all objects that were changed (for example, because you have an interface that sends all changes in equipments) I suggest you use change pointers instead.

Some transactions you'll need:

WE81 to create a new message type;

BD52 to configure which changes are relevant for you (and therefore create a change pointer);

BD50 to activate the message type.

This means that everytime a change is made (and recorded in CDHDR / CDPOS) then a pointer is created in tables BDCP / BDCPS. You can then read these tables, do what you have to do, and mark the pointer as processed (BDCPS-PROCESS). These change pointers should be regularly cleaned with transcation BD22 / report RBDCPCLR.

Hope this helps,

Rui Dantas

[ThomasZloch]

Actually, your select on CDHDR should already take some time, or at least it will once your system fills up with EUQI and IFLO type change documents over time.

Anyway, if you include OBJECTCLAS and OBJECTID as found in CDHDR in the WHERE-conditions of your select on CDPOS, you should see a significant improvement by using the primary key.

As a minor improvement, declare your internal tables for CDHDR/CDPOS only with those fields you actually need for further processing.

Also make sure that the READ TABLE statement uses a binary search, whether explicit or implicit.

Thomas

[adil]

now is more fast

if not wt_cdhdr[] is initial.

select OBJECTCLAS OBJECTID CHANGENR TABNAME TABKEY FNAME CHNGIND VALUE_NEW VALUE_OLD from cdpos into table wt_cdpos

for all entries in wt_cdhdr

where objectclas = wt_cdhdr-objectclas

and objectid = wt_cdhdr-objectid

and changenr = wt_cdhdr-changenr

and ( tabname = 'IFLOT' OR tabname = 'IFLOTX' OR tabname = 'ILOA' )

and ( fname = 'TPLMA' OR fname = 'PLTXT' OR fname = 'GROES' OR fname = 'EQART' OR fname = 'TPLNR' ).

endif.

thanks.