topic Re: Performance problem in select statement in Application Development and Automation Discussions

Performance problem in select statement

Former Member — Wed, 16 Sep 2009 05:31:40 GMT

Hi,

I am looking at one of the report in my organization due to performance issue.

The report have 2 select statement that look similar, using for all entries of Purch. Req (EBAN), to get PO data (EKKO).

1) First select statement, execution time can be ignored, took less than 0.1 percent of total running time of the program.

SELECT a_{banfn a}bnfpo a_{ebeln a}ebelp b_{konnr b}bedat b~bstyp

FROM eket AS a

INNER JOIN ekko AS b

ON a_{ebeln = b}ebeln

INTO CORRESPONDING FIELDS OF TABLE it_a_intr_data

FOR ALL ENTRIES IN it_eban_data

WHERE banfn = it_eban_data-banfn

AND b~loekz = ''

AND b~bstyp = 'A'.

2) Second select statement, execution time took 81.5% of total running time of the program.

SELECT b_{banfn b}bnfpo a_{ebeln b}ebelp a_{bedat a}bstyp c_{belnr c}vgabe

FROM ( ( ekko AS a

INNER JOIN ekpo AS b ON a_{ebeln = b}ebeln )

LEFT OUTER JOIN ekbe AS c ON b_{ebeln = c}ebeln

AND b_{ebelp = c}ebelp )

INTO CORRESPONDING FIELDS OF TABLE it_k_intr_data

FOR ALL ENTRIES IN it_eban_data

WHERE banfn = it_eban_data-banfn

AND b~loekz = ''

AND ( a~bstyp = 'K'

OR a~bstyp = 'F' ).

It seems like the report used similar selection criteria with the first one, however with totally different result in term of performance. Anyone can highlight what's wrong in the second select statement? Thank you.

Edited by: Abraham Bukit on Sep 16, 2009 1:32 PM

Fix spelling error.

Re: Performance problem in select statement

Former Member — Wed, 16 Sep 2009 07:26:43 GMT

i don't think both statements are similar the second select has a left outer join to EKBE which is a history table so it will contain more records than other two and as per what i have seen the left outer join will be expensive than all entries.

Re: Performance problem in select statement

Former Member — Wed, 16 Sep 2009 08:23:22 GMT

Hi Abraham,

In fact, your first query run fast since you use an index on table EKET (index EKET~E : MANDT / BANFN / BNFPO since MANDT is implicitely used).

But for the second query, you don't have any table which has field BANFN indexed (other fields are not so restricitive)!

Moreover you have a join on 3 tables instead of 2, EKPO is a big table and you have a LEFT OUTER JOIN which is generally more consuming than INNER JOIN.

On another hand, as you have to query including table EKKO, I suggest you to suppress it from the second one and modify your FOR ALL ENTRIES to carry on it_a_intr_data rather than it_eban_data. Like this you can enter on EBELN which would be an indexed field :

SELECT b~banfn b~bnfpo b~ebeln b~ebelp a~bedat a~bstyp c~belnr c~vgabe
  FROM ekpo AS b
  LEFT OUTER JOIN ekbe AS c 
    ON b~ebeln = c~ebeln AND
       b~ebelp = c~ebelp
  INTO CORRESPONDING FIELDS OF TABLE it_k_intr_data
  FOR ALL ENTRIES IN it_a_intr_data
    WHERE ebeln   = it_a_intr_data-ebeln AND
          b~loekz = '' AND
        ( a~bstyp = 'K' OR
          a~bstyp = 'F' ).

Like this you should get the same data and your query should be faster!

Best regards,

Samuel

Re: Performance problem in select statement

Former Member — Wed, 16 Sep 2009 08:30:23 GMT


> Hi Abraham,
> 
> In fact, your first query run fast since you use an index on table EKET (index EKET~E : MANDT / BANFN / BNFPO since MANDT is implicitely used).
> 
> But for the second query, you don't have any table which has field BANFN indexed (other fields are not so restricitive)!
> 
> Moreover you have a join on 3 tables instead of 2, EKPO is a big table and you have a LEFT OUTER JOIN which is generally more consuming than INNER JOIN.
> 
> On another hand, as you have to query including table EKKO, I suggest you to suppress it from the second one and modify your FOR ALL ENTRIES to carry on it_a_intr_data rather than it_eban_data. Like this you can enter on EBELN which would be an indexed field :
> 
> SELECT b~banfn b~bnfpo b~ebeln b~ebelp a~bedat a~bstyp c~belnr c~vgabe
>   FROM ekpo AS b
>   LEFT OUTER JOIN ekbe AS c 
>     ON b~ebeln = c~ebeln AND
>        b~ebelp = c~ebelp
>   INTO CORRESPONDING FIELDS OF TABLE it_k_intr_data
>   FOR ALL ENTRIES IN it_a_intr_data
>     WHERE ebeln   = it_a_intr_data-ebeln AND
>           b~loekz = '' AND
>         ( a~bstyp = 'K' OR
>           a~bstyp = 'F' ).
> 
> Like this you should get the same data and your query should be faster!
> 
> Best regards,
> 
> Samuel

