topic Re: Performance on Join Statement in Application Development and Automation Discussions

Performance on Join Statement

Former Member — Fri, 06 Oct 2006 06:29:24 GMT

Hi,

Can anyone let me know what exactly the problem in performance. Its taking almost 5 min to execute the below join statement. If anything needs to be modified then pls do let me know.

IF installation_search-installation_old_name IS NOT INITIAL.

SELECT tplnr

zzinst_old_name

erdat

zzzeit

FROM zcs_inst_names

INTO TABLE i_old_name.

SORT i_old_name BY tplnr date DESCENDING time

DESCENDING.

<b>SELECT a~tplnr

a~tplma

a~objnr

b~zzinst_old_name

b~erdat

b~zzzeit

FROM iflot AS a JOIN zcs_inst_names AS b

ON a_{tplnr = b}tplnr

INTO TABLE i_iflot

FOR ALL ENTRIES IN i_old_name

WHERE a~tplnr = i_old_name-tplnr AND

a~begru <> 'Z001'.</b>

LOOP AT i_iflot INTO w_iflot.

TRANSLATE w_iflot-old_name TO UPPER CASE.

IF NOT w_iflot-old_name IN r_zzinst_old_name.

DELETE i_iflot INDEX sy-tabix.

ENDIF.

ENDLOOP.

ENDIF.

Regards,

Ram

Re: Performance on Join Statement

Former Member — Fri, 06 Oct 2006 06:32:21 GMT

IF NOT i_old_name[] IS INITAIL.

SELECT a~tplnr

a~tplma

a~objnr

b~zzinst_old_name

b~erdat

b~zzzeit

FROM iflot AS a INNER JOIN zcs_inst_names AS b

ON a_{tplnr = b}tplnr

INTO TABLE i_iflot

FOR ALL ENTRIES IN i_old_name

WHERE a~tplnr = i_old_name-tplnr AND

a~begru <> 'Z001'.

ENDIF.

Re: Performance on Join Statement

gopi_narendra — Fri, 06 Oct 2006 06:33:54 GMT

just to add to kishen

sort i_old_name by <KEY>.

if not i_old_name[] is initial.

.......

Regards

- Gopi

Re: Performance on Join Statement

Former Member — Fri, 06 Oct 2006 06:55:11 GMT

Hi,

Even though i have given IF NOT i_old_name[] IS INITAIL.

statement its taking so much time to execute the join statement. Anything needs to be changed in the select statement.

Regards,

Ram

Re: Performance on Join Statement

Former Member — Fri, 06 Oct 2006 07:00:19 GMT

addition to above

Either u can use to select statments to get the values in two internal tables and then merge it.

If u have few values for BEGRU then try using

WHERE a~tplnr = i_old_name-tplnr AND

a~begru IN (.. , .. ,.. ).

Try using INTO Corresponding fileds of table ITAB

Regards

Sachin

Message was edited by: Sachin Dhingra

Re: Performance on Join Statement

Former Member — Fri, 06 Oct 2006 07:05:41 GMT

hey ,

try to put as many where conditions as possible

u have selection screen parameters which matches with that table palce it in the where condition

take the key fields of he table u r featching the data

if it is unnecessary also man so that the data retrival will be faster

Re: Performance on Join Statement

Former Member — Fri, 06 Oct 2006 11:18:33 GMT

Hi,

Try using INTO CORRESPONDING FIELDS OF TABLE I_old_name instead of using INTO TABLE i_old_name.

Regards,

vasantha.

Re: Performance on Join Statement

Former Member — Fri, 06 Oct 2006 13:24:52 GMT

You are joining on zcs_inst_names-tplnr. Is this the leftmost field of an index? If not, that's the cause of the performance issue.

Rob