topic Re: Alternative for Join. in Application Development and Automation Discussions

Alternative for Join.

Former Member — Fri, 27 Oct 2006 13:18:20 GMT

Hi friends,

I am facing problem in breaking up a select statement which contains join between 10 tables.

can you people suggest me different options for removing join in a select statements.

Re: Alternative for Join.

RichHeilman — Fri, 27 Oct 2006 13:21:28 GMT

Sure, instead of joins, use FOR ALL ENTRIES

REgards,

Rich Heilman

Re: Alternative for Join.

Former Member — Fri, 27 Oct 2006 13:22:41 GMT

For all entries.

santhosh

Re: Alternative for Join.

Former Member — Fri, 27 Oct 2006 13:43:44 GMT

Let me take a example for this problem

Lets say there are 8 table

tab1

tab2 and so on..

now my select statement has a join condition as

select (fields) from

tab1

innerjoin tab2 on tab1_{field1 = tab2}field1

innerjoin tab3 on tab3~field3 = tab1-field3

innerjoin tab4 on tab4_{field4 = tab2}field4

innerjoin tab5 on tab5_{field1 = tab1}field1 and

tab5~field5 = tab1-field5

innerjoin tab6 on tab6~field5 = tab5-field5

innerjoin tab7 on tab7~field1 = tab1-field1 and

tab7_{field3 = tab3}field3 and

tab7_{field5 = tab5}field5 and

innerjoin tab8 on tab8_{field8 = tab7}field8

This creaets a huge table from which fields are fetched.

Its a bit difficult to use For all entries in this case.

Can creating views be of any help in this case.

Re: Alternative for Join.

Former Member — Fri, 27 Oct 2006 13:48:35 GMT

What exactly is the problem? IE, why are you doing this? Is there a performance issue here?

Rob

Re: Alternative for Join.

Former Member — Fri, 27 Oct 2006 13:53:02 GMT

Because of these joins this statement is taking a long time to execute. That is why i want it to be done in some other way.

Re: Alternative for Join.

Former Member — Fri, 27 Oct 2006 13:59:29 GMT

It's possible to split up the select, but difficult to say exactly how without seeing the exact code. Could you please post it?

In general, a large join shouldn't cause a performance problem by itself, but if it's not well constructed (IE not using indexes) that can cause a problem.

Rob

Re: Alternative for Join.

Former Member — Fri, 27 Oct 2006 14:04:13 GMT

Hi Rob

here is the actual select statement.

have a look at it and see what can be done with this.

I tried out to break it in two parts, but was unsucessful.

SELECT AFKO~AUFNR "Order Number

AFKO~GSTRP "Order Basic start date

AFKO~GSTRS "Order Scheduled start

AFIH~PRIOK "Maint order Priority

AFIH~ILART "Maint activity type

AFIH~WARPL "Maint plan

AFIH~ABNUM "Package num

AFIH~WAPOS "Maint item

ILOA~TPLNR "Functional location

ILOA~ABCKZ "ABC indicator for technical object

AUFK~KTEXT "Order Short text

AUFK~AUART "Order type

AUFK~OBJNR "object num of order

AFVC~OBJNR "Object num of operation

AFVC~ANLZU "Task list oper's sys condition

CRHD~ARBPL "Work center

AFVV~ARBEI "Plan hours for the activity

AFVV~ISMNW "Actual hours

MPOS~WSTRA "Strategy

AFKO~PLNNR

AFKO~PLNAL

MHIS~ZAEHL "Maint package

FROM ( AFKO "afko: Order header

INNER JOIN AUFK "aufk: Order master data.

ON AUFK_{AUFNR = AFKO}AUFNR

INNER JOIN AFIH "afih: Maint order header

ON AFIH_{AUFNR = AUFK}AUFNR

INNER JOIN ILOA "iloa: PM Obj Loc & Acct Assgmt

ON ILOA_{ILOAN = AFIH}ILOAN

INNER JOIN caufv "

ON caufv_{AUFNR = AFKO}AUFNR

INNER JOIN AFVC "afvc: Operation within an order

ON AFVC_{AUFPL = CAUFV}AUFPL

INNER JOIN AFVV "afvv: qty/dates in operation

ON AFVV_{AUFPL = AFKO}AUFPL

INNER JOIN CRHD "crhd: Work Center Header

ON CRHD_{OBJID = AFVC}ARBID

INNER JOIN PLAS

ON PLAS_{PLNTY = AFVC}PLNTY

AND PLAS_{PLNNR = AFVC}PLNNR

AND PLAS_{PLNAL = AFVC}PLNAL

AND PLAS_{ZAEHL = AFVC}ZAEHL

INNER JOIN PLWP

ON PLWP_{PLNTY = AFVC}PLNTY

AND PLWP_{PLNNR = PLAS}PLNNR

AND PLWP_{PLNAL = PLAS}PLNAL

INNER JOIN MHIS "mhis: Maint plan history

ON MHIS_{ZAEHL = PLWP}PAKET

LEFT OUTER JOIN MPOS "mpos: Maint item

ON MPOS_{WAPOS = AFIH}WAPOS

)

INTO TABLE I_CO_MH3

WHERE AFKO~GSTRS IN S_DATES "Order scheduled start date

AND AFKO~GSTRS IN R_ONE_YEAR "Restriction by year

AND ILOA~TPLNR IN S_TPLNR "func location input

AND CRHD~OBJTY = 'A' "Object types of CIM resource

Re: Alternative for Join.

Former Member — Fri, 27 Oct 2006 14:18:03 GMT

It looks to me that your problem has more to do with the use of indexes rather than the join. Although there is an index on TPLNR in ILOA and OBJTY in CRHD, I don't think they will be restrictive enough to help much.

Rob

Re: Alternative for Join.

Former Member — Fri, 27 Oct 2006 14:27:15 GMT

Hi Rob thanks for the advice.

Can you please elaborate a bit more on this, and how how can we make this work properly.

Thnaks in advance.

Re: Alternative for Join.

sridhar_k1 — Fri, 27 Oct 2006 14:30:07 GMT

YOu've used CAUFV in the join, it's is a view for tables AFKO and AUFK. Take it out from join and use

ON AFVC_{AUFPL = AFKO}AUFPL in the next join condition with AFVC.

Regards

Sridhar

Re: Alternative for Join.

Former Member — Fri, 27 Oct 2006 14:31:19 GMT

Well, I'm not sure I can. I was just trying to get you to find the source of the problem rather than just assuming the join was the sole source. I think you need to examine your logic to determine why you are selecting these fields from these tables using your selection criteria.

Rob