Skip to Content

Inner Join Performance

Hi

I have the below select statement

Selec a~compcode a~profitcenter b~costcenter into table ztest

from /bic/zabc000 as A inner join /bic/def000 as B

ON A~compcode = b~compcode and A~profitcenter = b~profitcenter

for all entries in table xyz

where A~dateto >= sy-datum and B-costcenter = xyz-costcenter.

Here i have where clause on both table A and B. Table b is populated using table xyz entries.

Table A currently has more than 200000 records and table B also has the same.

How will be the performance of this select or is it better to select the data seperately from both table.

Thanks

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Aug 06, 2016 at 06:26 PM

    Try to create a view on the said tables and then do a SELECT on this view.This would drastically improve the performance. But first see if you have common keys fields to create the view or not...

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 07, 2016 at 09:36 AM

    Depending on xyz table entries too.

    But try break this into multiple select statements, one onA with sy-datum & other between B & xyz. And then join both. Then compare the performance of both.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 07, 2016 at 01:13 PM

    Joins are preferred over individual query.

    Additionally you can place the A~dateto condition in ON clause itself rather than placing it in WHERE clause.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member tom darsy

      Query should be like below, there should be comma after each field such a~compcode , a~profitcenter

      For E.g.

      select perNR from pa0105 INTO TABLE @DATA(IT_PERNR) where usrid = 'XXXXXX'.

      select a~pernr, a~persg, b~nachn into table @ztest from pa0001 as a inner join

      pa0002 as b on a~pernr = b~pernr and a~begda <= @sy-datum for all entries in

      @it_pernr where a~pernr = @it_pernr-pernr.

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.