topic Re: Inner join performance in Application Development and Automation Discussions

Inner join performance

Former Member — Thu, 08 May 2008 07:09:39 GMT

Hello,

Could the following program be improved?

types:
  begin of ty_konten,
    bukrs type bukrs,
    saknr type saknr,
  end of ty_konten.
data: it_konten type table of ty_konten.
data: wa_konten type ty_konten.

 
  select skb1~bukrs skb1~saknr
    appending corresponding fields of table it_konten
    from ska1 inner join skb1
      on ska1~saknr = skb1~saknr
    where ska1~ktopl = 'BSV'
      and ska1~xloev = ' '
      and skb1~xloeb = ' '.
  sort it_konten.

Thanks and Regards,

Tommaso

Re: Inner join performance

Former Member — Thu, 08 May 2008 07:21:23 GMT

hi,

do this way ..


  select saknr
    into table it_ska1
    from ska1 
    where ktopl = 'BSV'
      and  xloev = ' '.
 if sy-subrc = 0.
  sort it_ska1.
endif.

 if not it_ska1[] is initial.  
  select bukrs saknr
    into table it_skb1
    from skb1 
    for all entires in it_ska1
    where saknr = it_ska1-saknr
      and  xloeb = ' '.
  if sy-subrc = 0.
   sort it_skb1 by saknr.
  endif.
endif.

loop at it_ska1.
  read table it_skb1 with key saknr = it_ska1-saknr.
  if sy-subrc = 0.
    it_konten-bukrs = it_skb1-bukrs.
    it_konten-saknr = it_skb1-saknr.
    append it_konten.
    clear    it_konten.
  endif.
endloop.

Re: Inner join performance

Former Member — Thu, 08 May 2008 07:30:11 GMT

hi,

u can avoid 'appending corresponding fields'. instead populate into another internal table and then use loop+read to populate in to it_konten table.

i think u can go ahead with the inner join.

regards,

madhumitha

Re: Inner join performance

Former Member — Thu, 08 May 2008 09:54:21 GMT

Thank you both for your hints.

After some performance tests the best solution is the following:

  select skb1~bukrs skb1~saknr
    into table it_konten
    from ska1 inner join skb1
      on ska1~saknr = skb1~saknr
    where ska1~ktopl = 'BSV'
      and ska1~xloev = ' '
      and skb1~xloeb = ' '.
  sort it_konten.

Difficult is to guess the influence of the DB-caching.

Regards,

Tommaso

Re: Inner join performance

Former Member — Thu, 08 May 2008 10:53:05 GMT

There is nothing surprising here. The statement is rather simple you start with a part of SKA1

primary key check xloev = '' and access SKB1 with the index 2.

This join must be faster than any FAE solution!

The into corresponding is unncessary. But it was not a serious overhead.

Siegfried