For all Entries or Loop ? DB-Optimizer Issue

[sinan_keklik]

Hi,

FOR ALL ENTRIES is a WHERE condition with many ORs. Is it Right that the DB-Optimizer stop to Optimize when there is an OR in the WHERE Condition.

Is it better to use LOOP ENDLOOP when I have keys in my WHERE condition to avoid an full table scan with FOR ALL ENTRIES?

Sinan

[sinan_keklik]

Hi,

FOR ALL ENTRIES is a WHERE condition with many ORs. Is it Right that the DB-Optimizer stop to Optimize when there is an OR in the WHERE Condition.

Is it better to use LOOP ENDLOOP when I have keys in my WHERE condition to avoid an full table scan with FOR ALL ENTRIES?

Sinan

[Former Member]

Hi Sinan,

Yes, you are right.

OR condition in your where clause will neglect the search through index .

try to replace it wil IN condition. if not you can go for Loop-Endloop.

but pls check the performance of both using "get runtime" command. use the better one.

regards,

madhu

[Former Member]

Hi Sinan,

Create ranges for each of the criteria in the where clause. Polutae the ranges and use them in the where clause using the IN operator.

Hope this help.

Regards,

Mark

[sinan_keklik]

How can I create RANGES for more than one FIELDS.

Something like an internal Table with for example Matnr and Werks.

I need something like

WHERE matnr = itab-matnr[1] and werks = itab-werks[1]

OR matnr = itab-matnr[2] and werks = itab-werks[2]

and so on

Sinan

[Former Member]

Looks like your requirement cannot be satisfied with RANGES. You could try writing different selects for the various conditions..

or build two ranges, one fore matnr and one for werks, and use those.

Hope this helps.

Sudha

[sinan_keklik]

How can I make 2 Ranges and say matnr_range[1] and werks_range[1]?

I think with 2 Ranges the wrong combination matnr_range[1] and werks_range[2] will be shown, too.

I have 7000 Keys.

Different Selects with LOOP is perhaps faster than FOR ALL ENTRIES. Is there perhaps any other possibility than 7000 SELECTs.

Sinan

[Former Member]

Hi Sinan,

when you have to compare many values of the field use the 'IN' operator in select...like..

select matnr werks f1 f2 f3 form <dbtab> into itab

where matnr in (mat1,mat2,mat3...)

and werks in (werk1,werk2,....)...

if you have a specific combination of fields to selct from.....for all entries is a better option ... this works like a nested select..endselect...

populate a table with your field combinations and then use FOR ALL ENTRIES....

Regards,

Vidya..

[sinan_keklik]

The Question is:

What is faster?

To use FOR ALL ENTRIES with 7000 n-1 Keyfields (This will supposably be 7000 FULL TABLE SCANs) or LOOP ENDLOOP and make 7000 Selects with n-1 keyfields (which will find for each of the 7000 n-1 keyfields the recordset without a full table scan).

Any One gain experience about that?

Sinan

[christian_wohlfahrt]

Hi Sinan!

As long as your where-clause is the same, the for-all-entries should be faster. But in case DB-optimizer runs into troubles and translates this version in a strange way, loop can be faster, too.

Just code both ways and measure the runtime. That should give you the answer for sure without any blind guesses.

Regards,

Christian