Skip to Content
author's profile photo Former Member
Former Member

INNER JOIN VS FAE for ESSR, EKKO, EKPO, EKBE. It BAFFLED me!

Guys i have a problem when doing an improvement for this piece of sql statement.

SELECT ekbeebeln ekbeebelp ekbevgabe ekbegjahr

ekbebelnr ekbebuzei ekbebwart ekbebudat

ekbelfbnr ekbeernam ekkoekgrp ekkobukrs ekpo~txz01

ekko~ekorg

INTO TABLE gt_i_ekbe FROM ( ( ekbe

JOIN essr ON ekbelfbnr = essrlblni

JOIN ekko ON ekbeebeln = ekkoebeln )

JOIN ekpo ON ekbeebeln = ekpoebeln

AND ekbeebelp = ekpoebelp )

WHERE ekbe~ebeln IN s_ebeln

AND ekbe~vgabe IN (c_vgabe1,c_vgabe2)

AND essr~ernam IN s_usnam

AND essr~erdat IN s_budat

AND ekko~bstyp EQ c_bstyp

AND ekko~ekgrp IN s_ekgrp

AND ekko~bsart IN s_bsart

AND ekko~loekz EQ space

AND ekpo~pstyp EQ c_pstyp

AND ekpo~loekz EQ space .

I tried to separate the ESSR table from the join and using For all entries like below:

SELECT lblni FROM essr INTO TABLE lt_essr

WHERE essr~ernam IN s_usnam

AND essr~erdat IN s_budat.

IF lt_essr IS NOT INITIAL.

SELECT ekbeebeln ekbeebelp ekbevgabe ekbegjahr

ekbebelnr ekbebuzei ekbebwart ekbebudat

ekbelfbnr ekbeernam ekkoekgrp ekkobukrs ekpo~txz01

ekkoekorg ekbezekkn

INTO TABLE gt_i_ekbe FROM ( ( ekbe

JOIN ekko ON ekbeebeln = ekkoebeln )

JOIN ekpo ON ekbeebeln = ekpoebeln

AND ekbeebelp = ekpoebelp )

FOR ALL entries IN lt_essr

WHERE ekbe~lfbnr = lt_essr-lblni

AND ekbe~ebeln IN s_ebeln

AND ekbe~vgabe IN (c_vgabe1,c_vgabe2)

AND ekko~bstyp EQ c_bstyp

AND ekko~ekgrp IN s_ekgrp

AND ekko~bsart IN s_bsart

AND ekko~loekz EQ space

AND ekpo~pstyp EQ c_pstyp

AND ekpo~loekz EQ space .

ENDIF.

We are in ECC6.

Looks like the performance become very fast DEPENDS on the no of entries in the lt_essr. If it less 3000 records(more or less), it gives very good performance up to 99%(some cases give ridicioulus improvement when the inner join statement had timeout but the FAE give 3 seconds runtime). But if its more than that the INNER JOIN performance better about 50% better even after we have used the oracle hint and set the max_in_blocking_factor parameter become 1000(default is 5) still the inner join is better. Anybody can explain this? It really baffled me and i cant sleep tightly every night because of this.

Another question is, does it sounds silly if i want to get the best of both so i just do a checking if the lt_essr entries is less than 3000 records then the FAE logi is applied and if its more 3000 then the inner join logic is applied?

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Aug 24, 2011 at 09:06 AM

    Hi,

    why not remove the

    FOR ALL entries IN lt_essr

    WHERE ekbe~lfbnr = lt_essr-lblni

    from the select and delete unwanted entries afterwards, like:

    SELECT lblni FROM essr INTO TABLE lt_essr

    WHERE essr~ernam IN s_usnam

    AND essr~erdat IN s_budat.

    <put lfbnr into range here>

    IF lt_essr IS NOT INITIAL.

    SELECT ekbeebeln ekbeebelp ekbevgabe ekbegjahr

    ekbebelnr ekbebuzei ekbebwart ekbebudat

    ekbelfbnr ekbeernam ekkoekgrp ekkobukrs ekpo~txz01

    ekkoekorg ekbezekkn

    INTO TABLE gt_i_ekbe FROM ( ( ekbe

    JOIN ekko ON ekbeebeln = ekkoebeln )

    JOIN ekpo ON ekbeebeln = ekpoebeln

    AND ekbeebelp = ekpoebelp )

    WHERE ekbe~ebeln IN s_ebeln

    AND ekbe~vgabe IN (c_vgabe1,c_vgabe2)

    AND ekko~bstyp EQ c_bstyp

    AND ekko~ekgrp IN s_ekgrp

    AND ekko~bsart IN s_bsart

    AND ekko~loekz EQ space

    AND ekpo~pstyp EQ c_pstyp

    AND ekpo~loekz EQ space .

    ENDIF.

    DELETE gt_i_ekbe WHERE NOT lfbnr IN lra_essr.

    Roy

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 06, 2011 at 06:36 AM

    Hi,

    As said , first select ESSR table entries then using for all entries select EKKO and EKPO wih join, then using using for all entries select EKBE.

    1. ESSR

    2. EKKO, EKPO.

    3. EKBE.

    Regards,

    Ravi.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 06, 2011 at 11:43 AM

    Why don't you simply provide us with what Volker was asking for: Explain for the original join.

    Actually the database should be capable of selectingthe right execution path based on the input criteria for the tables.

    Volker, what do you think about using histograms with corresponding hint?

    This might help Oracle to select the right execution plan much better.

    Add a comment
    10|10000 characters needed characters exceeded

    • Why don't you simply provide us with what Volker was asking for: Explain for the original join.

      > Actually the database should be capable of selectingthe right execution path based on the input criteria for the tables.

      >

      > Volker, what do you think about using histograms with corresponding hint?

      > This might help Oracle to select the right execution plan much better.

      Hi,

      I'd rather check first, what the plan says.

      Then I'd check what the statsvalues are like.

      Then I'd consider to check out a "manually" selected historgram (group by ...) of the values in charge,

      esp. to find out, if your have i.e. 90:10 distributions.

      Then I'd force an execution choosing the desired index using sqlplus and verify it is really faster.

      If this comes out OK -> calculate Histogramms and check if the optimizer goes for the desired index without hint.

      If that works out OK -> You can change ABAP to use the literal substitution, but only for this statement.

      Changing ABAP code first hand to force a specific executionplan on the DB,

      without even knowing what the actual access plan is like sounds a bit difficult to me.

      Volker

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.