topic Re: Optimizer Consult in Application Development and Automation Discussions

Optimizer Consult

Former Member — Wed, 11 Sep 2013 15:14:12 GMT

Hi friends, i hope you can help me to improve this doubt

CHECK NOT r_objnr[] IS INITIAL.
SELECT cobk~budat coep~objnr coep~wkgbtr coep~mbgbtr coep~meinb
          coep~kstar coep~parob
     INTO CORRESPONDING FIELDS OF TABLE ti_cobk_coep3
     FROM coep
     INNER JOIN cobk ON coep~mandt = cobk~mandt AND coep~kokrs = cobk~kokrs AND coep~belnr = cobk~belnr
    WHERE cobk~budat   IN so_budat AND
          coep~kokrs   EQ 'GAND' AND
          coep~wrttp   EQ '04'    AND
          coep~lednr   EQ '00'    AND
          coep~objnr   IN r_objnr AND
          coep~vrgng   NE 'KOAO'.

It takes some time to execute, How I can optimize the query?, you can use functions or bapis.

I am new to the forum and in abap, if you have any information to improve this report i'll apreciate it.

Best regards

Message was edited by: Matthew Billingham - changed the font as suggested below!

Re: Optimizer Consult

matt — Wed, 11 Sep 2013 16:00:02 GMT

Two things not to do.

1. Don't switch to FOR ALL ENTRIES

2. Don't remove the INTO CORRESPONDING.

Anyone who tells you to do these things is repeating a false myth.

It's impossible for anyone to give a definitive answer, until you tell us what is in r_objnr when you have the bad performance.

Re: Optimizer Consult

Former Member — Wed, 11 Sep 2013 16:52:17 GMT

Welcome to the forum, Brujo!

For starters, you can remove the MANDT join conditions since Open SQL automatically handles the clients.

Secondly I would try rearranging the WHERE conditions. Keep the first as COEP~KOKRS since it is a key field and make the second COEP~OBJNR since it is part of an index. I'm not sure if this is will help though...can anyone confirm or deny this?

Also it would be very helpful on SCN if you change the font of your code to Courier New or something monospaced. It is much more readable.

Best,

Eric

Re: Optimizer Consult

matt — Wed, 11 Sep 2013 16:59:27 GMT

Eric Peterson wrote:
Also it would be very helpful on SCN if you change the font of your code to Courier New or something monospaced.  It is much more readable.
 
Best,
Eric

Good idea. Fixed it for him this time.

Re: Optimizer Consult

Former Member — Wed, 11 Sep 2013 20:44:18 GMT

One more tip: negative expressions is not supported by database indexes, try to avoid "NE" in the WHERE clause

Regards,

Paulo.

Re: Optimizer Consult

Former Member — Wed, 11 Sep 2013 21:17:25 GMT

This is the code:

refresh: r_objnr.
CLEAR:r_objnr.
   r_objnr-sign   = 'I'.
   r_objnr-option = 'CP'.
r_objnr-low    = 'OR*'.
   APPEND r_objnr.

Thank You...

Re: Optimizer Consult

RaymondGiuseppi — Thu, 12 Sep 2013 13:11:54 GMT

I agree, with such wide tables like COEP (and you are reading detail CO item on every orders of your system, can be a huge batch of data) I often get better results by deleting such criteria in the SELECT statement and delete a posteriori records in the filled internal table (*)

Also read (release independant) Note 115219 - Performance improvements for line items

Caution: Line item reporting via large datasets is
         NEVER fast. The data of tables COEP and COBK is often
         not kept in the database cache so that physical
         I/O has to be carried out on the hard disks
         during reporting from the database.
         Thus, it is generally preferable to carry out
         evaluations of large datasets via totals record
         reporting and to only branch to line item
         reporting for interesting sub-areas.
         Rule of thumb: you should not select more than
         1000 records in line item reporting.

Are you actually requiring COEP/COBK information, but could you access COSS/COSP total tables ?

Regards,

Raymond

(*) Actually i received an internal table of CO selection, structure COSEL, and created WHERE criteria for I options and excluding E options for later filter in the internal table