Performance issue when querying CDPOS table

[Former Member]

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.

[Former Member]

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

[Former Member]

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