topic Re: Optimise data retrieval from cdhdr in Application Development and Automation Discussions

Optimise data retrieval from cdhdr

Former Member — Thu, 02 Apr 2009 11:12:40 GMT

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??

Re: Optimise data retrieval from cdhdr

Former Member — Thu, 02 Apr 2009 11:15:16 GMT

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")'

Re: Optimise data retrieval from cdhdr

Former Member — Thu, 02 Apr 2009 11:16:40 GMT

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

Re: Optimise data retrieval from cdhdr

Former Member — Thu, 02 Apr 2009 11:24:07 GMT

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!!

Re: Optimise data retrieval from cdhdr

ThomasZloch — Thu, 02 Apr 2009 11:25:46 GMT

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

Re: Optimise data retrieval from cdhdr

Former Member — Thu, 02 Apr 2009 11:26:44 GMT

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")'

Re: Optimise data retrieval from cdhdr

Former Member — Thu, 02 Apr 2009 11:28:25 GMT

use this fm CHANGEDOCUMENT_READ_HEADERS

кu03B1ятu03B9к

Re: Optimise data retrieval from cdhdr

Former Member — Thu, 02 Apr 2009 11:30:15 GMT

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

Re: Optimise data retrieval from cdhdr

Former Member — Thu, 02 Apr 2009 11:30:53 GMT

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к

Re: Optimise data retrieval from cdhdr

Former Member — Thu, 02 Apr 2009 11:41:18 GMT

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.

Re: Optimise data retrieval from cdhdr

Former Member — Thu, 02 Apr 2009 11:56:50 GMT

icant find in indexe z05 of cdhdr in de11

Re: Optimise data retrieval from cdhdr

ThomasZloch — Thu, 02 Apr 2009 12:10:38 GMT

> icant find in indexe z05 of cdhdr in de11

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

Re: Optimise data retrieval from cdhdr

Former Member — Thu, 02 Apr 2009 12:30:06 GMT

Thanks!