Thanks for pointing that out, I guess I'm a bit tired when reading the code.

I also prefer your suggestion, where technically it is correct.

However, I'm still not sure whether the result will be the same or not.

In current select statement, program use PR number as a key to retrieve all PO related to this PR.

You suggest to use PO number in the PR records to get PO number.

Anyone can confirm whether both has the same result or not? Thanks.

Re: Performance problem in select statement

Former Member — Wed, 16 Sep 2009 08:52:25 GMT

Hi Abraham,

Sorry, you are right, BSTYP is not the same in both cases ('A' for 1st query and 'K'/'F' in the 2nd one).

And perhaps, if your 1st query is faster, it is perhaps also because you have far less PRs than POs in your system...

But in order to you use indexes, you could split your join like this :


* Select all Scheduling Agreement Schedule Lines (EKET)
SELECT banfn bnfpo ebeln ebelp
  FROM eket
  INTO CORRESPONDING FIELDS OF TABLE it_eket
  FOR ALL ENTRIES IN it_eban_data
    WHERE banfn = it_eban_data-banfn.

* Select all purchasing documents
SELECT ebeln konnr bedat bstyp
  FROM ekko
  INTO CORRESPONDING FIELDS OF TABLE it_ekko
  FOR ALL ENTRIES IN it_eket
    WHERE ebeln = it_eket-ebeln AND 
      loekz = '' AND 
      bstyp in ('A', 'F', 'K').

* Split into 2 parts : Purchase Request (BSTYP='A') and Purchase Order / Contracts (BSTYP = 'F' / 'K')
LOOP AT it_ekko ASSIGNING <fs_ekko>.
  IF <fs_ekko>-bstyp = 'A'.
    APPEND <fs_ekko> TO it_ekko_a.
 ELSE.
    APPEND <fs_ekko> TO it_ekko_f_k.
  ENDIF.
ENDLOOP.

* Select items corresponding only to POs
SELECT b~banfn b~bnfpo b~ebeln b~ebelp a~bedat a~bstyp c~belnr c~vgabe
  FROM ekpo AS b
  LEFT OUTER JOIN ekbe AS c 
    ON b~ebeln = c~ebeln AND
       b~ebelp = c~ebelp
  INTO CORRESPONDING FIELDS OF TABLE it_k_intr_data
  FOR ALL ENTRIES IN it_ekko_f_k
    WHERE ebeln   = it_ekko_f_k-ebeln AND
          b~loekz = '' AND
        ( a~bstyp = 'K' OR
          a~bstyp = 'F' ).

Hope it will be correct this time!

Best regards,

Samuel

Re: Performance problem in select statement

Former Member — Wed, 16 Sep 2009 09:10:38 GMT

Hi Samuel,

Your suggestion is actually very good however the problem is Contract document can not be linked to EKET table.

The only key that program can used is BANFN which is currently not included in the index of table EKPO.

I'm planning to create a new index for this table that used BANFN as the key so that my program can run significantly faster.

I'm assessing whether there is critical side affect when I create this index since this report is actually quite important report that will display link from PR until Invoice. Thanks.

Regards,

Abraham

Re: Performance problem in select statement

ThomasZloch — Wed, 16 Sep 2009 11:07:40 GMT

~~Maybe you can include a join on EKET in the second query as well, even if only for the sake of using index EKET~E. Check whether each EKPO has at lease one EKET entry (I believe this is the case).~~

Thomas

P.S. sorry, doesn't work for contract items, as you said already.

Re: Performance problem in select statement

Former Member — Wed, 16 Sep 2009 11:15:23 GMT

Hi,

Just before select statement. Check for all entries internal table is empty or not.

I think it might be empty.

Regards,

Nandha

Re: Performance problem in select statement

Former Member — Wed, 16 Sep 2009 15:26:11 GMT


> Hi,
> 
> Just before select statement. Check for all entries internal table  is empty or not.
> 
> I think it might be empty.
> 
> Regards,
> Nandha

