topic Re: Performance during joining large tables in Application Development and Automation Discussions

Performance during joining large tables

Former Member — Wed, 27 May 2009 10:31:28 GMT

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,

Re: Performance during joining large tables

Former Member — Wed, 27 May 2009 10:38:45 GMT

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..

Re: Performance during joining large tables

Former Member — Wed, 27 May 2009 10:41:12 GMT

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

Re: Performance during joining large tables

Former Member — Mon, 01 Jun 2009 02:34:01 GMT

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.