topic Re: CDPOS in Application Development and Automation Discussions

CDPOS

Former Member — Wed, 14 Feb 2007 12:04:38 GMT

Hi All

This is my first post on this forum and I am new to ABAP so please be gentle.

I am trying to extract a subset of data from CDPOS the cluster table. We have been doing this using Cognos sap connector but it takes over 6 hours. So I wrote a function module to extract from cluster table into new transparent table. There has been some performance improvement but not as much I would like.

My ABAP is pretty basic as similar to Oracle as that is my background. My code looks like this

SELECT MANDANT OBJECTCLAS OBJECTID CHANGENR

TABNAME FNAME VALUE_NEW VALUE_OLD

FROM CDPOS

INTO TABLE it_ztbcdpos

WHERE OBJECTCLAS = 'ORDER'

AND TABNAME = 'AFKO'

AND FNAME = 'GLTRP'

AND CHNGIND = 'U'.

Delete all records from transparent table

DELETE FROM ZTBCDPOS WHERE OBJECTCLAS IS NOT NULL.

COMMIT WORK.

transfer internal table to transparant table.

INSERT ZTBCDPOS FROM TABLE it_ztbcdpos.

Anything I can do optimise ? I have read on another thread about using CHANGEDOCUMENT_READ but I am not familiar with it. Any help appreciated.

Thanx in advance

Asif

Re: CDPOS

Former Member — Wed, 14 Feb 2007 13:54:56 GMT

this is a huge table... use this FM to get the entries

CHANGEDOCUMENT_READ or

CHANGEDOCUMENT_READ_HEADERS Change document: Read change document header

CHANGEDOCUMENT_READ_POSITIONS Change document: Read change document items..

by passing the same details u r pssing into the select query u ahve given..

it improve the performance...

one more thing here is u r nor passing primaary ke values

Re: CDPOS

Former Member — Wed, 14 Feb 2007 14:59:46 GMT

This mnay or may not work. But you can try first doing a SELECT against CDHDR instead of CDPOS using OBJECTCLAS only into an internal table. Then use this internal table in a SELECT FOR ALL ENTRIES against CDPOS using the other criteria.

Rob

Re: CDPOS

Former Member — Wed, 14 Feb 2007 18:03:03 GMT

I have tried CHANGEDOCUMENT_READ and it appears to take longer in the dev client atleast. Admittedly it only has 438000 records compared to the millions in production. Can someone explain why it might be quicker than a single database select statement please before I consider trying in prod..

I will look into trying to specify the full primary key values.

Thanx

Asif

Re: CDPOS

Former Member — Wed, 14 Feb 2007 20:52:47 GMT

Ashif,

do you really need to select all changed ever made to this table/field (AFKO/GLTRP) or you are interested in some date range only.

If you are interested in date range - select from SDHDR first using objectclas, udate.

We created new index for CDHDR using objectclas, udate, tcode to make some of our programs faster. You can create new idex for CDHDR as well which may significally improve the speed of your selection.

Then you can select from CDPOS based on full key from CDHDR...it will be much faster than just select directly from CDPOS as this table is constantly growing and your select will take longer & longer and will fail eventually anyway.

Re: CDPOS

Former Member — Thu, 15 Feb 2007 12:26:45 GMT

Thanx for you rsuggestions regarding CDHDR. Can you confirm that CDHDR is only appended to and records already in there do NOT get updated (changed).

Is this correct

Asif

Re: CDPOS

christian_wohlfahrt — Thu, 15 Feb 2007 13:20:49 GMT

Hi Asif!

Yes it's correct:

all changes make only inserts in CDHDR (and CDPOS), only archiving makes deletes.

In rare cases just no CDHDR entry is written (similiar to: there is no change document at all - it has to be programmed explicitly).

Regards,

Christian

Re: CDPOS

Former Member — Thu, 15 Feb 2007 16:12:55 GMT

Hi Siarhi

I think your idea of using CDHDR is a good one. Would you suggest also using FOR ALL ENTRIES too?

Asif

Re: CDPOS

Former Member — Thu, 15 Feb 2007 16:47:52 GMT

yes, you can select all changes from CDHDR into internal table, then select form CDPOS for all entries in this internal table (do not forget to check that internal table is NOT empty before using for all entries).

Re: CDPOS

Former Member — Thu, 15 Feb 2007 17:36:37 GMT

Check my earlier resonse.

Rob

Re: CDPOS

Former Member — Thu, 15 Feb 2007 18:01:28 GMT

Yes Rob, you did suggest this approach earlier, however, I discounted it as our CDHDR table contains almost 9 million records, and I read somewhere that SELECT FOR ALL ENTRIES using a large dataset was not effecient. Using the date to drastically cut down the dataset makes this approach far more appealing.

Maybe you implied this but as I am new to SAP I didnt get it.

Thanx

Asif

Re: CDPOS

Former Member — Thu, 15 Feb 2007 18:31:46 GMT

I just ran a partial test. I would stick with the way you originally wrote it.

Rob

Re: CDPOS

Former Member — Tue, 20 Feb 2007 10:58:01 GMT

Hi Rob

Please elaborate. What do you mean by partial test and which approach should I stick to. Are you saying that using CDHDH and a date is not the way to go ?

Please supply test results and conclusions.

regards

Asif