Optimise Join Condition

[manukapur]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[manukapur]

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