AND OR combination poor select performance

[former_member302630]

Can I improve the performance of such a select statement.

SELECT * FROM ZDB_TABLE1 INTO TABLE itab FOR ALL ENTRIES IN DATA_PACKAGE WHERE SALESORG = l_salesorg AND ( SOLD_TO = DATA_PACKAGE-SOLD_TO OR SHIP_TO = DATA_PACKAGE-SHIP_TO OR /BIC/zfield1 = DATA_PACKAGE-/BIC/Zfield1 ).

this statement takes a long time to execute inspite of creating three indexes.

salesorg+sold_to

salesorg+ship_to

salesorg+zfield1

execution plan:

SELECT * FROM "zDB_table1" WHERE "SALESORG"=:A0 AND ("SOLD_TO"=:A1 OR "SHIP_TO"=:A2 OR "/bic/zfield1"=:A3) OR "SALESORG"=:A4 AND ("SOLD_TO"=:A5 OR "SHIP_TO"=:A6 OR "/bic/zfield1"=:A7) OR "SALESORG"=:A8 AND ("SOLD_TO"=:A9 OR "SHIP_TO"=:A10 OR "/bic/zfield1"=:A11) OR "SALESORG"=:A12 AND ("SOLD_TO"=:A13 OR "SHIP_TO"=:A14 OR "/bic/zfield1"=:A15) OR "SALESORG"=:A16 AND ("SOLD_TO"=:A17 OR "SHIP_TO"=:A18 OR "/bic/zfield1"=:A19)

[former_member302630]

Can I improve the performance of such a select statement.

SELECT * FROM ZDB_TABLE1 INTO TABLE itab FOR ALL ENTRIES IN DATA_PACKAGE WHERE SALESORG = l_salesorg AND ( SOLD_TO = DATA_PACKAGE-SOLD_TO OR SHIP_TO = DATA_PACKAGE-SHIP_TO OR /BIC/zfield1 = DATA_PACKAGE-/BIC/Zfield1 ).

this statement takes a long time to execute inspite of creating three indexes.

salesorg+sold_to

salesorg+ship_to

salesorg+zfield1

execution plan:

SELECT * FROM "zDB_table1" WHERE "SALESORG"=:A0 AND ("SOLD_TO"=:A1 OR "SHIP_TO"=:A2 OR "/bic/zfield1"=:A3) OR "SALESORG"=:A4 AND ("SOLD_TO"=:A5 OR "SHIP_TO"=:A6 OR "/bic/zfield1"=:A7) OR "SALESORG"=:A8 AND ("SOLD_TO"=:A9 OR "SHIP_TO"=:A10 OR "/bic/zfield1"=:A11) OR "SALESORG"=:A12 AND ("SOLD_TO"=:A13 OR "SHIP_TO"=:A14 OR "/bic/zfield1"=:A15) OR "SALESORG"=:A16 AND ("SOLD_TO"=:A17 OR "SHIP_TO"=:A18 OR "/bic/zfield1"=:A19)

[Former Member]

The best solution is to try to use joins instead of for all entries.

The Select statement can only use one table index. It won't use a combination of all three. Therefore, I can see where that would be slow. It might be better to try to do 3 select statements instead of one. One using sold to, then one using ship to and then one using zfield1. I know multiple trips to the database isn't usually the best thing to do, but in this case it might be. You will probably get some duplicate records, but you can delete them.

Also, don't forget to check to make sure that the internal table isn't empty first or will you will get every record from the table.

[matt]

This is a BW routine - there's no real possibility of changing the FAE with DATA_PACKAGE to a join.

[roberto_forti]

Hi,

First of all, consider the primary key on where clause and avoid using (*) fields.

In case of the actual retrieve doesn't have primary key on where clause then try to identify possibility to execute Join query with table which has primary key.

Consider trace SQL tool (ST05) identifying any issue e resolving it. Example: The best secondary index traced to use with ABAP %_HINTS...

Regards,

[kiran_k8]

Cleo,

May be you should share structure of your Ztable & Index details (perferably with screenshots) and DATA_PACKAGE's structure to get more apt replies.

K.Kiran.

[RaymondGiuseppi]

" this statement takes a long time to execute inspite of creating three indexes.

AFAIK most database wont use your 3 indexes to fetch data, but only one index.

So you should consider using 3 select statements and merge the result tables, removing duplicates.

[UweFetzer_se38]

Haven't tried it yet, but maybe the combination of join (instead of FOR ALL ENTRIES) and UNION (instead of OR) will use all three indexes.