topic Re: SELECT WHERE IN issue in Application Development and Automation Discussions

SELECT WHERE IN issue

Former Member — Tue, 28 Aug 2007 09:40:01 GMT

Hi experts,

I am having performance issue regarding this statement:

SELECT zfc_join~awb_id

APPENDING TABLE i_waybill_ids

FROM zfc_join INNER JOIN qmel

ON zfc_join_{qmnum = qmel}qmnum

FOR ALL ENTRIES IN r_vbeln_temp

WHERE qmart IN (c_dm,c_dv)

AND qmel~vbeln EQ r_vbeln_temp-low.

*Additional Notes:

1. QMEL contains 10 million+ records. ZFC_JOIN contains 200,000+ records.

2. Changed the range selection from QMEL~VBELN IN r_vbeln_temp to the codes above due

to a short dump error when the range is too big.

Questions / clarification needed:

1. Does the placement of the join tables matters? That is,

"zfc_join INNER JOIN qmel" is different from

"qmel INNER JOIN zfc_join"

2. Does the command "qmart IN (c_dv,c_dm)" different from

qmart EQ c_dv OR qmart EQ c_dm? Which is faster?

Please help out. Thanks

Re: SELECT WHERE IN issue

Former Member — Tue, 28 Aug 2007 10:52:08 GMT

Hi chan,

Definitely the following selection code gives big performance issues because u r u using joins conditions with where clause the fields are non-key fields

You can try this way.

Select QMNUM

from QMEL into table tbl_qmel

where QMART eq c_dm or

QMART c_dv and

Vbeln in r_vbeln_temp-vbeln.

If sy-subrc = 0.

Sort tbl_qmel.

Endif.

NOTE : in the above select statement is also gives performance issue bec fields using in where clause are QMART and VBELN are non-key fields in table QMEL..

If you want increase performance you have to create ‘Secondary index’- for QMART and VBELN for tabel QMEL

If not tbl_qmel[] is initial.

Select awb_id

From zfc_join

APPENDING TABLE i_waybill_ids

For all entries in tbl_qmel

Where qmnum = tbl_qmel-qmnum.

Endif.

Hope you will get some idea with above explanation.

Reward with points if it is helpful.

Regards,

Vijay

Re: SELECT WHERE IN issue

Former Member — Tue, 28 Aug 2007 11:12:37 GMT

For joins you must check the fields (where condition and on-condition) of the joined tables and the whether they appear indexes, this information should be added.

The question is always, is it possible to select effiecently with the where condition from on table and join the next table efficiently with the on-condition maybe plus further where-fields.

What are thre indexes on QMEL and on ZFC_Join (why is the table called join, is it a view).

Siegfried

P.S: I don't have time to look up this information by myself.

Re: SELECT WHERE IN issue

Former Member — Thu, 30 Aug 2007 17:30:05 GMT

Hi Ryan,

This code is not good in terms of performance..

SELECT qmnum

FROM qmel

INTO TABLE tab_qmel

FOR ALL ENTRIES IN r_vbeln_temp

WHERE vbeln EQ r_vbeln_temp-low.

If sy-subrc EQ 0

AND NOT tab_qmel[] IS INITIAL.

SELECT qmnum

awb_id

....

FROM zfc_join

INTO TABLE tab_zfc

FOR ALL ENTRIES IN tab_qmel

WHERE ( qmart EQ c_dm

OR qmart EQ c_dv )

AND qmnum EQ tab_qmel-qmnum.

Thanks and Best Regards,

Vikas Bittera.

**Reward if useful**

Re: SELECT WHERE IN issue

Former Member — Fri, 14 Sep 2007 03:35:36 GMT

Hi Ryan,

Not sure that you have your answers but will respond anyways.

To answer your questions:

1- haven't seen anywhere where it said which table should go first, I'm confident it doesn't matter.

2-there should be no difference in how you coded your qmart where condition.

To improve upon your code:

- Select your data into Table, rather than appending table, Into Table is more efficient

- it is better to have an inner join than bring back a ton of data, as it puts load on the network, there are always exceptions but this is the norm. your statement is not complex, so your join is fine

- as mentioned by another response, a secondary index would make your program fly. Both your Where conditions are on non-key fields and your performance suffers greatly I'm sure. You would need an index created with qmart and vbeln in that order. Your Basis team should be able to confirm the benefit of this index. This should not be an option, given the size of the table.

The non-key fields on the Where clause is your biggest issue. Second is the Appending table. Make that change as well.

Hope this helps.

Filler