topic Re: COEP select optimization in Application Development and Automation Discussions

COEP select optimization

Former Member — Thu, 22 Mar 2012 10:17:10 GMT

Hi experts,

I would like to ask you for select optimization:

SELECT mandt kokrs objnr belnr buzei gjahr wrttp beknz wkgbtr ebeln ebelp timestmp
FROM coep CLIENT SPECIFIED
INTO CORRESPONDING FIELDS OF TABLE lt_covp
FOR ALL ENTRIES IN p_gt_outtab
WHERE mandt EQ sy-mandt
AND kokrs EQ g_kokrs
AND objnr EQ p_gt_outtab-objnr
AND ( wrttp EQ '04' OR wrttp EQ '12')
AND beknz NE 'A'
AND gjahr LE p_gjahr.

Generally there can be a lot of data in p_gt_outtab and it results in TSV_TNEW_PAGE_ALLOC_FAILED error. The selection time is very long too. I 'm using MANDT KOKRS OBJNR index. Thank you in advance.

Best regards

Paul Smuda

Re: COEP select optimization

Former Member — Thu, 22 Mar 2012 10:25:14 GMT

This message was moderated.

Re: COEP select optimization

janisar_munshi — Thu, 22 Mar 2012 10:43:06 GMT

Pawel,

Technical Setting of the table shows Size Category as

4	Data records expected: 31,000 to 120,000

so it is assumed to have maximum .12 million records.

The key u have used seems fine. Try to have maximum fields on selection that can reduce the selection.

try following as well.

1. remove CORRESPONDING FIELDS by matching the selection field and internal table structure

2. Sort table p_gt_outtab in the sequence of where clause and use delete ADJACENT DUPLICATES using the same key

3. check the empty table p_gt_outtab. if it is empty, will return the entire data from coep.

4. if still you get the error you can distribute data in multiple internal table using select * UP TO 1000 rows into table lt_covp.

Hope this would help.

Re: COEP select optimization

Former Member — Thu, 22 Mar 2012 10:50:58 GMT

Hi Pawel,

to work around the alloc. failed issue. Use the PACKAGE SIZE addition to the SELECT statment.

SELECT mandt kokrs objnr belnr buzei gjahr wrttp beknz wkgbtr ebeln ebelp timestmp
FROM coep
CLIENT SPECIFIED
INTO CORRESPONDING FIELDS OF TABLE lt_covp
FOR ALL ENTRIES IN p_gt_outtab
PACKAGE SIZE 10000
WHERE mandt EQ sy-mandt
AND kokrs EQ g_kokrs
AND objnr EQ p_gt_outtab-objnr
AND ( wrttp EQ '04' OR wrttp EQ '12')
AND beknz NE 'A'
AND gjahr LE p_gjahr.

* process your data here.

ENDSELECT.

Also do you really need the CLIENT SPECIFIED OPTION. If you only want data from the current client then drop that option the ABAP kernel will take of it for you.

Cheers
Adi

Re: COEP select optimization

RaymondGiuseppi — Thu, 22 Mar 2012 10:58:37 GMT

First remove the mandt field and client specified. (not required)

The problem may come from the FOR ALL ENTRIES which is definitively not an accelerator (interpreted as a long list of FETCH for some values) also the data will be stored by the system before deletion of duplicate records (Note 634263 - Selects with FOR ALL ENTRIES)

You could try some parallelization, create a RFC enabled FM which will receive a subset of the p_gt_outtab and be executed STARTING NEW TASK and PERFORMING subroutine or CALLING method at END. IN this FM, if you create not-too large subset, you could replace the FOR ALL ENTRIES by a WHERE IN clause in the called FM. > Best performance and less memory consumption, but more programming...

Regards,
Raymond

Re: COEP select optimization

RaymondGiuseppi — Thu, 22 Mar 2012 12:03:10 GMT

Sorry but PACKAGE SIZE (as well as UP TO n ROWS) will have no effect due to the FOR ALL ENTRIES clause, so not memory will be spared. Those options will not be passed to the database system but will be applied instead to the resulting set (So after the memory allocation failure)

Regards,
Raymond

Re: COEP select optimization

HermannGahm — Thu, 22 Mar 2012 13:52:11 GMT

Hi Adi,

this solution (Package size) will not work here.

Kind regards,

Hermann

p.s.: saw Raymonds answer to late...

Re: COEP select optimization

Former Member — Thu, 22 Mar 2012 19:18:39 GMT

Hi Raymond,

OK that's good to know.

Cheers
Adi

Re: COEP select optimization

hardyp180 — Sun, 25 Mar 2012 09:54:46 GMT

Several points

there is no need to specify the client
there is nothing wrong with using FOR ALL ENTRIES, I have done a lot of performance testing and this is not a problematic statement. The flaw in ABAP is that if the select table is empty than a full table scan is done. SAP thinks that documenting this obvious bug is better than fixing it, and so loads of newcomers get caught out every day.
in SAP there is the concept of an "unstable" index, which is one where no date is specified. Your statement is looking to use index 2 on COEP based on OBJNR as the key. You are using GJAHR (year) but because the cost centre is not specified that part of the index is ignored.
If the cost centre is not a known value, you may need to create your own index, based on OBJNR and the year / period. Then you will be laughing.

Cheersy Cheers

Paul

Re: COEP select optimization

Former Member — Mon, 26 Mar 2012 14:53:25 GMT

There are two problems here - the memory allocation and the long run time.

The long run time can be solved quite easily by adding LEDNR to the WHERE. If you check the domain, you'll see that the only possible value is '00'.

No need to create an index on OBJNR.

Rob