Performance issue when querying CDPOS table

Former Member — Thu, 12 Nov 2009 17:17:18 GMT

Hi,

I have written a sample code where in the selection screen i would entering date range,for that range,i need PL changes which have taken place.but if i enter date range for 3-4 months it takes lot of time to execute.Any idea how to overcome the performance issue.

REPORT ZTESTM1.

TABLES: MARA,

zwcoo_repository.

DATA: T_CDHDR LIKE CDHDR OCCURS 0 WITH HEADER LINE,

T_CDPOS LIKE CDPOS OCCURS 0 WITH HEADER LINE.

selection-screen begin of block b1.

select-options: s_ardate FOR zwcoo_repository-start_date.

selection-screen end of block b1.

START-OF-SELECTION.

SELECT * FROM CDHDR

INTO TABLE T_CDHDR

WHERE OBJECTCLAS = 'MATERIAL'

AND UDATE IN s_ardate.

SELECT VALUE_OLD VALUE_NEW

FROM CDPOS INTO TABLE T_CDPOS

FOR ALL ENTRIES IN T_CDHDR

WHERE OBJECTCLAS = 'MATERIAL' AND

OBJECTID = T_CDHDR-OBJECTID AND

CHANGENR = T_CDHDR-CHANGENR AND

TABNAME = 'MARA' AND

FNAME = 'SPART' AND

CHNGIND = 'U'.

LOOP AT T_CDPOS.

WRITE:/ T_CDPOS-OBJECTID.

WRITE:/ T_CDPOS-VALUE_OLD.

WRITE:/ T_CDPOS-VALUE_NEW.

WRITE:/ T_CDHDR-UDATE.

ENDLOOP.

Re: Performance issue when querying CDPOS table

Former Member — Thu, 12 Nov 2009 17:39:55 GMT

Hi Manjunath,

Can you fine tune the first select query like, you can fetch only the fields you are passing in the second select query instead of fetching all fields using SELECT * , so do like SELECT F1 F2.....into T_CDHDR. This aslo means that you should define internal table T_CDHDR with the selected fields.

This way the load on the second select query is reduced to a certain extent. Also put a condition like if T_CDHDR[] is not initial the n only proceed with SELECT FOR ALL.

Also try converting the data in the T_CDHDR to ranges if possible as I have seen there are some constant values you are passing in the WHERE clause.

Please set to resolved if it helps you.

Regards

Abhii...

topic Re: Performance issue when querying CDPOS table in Application Development and Automation Discussions

Performance issue when querying CDPOS table

Re: Performance issue when querying CDPOS table