It is not empty. The performance problem happen because select to EKPO using BANFN as the only key as pointed out by Samuel. EKPO does not have any index that make use of BANFN, hence it will loop entire records inside the table. I'm thinking of creating secondary index using BANFN and BNFPO as the non unique key but I'm still in the process of assessing the risk. I need to recheck this decision since it is not delivered as SAP standard even though the requirement is quite make sense, linking PR to RFQ, Contract and PO in a report. Thanks for the response.

Edited by: Abraham Bukit on Sep 16, 2009 11:26 PM

Re: Performance problem in select statement

Former Member — Wed, 16 Sep 2009 15:36:38 GMT

You are already using EKET in your first SELECT. Can you work that into the second one as well? That way you would be able to use its secondary index.

Rob

Re: Performance problem in select statement

Former Member — Wed, 16 Sep 2009 22:21:33 GMT

If it aint broke don't fix it. If the first select takes just 0.1 percent of the total time, it is probably fine. I have concentrated on the second case that takes 81.5% of the total running time. The problem with that select statement is that you are reading table EKPO with BANFN, but there is no index on that particular field. You are effectively doing a full table scan on EKPO and hence the performance issue.

Please implement the following code instead of the second select statement and let me know if your program runs faster.

TYPES: BEGIN OF ty_k_intr_data,
         banfn TYPE ekpo-banfn,
         bnfpo TYPE ekpo-bnfpo,
         ebeln TYPE ekko-ebeln,
         ebelp TYPE ekpo-ebelp,
         bedat TYPE ekko-bedat,
         bstyp TYPE ekko-bstyp,
         belnr TYPE ekbe-belnr,
         vgabe TYPE ekbe-vgabe,
       END OF ty_k_intr_data,

       BEGIN OF ty_ekko_ekpo,
         ebeln TYPE ekpo-ebeln,
         ebelp TYPE ekpo-ebelp,
         banfn TYPE ekpo-banfn,
         bnfpo TYPE ekpo-bnfpo,
         bedat TYPE ekko-bedat,
         bstyp TYPE ekko-bstyp,
       END OF ty_ekko_ekpo,

       BEGIN OF ty_ekbe,
         ebeln TYPE ekbe-ebeln,
         ebelp TYPE ekbe-ebelp,
         zekkn TYPE ekbe-zekkn,
         vgabe TYPE ekbe-vgabe,
         gjahr TYPE ekbe-gjahr,
         belnr TYPE ekbe-belnr,
         buzei TYPE ekbe-buzei,
       END OF ty_ekbe.


DATA: w_k_intr_data    TYPE                 ty_k_intr_data,
      w_ekko_ekpo      TYPE                 ty_ekko_ekpo  ,
      w_ekbe           TYPE                 ty_ekbe       ,

      it_k_intr_data   TYPE        TABLE OF ty_k_intr_data,
      it_ekko_ekpo     TYPE        TABLE OF ty_ekko_ekpo  ,
      it_eban_data_tmp LIKE        TABLE OF it_eban_data  ,
      it_ekbe          TYPE SORTED TABLE OF ty_ekbe
        WITH NON-UNIQUE KEY ebeln ebelp.


IF NOT it_eban_data[] IS INITIAL.

  it_eban_data_tmp[] = it_eban_data[].

  SORT it_eban_data_tmp BY banfn.

  DELETE ADJACENT DUPLICATES FROM it_eban_data_tmp COMPARING banfn.

  SELECT b~ebeln
         b~ebelp
         b~banfn
         b~bnfpo
         c~bedat
         c~bstyp
    FROM       eban AS a
    INNER JOIN ekpo AS b
    ON  a~ebeln EQ b~ebeln
    AND a~ebelp EQ b~ebelp
    INNER JOIN ekko AS c
    ON b~ebeln EQ c~ebeln
    INTO TABLE it_ekko_ekpo
    FOR ALL ENTRIES IN it_eban_data_tmp
    WHERE a~banfn = it_eban_data_tmp-banfn
    AND   b~loekz = space
    AND ( c~bstyp = 'K'
    OR    c~bstyp = 'F' ).

  IF sy-subrc EQ 0.

    SELECT ebeln
           ebelp
           zekkn
           vgabe
           gjahr
           belnr
           buzei
           belnr
           vgabe
      FROM ekbe
      INTO TABLE it_ekbe
      FOR ALL ENTRIES IN it_ekko_ekpo
      WHERE ebeln EQ it_ekko_ekpo-ebeln
      AND   ebelp EQ it_ekko_ekpo-ebelp.


  ENDIF.

ENDIF.

