Sequential access on CDHDR table

[Former Member]

How to improve this query on CDHDR which leads to Sequential access on CDHDR table so that it can improve performance, or is there any other means which i can achieve better performance on CDHDR?

the query as follows:

SELECT objectclas objectid changenr FROM cdhdr INTO TABLE it_cdhdr

WHERE changenr > changenr1 AND ( objectclas = 'VERKBELEG' ).

Thnaks in Advance

Ramada

[Former Member]

With the select statement above, the query actually hits the index on OBJECTCLAS alone. It would help to put in the OBJECTCLAS and OBJECTID in the select.

Also, instead of coding the Changenr > changenr in the select, do a select on Objectclas and objectid and then use the DELETE ADJACENT DUPLICATES statement to delete entires with condition changenr > changenr1.

Hope this helps.

Sudha

[Former Member]

Thank you Sudha for the prompt responce,

i will do it as per your stated suggestions and it is not reducing the sequential access on CDHDR as its a hue table, in production it is showing very bady performance.

This table got 2 indexes

1. MANDANT Client

CHANGENR Document change number

2. OBJECTCLAS Object class

OBJECTID Object value

How can i change my SQL to improve further based on the above indexs?

Please help me out.

Regards,

Ramada

[Former Member]

The only way to hit the indexes would be to specify the select with both OBJECTCLAS and OBJECTID in the WHERE clause, and remove the CHANGENR from it, as I had suggested earlier.

Sudha

[Former Member]

HI Sudha,

i have tried the way you have suggested, but i am not able to get it right.

see the code below.

DATA: changenr1 LIKE cdhdr-changenr VALUE '0001412183'.

SELECT objectclas objectid changenr FROM cdhdr INTO TABLE it_cdhdr

WHERE ( objectclas = 'VERKBELEG').

Delete adjacent duplicates from IT_cdhdr

comparing changenr .

Regards,

Ramada

[Former Member]

Hey Ramada,

I have noticed that you only ask questions to seek answers but do not give any points. Why do you ask new questions when your old queries remain open and abandoned. What is the matter? Did you find a solution to those issues? If so why not close the thread and allocate points to people whose answers helped. If not why don't you give your feedback or ask for clarifications. I have virtually provided you the complete code (solution) for your problems and you have not even bothered to close the query and allocate points. If you continiue to do this nobody will answer your questions. It is not worth our while.

Regards,

Mark

[Former Member]

HI Mark,

First of all sorry for the late responce.

I have not tested the code given by you, i am going through that code and prparing to implement it by may 15th.

I do apologise for the late responce

I will allocate points as per the solution given and give you the feedback once i tested the code.

Cheers and Regards,

Ramada

[christian_wohlfahrt]

Hi Ramada!

Sounds like you don't need change documents (CDHDR), but change pointers (BDCP / BDPCS or BDCPV). You can define a message type (WE81), assign some fields you are interested in (BD52) and activate the message (BD50).

Then every time something was changed (CDHDR creation), SAP looks for assigned change pointers and writes additional (new) entries in BDCP. This is like your changenr > param.

When you are ready with your execution, you can set the pointers to status 'X' and won't read them again.

Regards,

Christian

[Former Member]

Hi Christian,

I am not able to understand the given hints.

1. Why shall i create a Message type (WE81).

2. I am asking to improve the performance of the givne SQL, which is accessing the CDHDR tbale.

Please let me know how to improve the performance of the code.

Regards,

Ramada

[christian_wohlfahrt]

Hi Ramada!

Your 2nd index (with change number) has no MANDT field - maybe this is the reason it's not used. Also a combination of change number and object class can be better; you might give it a try.

But in general there is no good way to select the CDHDR for changes done since xxx (change number or date / time).

Of course it's database optimizer decision, but likely you and up all the time with sequential read accesses -> it will be slow in production.

So I gave you a hint how to change the program design a little bit - from business process point of view, this shouldn't be a difference. But if you change from CDHDR to BDCP(S), then you can select changes much faster (usually in a few seconds, even for >100.000 entries).

Regards,

Christian

[Former Member]

HI Christian,

Thank you for the reply.

As i have never worked on change pointers concept in IDOCS hence i am not able to understand how to use this table BDCP instead of CDHDR. Could you please elobarate on How to use this in my Function module.

Regards,

Ramada

[christian_wohlfahrt]

Hi Ramada!

This has nothing to do with IDOCs. You only need the three mentioned transactions. In BD52 you have to assign VERKBELEG and some fields like KEY for creation and others you are interested in (if in question, have a look in CDPOS for the fields / use all of the F4-help).

Then you can use FM CHANGE_POINTERS_READ for reading and FM CHANGE_POINTERS_STATUS_WRITE for marking the pointers as read -> they aren't selected again. With BD22 some deletion of old entries can be done. That's it - you have an easy way to be informed of changes exactly once (since the last run of your report / whatever interval you use).

Search the forum / help if you like to have more details.

Regards,

Christian

[Former Member]

Hi Christian,

Thanks for the repsponce,

I do't have authorisation to create any message type in BD52. what shall i do now?

Please suggest?

Regards,

Ramada

[christian_wohlfahrt]

Hi,

ask for the authorization / someone else to do this.

[Former Member]

HI

Thanks for the responce.

Regards,

Ramada