topic Re: Optimise Join Condition in Application Development and Automation Discussions

Optimise Join Condition

manukapur — Thu, 18 Jul 2013 11:05:20 GMT

HI All,

I have written a Select Query for a BW Datasource. The query seems to work fine when the data is less e.g. in Dev system, but when we move to QA it times out because of the Volume of data.

The join is on 5 different tables. Any pointers to optimise the query would helpful

At the moment query is something like

select

t1~objid

t2~objid as objid_m

t2~smlevycat

t3~objid as objid_se

t3~seccat1

t4~begda

t4~peryr

t4~perid

t5~knumh

t5~kschl

t6~kbetr

t6~konwa

* into

* table t_cprice

from hrp1001 as t1

inner join hrp1742 as t2 on t2~objid = t1~sobid and t1~mandt = t2~mandt

inner join hrp1743 as t3 on t3~objid = t1~objid and t3~mandt = t1~mandt

inner join hrp1739 as t4 on t4~objid = t1~objid and t4~mandt = t1~mandt and t4~mandt = t3~mandt

inner join a913 as t5 on t5~kschl = t2~smlevycat and t5~cmsmsecat1 = t3~seccat1 and t5~mandt = t2~mandt and t5~mandt = t3~mandt and t5~cmperid = t4~perid and t5~cmperyr = t4~peryr

inner join konp as t6 on t6~knumh = t5~knumh and t6~mandt = t5~mandt and t6~kschl = t2~smlevycat

where

t1~otype = 'SE' and

t1~sclas = 'SM' and

t1~plvar = '01' and

t2~otype = 'SM' and

t2~plvar = '01' and

t3~otype = 'SE' and

t3~plvar = '01' and

t4~plvar = '01' and

t4~otype = 'SE' and

t5~kappl = 'CM' and

t1~objid in l_r_epid and

t2~objid in l_r_modid and

t2~smlevycat in l_r_scat and

t3~seccat1 in l_r_sect1 and

t4~begda in l_r_begda and

t4~peryr in l_r_ayear and

t4~perid in l_r_asess and

t5~datbi ge sy-datum and "Validity End Date

t5~datab le sy-datum. "Validity Start Date

Thanks,

Manu

Re: Optimise Join Condition

Former Member — Thu, 18 Jul 2013 11:10:29 GMT

Hello Manu,

If you use INNER JOIN more than 3 tables then the performance of the Query will be huge impact.

So split the Query and use FOR ALL ENTRIES IN table so that the performance will be improved.

EX, ITAB1 ,ITAB2, ITAB3, ITAB4, ITAB5.

USE INNER JOIN for first three tables

ITAB1 ITAB2 ITAB3 into target Internal table IT_TEMP .

SORT the internal table IT_TEMP with the keys before using the FOR ALL ENTRIES option.

now use ITAB4 ITAB5 for all entries in the table IT_TEMP into the another table IT_FINAL.

In this way, we can improve the performace of the SELECT Query.

Hope problem solves !!!

Venkat

Re: Optimise Join Condition

Former Member — Thu, 18 Jul 2013 11:34:32 GMT

You are using range tables.

Make sure that range table is not empty, at least for first table t1 (l_r_epid).

t1~objid in l_r_epid  and

Re: Optimise Join Condition

Former Member — Fri, 19 Jul 2013 03:30:19 GMT

Hi Manu,

I would recommend to use t1 , t2 , t3, t4 in one query and if possible making the OBJID always filled and the first clause in Where.

Then use the t5 and t6 in a second query this will help.

Regards

Re: Optimise Join Condition

manukapur — Tue, 23 Jul 2013 09:53:18 GMT

Hi All,

Thanks for your suggestions. I had to break the query. Did a join on t1 , t3 and t4 - one query

another Join on t5 and t6. Individually from t2.

The new fm executes in seconds even on QA.

Thanks for your help.

Regards,

Manu