alternative for inner joins

[Former Member]

Hi,

please check this code and suggest me of an alternative for this performance wise.

SELECT b~partner

APPENDING CORRESPONDING FIELDS OF

TABLE t_db_pos_match

FROM adrc AS a

JOIN but020 AS b

ON a_{addrnumber = b}addrnumber

JOIN but000 AS c

ON b_{partner = c}partner

WHERE c~partner NE t_bp_obj-act-ekun-partner

AND c~mc_name1 = t_bp_obj-act-ekun-name_last

AND a~city1 = wa-city1

AND a~post_code1 = wa-post_code1

AND a~po_box = wa-po_box

AND a~street = wa-street

AND a~house_num1 = wa-house_num1

AND a~house_num2 = wa-house_num2

AND a~region = wa-region

AND a~addr_group = 'BP'.

awaiting your reply.

Binay.

[Former Member]

Checking for 'NE' on the primary key field will kill your performance. For a start, remove

WHERE c~partner NE t_bp_obj-act-ekun-partner

and check the condition after the SELECT.

Rob

[Former Member]

Hi Shankar,

Use FOR ALL ENTRIES option instead of INNER JOINS. Inner Joins more than two levels will have great impact on performance.

Check this code.

SELECT ADRNR

FROM ADRC

INTO TABLE IT_ADRC

WHERE city1 = wa-city1

AND Post_code1 = wa-post_code1

AND po_box = wa-po_box

AND street = wa-street

AND house_num1 = wa-house_num1

AND house_num2 = wa-house_num2

AND region = wa-region

AND addr_group = 'BP'.

IF IT_ADRC[] IS NOT INITIAL.

SELECT PARTNER

FROM BUT020

INTO TABLE IT_BUT020

FOR ALL ENTRIES IN IT_ADRC

WHERE PARTNER = IT_ADRC-PARTNER.

IF IT_BUT020[] IS NOT INITIAL.

SELECT PARTNER

FROM BUT000

APPENDING CORRESPONDING FIELDS OF

TABLE t_db_pos_match

FOR ALL ENTRIES IN IT_BUT020

WHERE PARTNER = IT_BUT020-PARTNER

AND partner <> t_bp_obj-act-ekun-partner

AND mc_name1 = t_bp_obj-act-ekun-name_last.

ENDIF.

ENDIF.

Thanks,

Vinay

[Former Member]

Hi,

First select partner and addrnumber by joining BUT000 & BUT020 for all the entries in t_bp_obj-act using the condition

BUT000~partner NE t_bp_obj-act-ekun-partner

AND BUT000~mc_name1 = t_bp_obj-act-ekun-name_last

Store the data in say it_partner.

Now for all the entries in it_partner get the matching data from ADRC and store in it_adrc using the condition

adrc-adrnr = it_partner-addrnumber and rest of the condition in yiur query.

Now sort it_adrc by adrnr.

Loop at it_partner.

read the matching record in it_adrc - use bunary search

if sy-subrc = 0.

append it to t_db_pos_match

endif.

endloop.

-Regards

Ashim

[former_member194613]

There is no general coding alternative, the only thing which will help you is the SQL Trace, you must trace your statement. Then you must check

+ the runtime of the statement, the number of records and the minimal time per

record. The statement is in principle o.k., if the minimal time per record is less

than 10.000 microseconds.

+ Then check then SQL explain, which index on the three tables are used, and

which fields. What is the order in which the tables are accessed.

Would an alternative index on one of the tables help?

Siegfried

[Former Member]

used for all entries.

[Former Member]

hi shankar,

i codded for you . i would be correct you codded correct but you made only one mistake that is you want to code as <b>INNER JOIN</b>

SELECT b~partner

APPENDING CORRESPONDING FIELDS OF

TABLE t_db_pos_match

FROM adrc AS a

INNER JOIN but020 AS b

ON a_{addrnumber = b}addrnumber

INNER JOIN but000 AS c

ON b_{partner = c}partner

WHERE c~partner NE t_bp_obj-act-ekun-partner

AND c~mc_name1 = t_bp_obj-act-ekun-name_last

AND a~city1 = wa-city1

AND a~post_code1 = wa-post_code1

AND a~po_box = wa-po_box

AND a~street = wa-street

AND a~house_num1 = wa-house_num1

AND a~house_num2 = wa-house_num2

AND a~region = wa-region

AND a~addr_group = 'BP'.

offer me points if helpful

[Former Member]

HI

ALTERNATE FOR INNERJOIN IS

FOR ALL ENTRIES

IF YOU USE INNER JOIN FOR MORE THAN 3 TABLES YOU WILL GET PERFORMANCE PROBLEM

F YOU USE FOR LL ENTRIES FOR ANY NUMBER OF TABLES YOU WON'T GET ANY PROBLEM

REWARD IF USEFULL

[Former Member]

HI

*PARAMETERS P_CITY TYPE SPFLI-CITYFROM.

*

**TYPES: BEGIN OF ENTRY_TAB_TYPE,

• CARRID TYPE SPFLI-CARRID,

• CONNID TYPE SPFLI-CONNID,

• END OF ENTRY_TAB_TYPE.

**

**DATA: ENTRY_TAB TYPE TABLE OF ENTRY_TAB_TYPE,

• SFLIGHT_TAB TYPE SORTED TABLE OF SFLIGHT

• WITH UNIQUE KEY CARRID CONNID FLDATE.

**

**SELECT CARRID CONNID

• FROM SPFLI

• INTO CORRESPONDING FIELDS OF TABLE ENTRY_TAB

• WHERE CITYFROM = P_CITY.

**

**SELECT CARRID CONNID FLDATE

• FROM SFLIGHT

• INTO CORRESPONDING FIELDS OF TABLE SFLIGHT_TAB

• FOR ALL ENTRIES IN ENTRY_TAB

• WHERE CARRID = ENTRY_TAB-CARRID AND

• CONNID = ENTRY_TAB-CONNID.

TRY LIKE THIS

REWARD IF USEFULL