For all entries issue

[Former Member]

Hi All,

Below is the select query I have which is taking more time when the records in the internal table is touching to almost 2 million.

SELECT paledger vrgar versi

perio paobjnr pasubnr

belnr posnr gjahr perde

FROM ce1ec01

APPENDING TABLE t_ce1ec01 PACKAGE SIZE 10000

FOR ALL ENTRIES IN t_arixco2_tmp

WHERE paobjnr EQ t_arixco2_tmp-paobjnr

AND PERIO gt p_perio.

ENDSELECT.

The secondary index is created with fields PAOBJNR and PERIO fields.

I have used ST05 and it is using the secondary index.

Regards,

Pratyusha.

[Former Member]

Well, the first thing to do is to eliminat e duplicate paobjnr from t_arixco2_tmp before doing the SELECT.

Rob

[former_member192616]

the second thing (after Rob's suggestion) to do is to try it like this:

SELECT paledger vrgar versi

perio paobjnr pasubnr

belnr posnr gjahr perde

FROM ce1ec01

INTO TABLE t_ce1ec01

FOR ALL ENTRIES IN t_arixco2_tmp

WHERE paobjnr EQ t_arixco2_tmp-paobjnr

AND PERIO gt p_perio.

since the package size will not save memory for the FAE we can try it directly

since all the data is selected before the package size is applied in any case.

if this fails we have a configuration issue. (your memory demand is higher than what

your configuration allows). You have to think of alternative solutions than... e.g.

divide the entries in t_arixco2_tmp in packages and process one after the other with FAE.

Kind regrds,

Hermann

[Former Member]

Sorry for getting back to this lately!

It helped a lot.

[Former Member]

Hello,

Its better to check whether table is INITIAL before using it for FOR ALL ENTRIES.

You can populate the data into an Internal table rather than using SELECT ENDSELECT query.

Hope it helps you!

Regards,

M M Jaffer.

[anjaneya_bhardwaj2]

Deviding the data fetched in package size may produce good results if we have space issue in ABAP memory using the OPEN FETCH AND CLOSE Cursor . however it will produce significant performance improvement in our case that has a lot of data as we are going to have an Array fetch by using the Cursor (Also deciding the package size optimum will help you significantly) . The best solution is to optimize your selction by passing index (sorted not duplicates) and then use cursor for that selection .

Cheers

Edited by: Anjaneya Bhardwaj Anjaneya Bhardwaj on Dec 8, 2010 7:10 AM

[Former Member]

Hi,

Avoid select END select.

Use to fetch the record in to the internal table ( In to table not into corresponding table).

Create index and compress the index and force the select query to use this index by as follows

%_HINTS ORACLE 'INDEX("COEP" "COEP~1")'.

Refer SAp Note: 185530