Inner join taking more time

[Former Member]

Hi Experts,

I have joined two table prps and proj by inner join . But it is taking more time.

pls advice.

the code is:

select prps~posid

prps~post1

prps~objnr

prps~pbukr

prps~prctr

prps~erdat

prps~zz_can

into table itab_prps_all

from proJ inner join prps

on proj_{pspnr = prps}psphi

where prps~posid IN sel_wbs

AND prps~pbukr IN sel_comp

AND prps~prctr IN sel_prct

AND prps~erdat IN sel_date

and proj~pprof NE wa_pprof.

thanks in advance,

SG

[Former Member]

go with subquerries or with alentries

subquerries are most efficient next

are all entries

then iner join

so go with subquerries

[Former Member]

Hi,

try this below code

select pspnr

posid

post1

objnr

pbukr

prctr

erdat

zz_can

from prps

INTO table i_prps

where prps~posid IN sel_wbs

AND pbukr IN sel_comp

AND prctr IN sel_prct

AND erdat IN sel_date.

IF NOT i_prps[ ] IS INITIAL.

select pspnr

posid

pprof

FORM proj

INTO TABLE i_proj

FOR ALL ENTRIES IN i_prps

WHERE pspnr = i_prps-pspnr

AND pprof NE wa_pprof.

ENDIF.

Regards,

Bhupal.

[Former Member]

hi,

plz use FOR ALL ENTRIES IN , it will increase performance of code

example:

Exporting all flight data for a specified departure city. The relevant airlines and flight numbers are first put in an internal table entry_tab, which is evaluated in the WHERE condition of the subsquent SELECT statement.

PARAMETERS p_city TYPE spfli-cityfrom.

TYPES: BEGIN OF entry_tab_type,

carrid TYPE spfli-carrid,

connid TYPE spfli-connid,

END OF entry_tab_type.

DATA: entry_tab TYPE TABLE OF entry_tab_type,

sflight_tab TYPE SORTED TABLE OF sflight

WITH UNIQUE KEY carrid connid fldate.

SELECT carrid connid

FROM spfli

INTO CORRESPONDING FIELDS OF TABLE entry_tab

WHERE cityfrom = p_city.

SELECT carrid connid fldate

FROM sflight

INTO CORRESPONDING FIELDS OF TABLE sflight_tab

FOR ALL ENTRIES IN entry_tab

WHERE carrid = entry_tab-carrid AND

connid = entry_tab-connid.

thanks and regards

rahul sharma

Edited by: RAHUL SHARMA on Sep 18, 2008 7:24 AM

Edited by: RAHUL SHARMA on Sep 18, 2008 7:33 AM

Edited by: RAHUL SHARMA on Sep 18, 2008 7:34 AM

[Former Member]

Hi,

u can use select single sta.

its very efficent way to fetching data form DB.

[naveen_inuganti2]

Hi..,

select prps~posid 
prps~post1
prps~objnr
prps~pbukr
prps~prctr
prps~erdat
prps~zz_can
into table itab_prps_all
from prps inner join proj      <--- change like this 
on proj~pspnr = prps~psphi
where prps~posid IN sel_wbs
AND prps~pbukr IN sel_comp
AND prps~prctr IN sel_prct
AND prps~erdat IN sel_date
and proj~pprof NE wa_pprof.

1. Here declare structure for output internal table itab_prps_all with reuired fields, because you are fetching all fields from

proj table, is it neccessary?

2. If so, check the performance of this by using for all entries method also., in the for all entries method, we can link to table with where

condition of first internal table i.e for all entries table means fetching data by linking tables at application level, But here we are fethcing

the data from both tables by linking them at data base level i.e. with ON condition.. That may effect the performance some times.

Check this blog on this concept,

Thanks,

Naveen.I

Thanks,

Naveen.I

[nelsonkarunakar_darla2]

Hi,

select prps~posid

prps~post1

prps~objnr

prps~pbukr

prps~prctr

prps~erdat

prps~zz_can

into table itab_prps_all

from proJ inner join prps

on proj_{pspnr = prps}psphi

where prps~posid IN sel_wbs

AND prps~pbukr IN sel_comp

AND prps~prctr IN sel_prct

AND prps~erdat IN sel_date

and proj~pprof NE wa_pprof.

in the above select querry as you are fetching the data from only one table that is from PRPS (fields: posid post1 objnr bukr

prctr erdat zz_can ) so i think it is better to use with out inner joins

select posid

post1

objnr

pbukr

prctr

erdat

zz_can

into table itab_prps_all

from proJ

where posid IN sel_wbs

AND pbukr IN sel_comp

AND prctr IN sel_prct

AND erdat IN sel_date

and pprof NE wa_pprof.

Thanks,

Nelson

[Former Member]

hi

never use inner join untill and unless it is very important. rather you can use select for all enteries.

[Former Member]

Hi SG,

Inner joins always create problem, when huge data present in table. You are required to fire two separate select query for both the tables using keyword FOR ALL ENTERIES IN

Query can be modified in following manner:

Define two internal table for proj and prps table respectively.

Select pspnr pprof from proj into table lt_proj

Where some conditions of your requirement.

Select posid post1 objnr pbukr prctr erdat zz_can FROM prps INTO TABLE lt_prps

FOR ALL ENTERIES IN lt_proj

WHERE psphi = pspnr AND conditions of yours requirement.

Now create a internal table which is finally reuired.

append data from both internal table into final one.

Hope this solves your problem.

Regards,

Brajvir