Performance during joining large tables

[Former Member]

Hi,

I have to maintain a report which gets data from many large tables as below. Currently it is using join statement to join all 8 tables and causing a very slow performance.

SELECT
      ...
    into corresponding fields of table equip
    FROM caufv
              join afih on afih~aufnr = caufv~aufnr
              join iloa on iloa~iloan = afih~iloan
              join iflos  on iflos~tplnr = iloa~tplnr
              join iflotx on iflos~tplnr = iflotx~tplnr
              join vbak on vbak~aufnr = caufv~aufnr
              join equz on equz~equnr = afih~equnr
              join equi on equi~equnr = equz~equnr
              join vbap on vbak~vbeln = vbap~vbeln
    WHERE
          ...

Please suggest me another way, I'm newbie in ABAP. I tried using FOR ALL ENTRIES IN but it did not work. I would very appreciate if you can leave me some sample lines of code.

Thanks,

[Former Member]

if it_caufv is not initial.

select <fields> from afih into table it_afih for all entries in it_caufv where aufnr = it_caufv-aufnr.

endif.

if it_afih is not initial.

select <fields> from iloa into table it_iloa for all entries in it_afih where iloan = afih-iloan.

endif.

and like wise..

Try doing like this..

[Former Member]

Hi Dear ,

I will suggest you not to use inner join for such i.e. 8 number of table and that too huge tables. Instead use For All entries wherever possible. But before using for all entries check initial for base table and if its not possible to avoid inner join then try to minimise it. Use inner join between header and item.

Hope this will help you to solve your problem . Feel free to ask if you have any doubt.

Regards,

Vijay

[Former Member]

Hi Krishna and Vijay,

Thanks for your suggestions. I created a database view for some main huge tables and using FOR ALL ENTRIES IN for other tables. The performance is now quite better.