LOOP AT it_ekko_ekpo INTO w_ekko_ekpo.

  w_k_intr_data-banfn = w_ekko_ekpo-banfn.

  w_k_intr_data-bnfpo = w_ekko_ekpo-bnfpo.

  w_k_intr_data-ebeln = w_ekko_ekpo-ebeln.

  w_k_intr_data-ebelp = w_ekko_ekpo-ebelp.

  w_k_intr_data-bedat = w_ekko_ekpo-bedat.

  w_k_intr_data-bstyp = w_ekko_ekpo-bstyp.


  READ TABLE it_ekbe WITH KEY ebeln = w_ekko_ekpo-ebeln
                              ebelp = w_ekko_ekpo-ebelp
                              TRANSPORTING NO FIELDS.

  IF sy-subrc EQ 0.

    LOOP AT it_ekbe INTO w_ekbe
      WHERE ebeln EQ w_ekko_ekpo-ebeln
      AND   ebelp EQ w_ekko_ekpo-ebelp.

      w_k_intr_data-belnr = w_ekbe-belnr.

      w_k_intr_data-vgabe = w_ekbe-vgabe.

      APPEND w_k_intr_data TO it_k_intr_data.

      CLEAR  w_k_intr_data.

    ENDLOOP.

  ELSE.

    APPEND w_k_intr_data TO it_k_intr_data.

  ENDIF.

ENDLOOP.

Edited by: Mark Christian on Sep 16, 2009 6:22 PM

The code functionality is not working again I see. I wonder how SAP can keep getting the same thing wrong so often? Please note that when ever SAP corrects this error again my post will appear legible.

Re: Performance problem in select statement

Former Member — Wed, 16 Sep 2009 22:49:05 GMT

Hi Rob,

Why does the code paste functionality fail so often (it has happened before and was fixed later)? I spent 20 minutes creating a solution and the message board software doesn't co-operate. Just take a look at my post. Believe you me it has the entire code in it but I doubt the asker will be able to read it at all.

Could you get somebody to take a look at this please?

Thanks.

Regards,

Mark

It's actually a "design feature". There is a 2.500 character limit to posts. I believe this is referred to in one of the stickies at the top opf the forum. So try to post only small snippets of code.

Rob

Edited by: Rob Burbank on Sep 17, 2009 9:04 AM

Re: Performance problem in select statement

Former Member — Thu, 17 Sep 2009 01:14:00 GMT


> Hi Rob,
> 
> Why does the code paste functionality fail so often (it has happened before and was fixed later)? I spent 20 minutes creating a solution and the message board software doesn't co-operate. Just take a look at my post. Believe you me it has the entire code in it but I doubt the asker will be able to read it at all.
> 
> Could you get somebody to take a look at this please?
> 
> Thanks.
> 
> Regards,
> Mark

Hi Mark,

Don't worry, I can read your code since it is forwarded to my e-mail.

I will check and come back with the result soon. Thanks.

Edited by: Abraham Bukit on Sep 17, 2009 9:14 AM

I just found out that 'email' is forbidden word

Re: Performance problem in select statement

Former Member — Thu, 17 Sep 2009 01:16:04 GMT


> You are already using EKET in your first SELECT. Can you work that into the second one as well? That way you would be able to use its secondary index.
> 
> Rob

That's my initial plan Rob, until I found out that Contract does not have data in EKET table. Thanks for the suggestion.

Yes and after I read the other posts I realized that as well. Sorry.

Rob

Edited by: Rob Burbank on Sep 17, 2009 9:02 AM

Re: Performance problem in select statement

Former Member — Thu, 17 Sep 2009 01:28:13 GMT

Hi Mark,

Based on your suggestion, if I'm using this select statement:

SELECT b~ebeln

b~ebelp

b~banfn

b~bnfpo

c~bedat

c~bstyp

FROM eban AS a

INNER JOIN ekpo AS b

ON a_{ebeln EQ b}ebeln

AND a_{ebelp EQ b}ebelp

INNER JOIN ekko AS c

ON b_{ebeln EQ c}ebeln

INTO TABLE it_ekko_ekpo

FOR ALL ENTRIES IN it_eban_data_tmp

WHERE a~banfn = it_eban_data_tmp-banfn

AND b~loekz = space

AND ( c~bstyp = 'K'

OR c~bstyp = 'F' ).

I will not get data for contract (bstyp = 'K') because it is not linked by EBAN-EBELN.

EBAN-EBELN field only contain PO number (bstyp = 'F').

Hence, I will still need to select to EKPO table using BANFN as the key for contract data and eventually will have same problem because it will scan the entire EKPO table. Thanks for the suggestion.

Re: Performance problem in select statement

Former Member — Tue, 02 Feb 2010 05:50:47 GMT

In the end, I create the index to speed things up. No further issue until today. Thanks everyone.