Optimise data retrieval from cdhdr

[Former Member]

Hi,

SELECT objectid udate utime username FROM cdhdr

INTO TABLE tb_cdhdr

WHERE objectid IN ra_pernrange

AND udate IN so_datum

AND username IN so_uname.

This is the query I am using to retrieve any changes made to the pernr-qualification relationship. It takes ages to retrieve the data.

Is there any way to optimize this??

[Former Member]

If u see this table in SE11, there are indexes set on this table. As per you requirement i feel index 'Z05' is the most appropriate one which forms the index on the OBJECTID , UDATE(Creation date of the change document) and TCODE (Transaction in which a change was made).

select*
from cdhdr
into table..
where....
%_HINTS ORACLE 'INDEX("CDHDR" "CDHDR~Z05")'

[Former Member]

SELECT objectid udate utime username FROM cdhdr

INTO TABLE tb_cdhdr

WHERE objectid IN ra_pernrange

AND udate IN so_datum

AND username IN so_uname.

For ra_pernrange,

here i am concatenating the pattern '00' sy-mandt 'P' wa_pernrqd '01A0321Q' '*'

Please elaborate on using indexes with this query m nt sure hw to do it!!

[Former Member]

SELECT objectid udate utime username FROM cdhdr

INTO TABLE tb_cdhdr

WHERE objectid IN ra_pernrange

AND udate IN so_datum

AND username IN so_uname

%_HINTS ORACLE 'INDEX("CDHDR" "CDHDR~Z05")'

[Former Member]

Index Z05 that u have mentioned is a z index created on ur server so it will not be availble in other systems, besides creating indexes is not suggested always as it slows down insert operations and table like cdhdr which record each and evary change thus have a lot of insert operation on them so their performance will be affected so crearting secondary index should be last option to use,

кu03B1ятu03B9к

[Former Member]

Hi,

In the above select statement there are no key fields in the where clause. Try using all the key fields OBJECTCLAS

OBJECTID

CHANGENR along with your selection, the performance is always faster when the key fields are used.

cheers,

babu

[ThomasZloch]

You must include OBJECTCLAS in the WHERE conditions. Find out the correct value for the type of change documents you are looking for, it will most likely be one fixed value.

Index Z05 is a customer specific secondary index by Mr. SAP USER, you will not find it in your system.

Thomas

[Former Member]

use this fm CHANGEDOCUMENT_READ_HEADERS

кu03B1ятu03B9к

[Former Member]

Hi,

I just did a sample run of your code, its working fine in my system.

I think its optimised, there is a problem somewhere else.

However i had to change the select-options parameter ra_pernrange to ra_pernr as it did not accept more than 8 characters for select-options.

But its working fine and populating the internal table as well.

Refer the sample code:

TABLES: CDHDR.
TYPES: BEGIN OF T_CDHDR,
  OBJECTID TYPE CDOBJECTV,
  UDATE TYPE CDDATUM,
  UTIME TYPE CDUZEIT,
  USERNAME TYPE CDUSERNAME,
  END OF T_CDHDR.

DATA: TB_CDHDR TYPE TABLE OF T_CDHDR.

SELECT-OPTIONS: RA_PERNR FOR CDHDR-OBJECTID,
                SO_DATUM FOR CDHDR-UDATE,
                SO_UNAME FOR CDHDR-USERNAME.

SELECT OBJECTID UDATE UTIME USERNAME FROM CDHDR
INTO TABLE TB_CDHDR
WHERE OBJECTID IN RA_PERNR
AND UDATE IN SO_DATUM
AND USERNAME IN SO_UNAME.

Regards.

Edited by: rajan roy on Apr 2, 2009 1:30 PM

[Former Member]

How do u check indexes as in how did u get to know it is z05

Also i am using this table to check changes made to qualification data for all pernrs in the system is there any other way as cdhdr is slowing down performance in test server as there are 1000 employees in test system.

[Former Member]

icant find in indexe z05 of cdhdr in de11

[ThomasZloch]

> icant find in indexe z05 of cdhdr in de11

Really? Why don't you read and understand all replies to your question?

[Former Member]

Thanks!