Optimise Code

[Former Member]

Hi,

Please optimise below code :

Code :

SELECT z_reference~bp_number

z_reference~profile

but000~partner_guid

FROM z_reference

INNER JOIN but000

ON but000_{partner = z_reference}bp_number

INNER JOIN y_projectinfo

ON y_projectinfo_{bp_number = z_reference}bp_number

INNER JOIN yrms_collateral

ON yrms_collateral_{bp_number = z_reference}bp_number

INNER JOIN crmm_but_frg0041

ON crmm_but_frg0041_{partner_guid = but000}partner_guid

INNER JOIN but021_fs

ON but021_fs_{partner = but000}partner

INNER JOIN adrc

ON adrc_{addrnumber = but021_fs}addrnumber

INNER JOIN but0is

ON but0is_{partner = z_reference}bp_number

INNER JOIN z_ref_profile

ON z_ref_profile_{profile = z_reference}profile

INNER JOIN z_refactivity

ON z_refactivity_{profile = z_reference}profile

INTO CORRESPONDING FIELDS OF TABLE lt_results

WHERE but000~xdele = space

and but000~xblck = space

and but000~not_released = space

AND but000~mc_name1 LIKE ps_sel_mc_name1

AND adrc~country IN pt_sel_country

AND adrc~region IN pt_sel_region

AND but021_fs~valid_from <= lv_time_stamp

AND but021_fs~valid_to >= lv_time_stamp

AND but021_fs~adr_kind = zcl_bupa_utilities=>c_xxdefault

AND crmm_but_frg0041~attrib_3 IN pt_sel_mastercode

AND z_reference~conf_ref IN pt_sel_con_ref

AND z_reference~bp_number IN pt_sel_bp_number

AND z_reference~ramp_up IN pt_sel_ramp_up

AND z_reference~status IN pt_sel_ref_status

AND z_reference~non_live IN pt_sel_non_live

AND but000~zz_segment IN pt_sel_market_segment

AND but000~type IN pt_sel_bp_type

AND but0is~istype IN pt_sel_istype

AND but0is~ind_sector IN pt_sel_ind_sector

AND z_ref_profile~child_id IN pt_sel_node

AND z_refactivity~ref_activity IN pt_sel_ref_activity

AND y_projectinfo~proj_sys_rpl1 IN pt_sel_proj_sys_rpl1

AND y_projectinfo~proj_sys_rpl2 IN pt_sel_proj_sys_rpl2

AND y_projectinfo~proj_sys_rpl3 IN pt_sel_proj_sys_rpl3

AND y_projectinfo~proj_compete1 IN pt_sel_proj_compete1

AND y_projectinfo~proj_compete2 IN pt_sel_proj_compete2

AND y_projectinfo~proj_compete3 IN pt_sel_proj_compete3

AND y_projectinfo~proj_countries IN pt_sel_proj_countries

AND y_projectinfo~proj_length IN pt_sel_proj_length

AND y_projectinfo~proj_man_days IN pt_sel_proj_man_days

AND y_projectinfo~proj_snglinstall IN pt_sel_proj_snglinstall

AND y_projectinfo~proj_roll_out IN pt_sel_proj_roll_out

AND y_collateral~coll_type IN pt_sel_coll_type

AND y_collateral~language IN pt_sel_coll_langu

.

[Former Member]

There are a number of your own tables here. It's pretty difficult for theforum to give much help in this case.

But the explain function of ST05 may help you.

Rob

[varun_maharshi]

Never use more than 3 inner joins will be my motto

[Former Member]

Varun - why not? SAP has views (that are implemented by JOINS) on many tables.

Rob

[Former Member]

This code can not be optimized in general.

There are so many IN condition which are probably very often not filled that there is no way to optimize it.

You must check you main selects:

+ this and this and that IN filled what happens, this is then one statement

+ if other IN conditions are filled then you get a completely different statement

Sometimes the database optimizer will find the right way in which order it should access the tables and which indexes it should.

Sometimes it will not.

Siegfried

[Sultanuddin]

Hi Sanju,

Go for FOR ALL ENTRIES instead of inner joins, and also see that all the selection criteria is filled.

This would optimize your code to a great extent.

---

Reward points if found useful

[Former Member]

Hi Sanju,

You have a condition that not match with the join clause.

AND y_collateral~coll_type IN pt_sel_coll_type
AND y_collateral~language IN pt_sel_coll_langu

have you try to use this code using loop at...where adr_kind <> zcl_bupa_utilities=>c_xxdefault after the select to delete records.

AND but021_fs  ~adr_kind   = zcl_bupa_utilities=>c_xxdefault