Reading Data From Change Tables:

[Former Member]

I need to read data from Change Tables:

My Primary requirement is to Find all updated CONTRACT VALUES (<b>AMTBL</b>), BEGIN DATE (<b>GUEBG</b>) and END DATE (<b>GUEEN</b>) from table VBAK in a particular date range.

I tried the following code: But it has become a really bad performance issue.

SELECT objectid changenr

FROM cdhdr INTO TABLE t_cdhdr

WHERE objectclas = 'VERKBELEG'

AND udate IN so_erdat

AND tcode = 'VA42'.

SELECT objectid changenr fname value_old value_new

FROM cdpos

INTO TABLE t_cdpos

FOR ALL ENTRIES IN t_cdhdr

WHERE objectclas = 'VERKBELEG'

AND objectid = t_cdhdr-objectid

AND changenr = t_cdhdr-changenr

AND tabname = 'VBAK'

AND fname in ('GUEEN' , 'AMTBL' , 'GUEBG')

AND chngind = 'U'.

Can you please guide me in the right way to make it performance effective.

[RichHeilman]

Try adding.....

select objectid changenr
from cdhdr into table t_cdhdr
where objectclas = 'VERKBELEG'
and udate in so_erdat
and tcode = 'VA42'.


<b>check not t_cdhdr[] is initial.
sort table t_cdhdr ascending by objectclas objectid changenr.</b>

select objectid changenr fname value_old value_new
from cdpos
into table t_cdpos
for all entries in t_cdhdr
where objectclas = 'VERKBELEG'
and objectid = t_cdhdr-objectid
and changenr = t_cdhdr-changenr
and tabname = 'VBAK'
and fname in ('GUEEN' , 'AMTBL' , 'GUEBG')
and chngind = 'U'.

Regards,

Rich Heilman

[Former Member]

Raj,

Try using joins as follows..

SELECT a_{objectid a}changenr a~fname

b_{value_old b}value_new

FROM cdhdr as a

inner join cdpos as b

on a_{objectid = b}objectid

AND a_{changenr = b}t_cdhdr-changenr

INTO TABLE t_ittab

WHERE a~objectclas = 'VERKBELEG'

AND a~udate IN so_erdat

AND a~tcode = 'VA42'

AND ......from second table.

This will much faster than individual select statment.

Cheers,

Nilesh

[Former Member]

look into OSS on the CDHDR table. there is a note about creating an idex over CDHDR by objectclas and udate. once we did this, LOTS of stuff started running much faster.