Performance Issue

[Former Member]

Hi,

I am facing peroformane issue with faglflexa table select statement,.

In table there is standard index 3 'Index using BSEG key' ( index fields Sequence : RCLNT BELNR RBUKRS GJAHR BUZEI RLDNR DOCLN ), but i have only few index fields match with the BSID table..

Please help to correct if the below select statement is incorrect in performance point of view.

SELECT ryear docnr rbukrs buzei prctr hsl segment INTO TABLE it_faglflexa

FROM faglflexa

FOR ALL ENTRIES IN it_bsid

WHERE docnr EQ it_bsid-belnr

AND rbukrs EQ it_bsid-bukrs

AND gjahr EQ it_bsid-gjahr

AND ryear EQ it_bsid-gjahr

AND buzei EQ it_bsid-buzei.

Also i am trying to read the above internal table as below to fetch segment field from it_faglflexa , please correct if i am doing wrong.

it_output contains bsid data.

Loop at it_ouput into wa_output.

IF wa_output-profit_cntr IS INITIAL.

READ TABLE it_faglflexa into ls_faglflexa with key rbukrs = wa_output-bukrs

docnr = wa_output-document_no

ryear = wa_output-year

buzei = wa_output-buzei.

if sy-subrc eq 0.

wa_output-segment = ls_faglflexa-segment.

endif.

endif.

endloop.

Thanks

Sireesha

Edited by: Sireesha_SAP on Nov 4, 2011 3:21 AM

Edited by: Sireesha_SAP on Nov 4, 2011 3:24 AM

Edited by: Sireesha_SAP on Nov 4, 2011 3:25 AM

[Former Member]

Any reply Please

Thanks

Sireesha

[Former Member]

Sireesha,

There are 2 separate fields in table - DOCNR and BELNR (values are same though).

Index you want to use is having BELNR in it, but you are using DOCNR in your select. Changing it to BELNR should help. Still, I think that your query is using most of the fields in the primary key and should be fast enough.

Also, check that if you have a fixed value(s) of RLDNR, include it is query.

I hope you take care that it_bsid is not blank before you use FAE, also make sure that it_bsid is not having any row with blank belnr value.

Sort it_faglflexa table and use BINARY SEARCH in READ, it will help a lot if data in itab is large. Search abt BINARY SEARCH if you have any doubts in usage.

If the problem still persists, pls share the volume of data and time stats.

Regards,

Diwakar

[Former Member]

Diwakar,

Thanks for your help.

I need some more help.

If I use primary key in my WHERE selection should i follow field sequence.

I need all 3 fixed values ( L0, YS, YQ ) from RLDNR field, should i fill it in range and pass in WHERE selection or can i ignore RLDNR in WHERE selection..

Please help...

Thanks

Sireesha

[Clemenss]

Hi Sireesha_SAP,

probably BSID table has many entries.

SAP has a very bad default value for the package size with FOR ALL ENTRIES. I remember an interesting blog about that (search!). You could ask for change of system parameter or override with ORACLE HINT.

Regards

Clemens

Check Here: [SAP Support case "FOR ALL ENTRIES disaster|]

Edited by: Clemens Li on Nov 5, 2011 11:29 PM

[Former Member]

Thanks Clemens. I never used ORACLE HINT..

Can you please suggest, if I can use ORACLE HINT in DB2.

Thanks

Sireesha

[Former Member]

Hi Sireesha,

One of the most under used, yet which gives considerable better performance is use of <Field Symbol>.

Make a field symbol of your internal table type (or type ANY (generic)) and also declare Work Area of same type and use it while reading the table. What this does is, it access data through reference, hence quicker response.

Please go thru the document:

http://help.sap.com/SAPHELP_NW04s/helpdata/EN/fc/eb387a358411d1829f0000e829fbfe/frameset.htm

Hope, you find this useful.

Regards,

Reetesh

[deepak_dhamat]

Hi ,

SELECT ryear docnr rbukrs buzei prctr hsl segment INTO TABLE it_faglflexa
FROM faglflexa
FOR ALL ENTRIES IN it_bsid
WHERE docnr EQ it_bsid-belnr
AND rbukrs EQ it_bsid-bukrs
AND gjahr EQ it_bsid-gjahr
AND ryear EQ it_bsid-gjahr
AND buzei EQ it_bsid-buzei
%_hints oracle 'index(FAGLFLEXA"3")'.

For using index

regards

Deepak.

[Former Member]

Hi Sireesha,

For peroformane you follow the below points.

1. Check for initialization. i.e If it_bsid is not initial.

2. Select the table field with sequence and passing fields in where also in sequence. i.e

SELECT ryear docnr rbukrs prctr segment hsl buzei INTO TABLE it_faglflexa

FROM faglflexa

FOR ALL ENTRIES IN it_bsid

WHERE ryear EQ it_bsid-gjahr

AND docnr EQ it_bsid-belnr

AND rbukrs EQ it_bsid-bukrs

AND gjahr EQ it_bsid-gjahr

AND buzei EQ it_bsid-buzei.

You apply the above code. It wiil definitly increase the performance.

[Former Member]

The SELECT looks pretty well optimized. As mentioned earlier, look at the READ statement. It's done inside a loop, so unless you are using sorted or hashed tables it is a nested LOOP. This is the first place to start looking - not the SELECT.

Rob

[Former Member]

1. Check if IT_BSID is initial.

2. Try to get RLDNR in the query if possible. The rest of the query looks fine.

3. Sort and do the Binary search on read statement if possible within the loop.

I would advise against using oracle hint - as it makes query very rigid and might cause issues in production.

Mark this thread closed if your query is resolved.