topic Re: Inner join in Application Development and Automation Discussions

Inner join

Former Member — Mon, 07 Jan 2008 06:57:41 GMT

Hi All,

I have a problem with innerjoin...whenever i m writing innerjoin our server getting slow.if i stop it ...it is working fine..is there any problm with innerjoin...does writing wrong join effect sys performance..?

Regards

Sameer

Re: Inner join

Former Member — Mon, 07 Jan 2008 07:00:06 GMT

Hi ,

for that there r more reasons r there ,

1. in u r inner join more tables u have used

2. in u inner join u used master tables (more data )

3. try to use For all entries logic

THX

Re: Inner join

Former Member — Mon, 07 Jan 2008 07:01:14 GMT

Hi, give me your code which u use for inner join. Most cases is u may use lot of tables in that.

Suku

Re: Inner join

Former Member — Mon, 07 Jan 2008 07:03:18 GMT

HI,

if u r not writing the correct join you will not get wanted records.

Join the columns carrname, connid, fldate of the database tables scarr, spfli and sflight by means of two inner joins. A list is created of the flights from p_cityfr to p_cityto. Alternative names are used for every table.

PARAMETERS: p_cityfr TYPE spfli-cityfrom,

p_cityto TYPE spfli-cityto.

DATA: BEGIN OF wa,

fldate TYPE sflight-fldate,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY fldate carrname connid.

SELECT c_{carrname p}connid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

JOIN spfli AS p ON p_{carrid = c}carrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

JOIN sflight AS f ON f_{carrid = p}carrid

AND f_{connid = p}connid ).

LOOP AT itab INTO wa.

WRITE: / wa-fldate, wa-carrname, wa-connid.

ENDLOOP.

Plzz reward points if it helps.

Re: Inner join

Former Member — Mon, 07 Jan 2008 07:11:14 GMT

Hi,

For performance issue check the following link:

http://www.sapbrainsonline.com/ARTICLES/TECHNICAL/optimization/optimization.html

Regards,

Bhaskar

Re: Inner join

Former Member — Mon, 07 Jan 2008 07:11:46 GMT

Thank u for ur reply.....

Finally i came to one conclusion that if we writes wrong code

in inner joins if effects the system performance..then and there itself..In my case i did a small mistake and our server CPU usage drastically increased..

Please correct me if i m wrong..

my wrong code was.

select a~ebeln

a~bedat

a~lifnr

b~ebelp

b~txz01

b~matnr

b~netwr

b~pstyp

into corresponding fields of table it_ekko

from ekko as a inner join ekpo as b

on a_{ekko-ebeln = b}ekpo-ebeln

where ebeln in s_ebeln

and bedat in s_bedat

and werks in s_werks.

i corrected it as below.

select a~ebeln

a~bedat

a~lifnr

b~ebelp

b~txz01

b~matnr

b~netwr

b~pstyp

into corresponding fields of table it_ekko

from ekko as a inner join ekpo as b

on a_{ebeln = b}ebeln

where a~ebeln in s_ebeln

and a~bedat in s_bedat

and b~werks in s_werks.

thanx.

Re: Inner join

arpit_shah — Mon, 07 Jan 2008 07:17:32 GMT

hi..sameer..

try to use for all enteries ,

if ur inner join is not working ,

e.g

i m giving one example how to use for all entries.

select l_{vbeln l}posnr l_{vgbel l}matnr t_{maktx l}charg l_{lfimg l}umvkz l_{meins l}vrkme

k_{wadat_ist k}kunag k_{kunnr n}name2 " l~pstyv

into corresponding fields of table atab

from ( lips as l

inner join likp as k on l_{vbeln = k}vbeln

inner join kna1 as n on n_{kunnr = k}kunnr

inner join makt as t on t_{matnr = l}matnr )

where ( l~vbeln in dno

and wadat_ist in agi

and charg in batch

and posnr like '9%'

and l~matnr in mt ).

if sy-subrc eq 0.

select k_{vbeln k}stceg p_{vgbel p}vgpos "k_{kunag "p}vbeln

into corresponding fields of table aatab

from vbrk as k

inner join vbrp as p on k_{vbeln = p}vbeln

for all entries in atab

where "k~fkdat in date

k_{vbtyp = 'U' and p}vgtyp = 'J'

and p~vgbel = atab-vbeln.

endif.

select belnr gjahr budat

into corresponding fields of table t_head_expense

from bkpf

where budat in posdate

select belnr buzei gjahr hkont dmbtr shkzg sgtxt

into corresponding fields of table t_expense

from bseg

for all entries in t_head_expense

where belnr = t_head_expense-belnr and gjahr = t_head_expense-gjahr.

i hope it is helpful to u.

Regards,

Arpit