topic Re: For all Entries or Loop ? DB-Optimizer Issue in Application Development and Automation Discussions

For all Entries or Loop ? DB-Optimizer Issue

sinan_keklik — Mon, 14 May 2007 15:08:47 GMT

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

Re: For all Entries or Loop ? DB-Optimizer Issue

Former Member — Mon, 14 May 2007 15:11:55 GMT

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

Re: For all Entries or Loop ? DB-Optimizer Issue

Former Member — Mon, 14 May 2007 15:26:13 GMT

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

Re: For all Entries or Loop ? DB-Optimizer Issue

sinan_keklik — Mon, 14 May 2007 15:33:32 GMT

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

Re: For all Entries or Loop ? DB-Optimizer Issue

Former Member — Mon, 14 May 2007 15:54:05 GMT

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

Re: For all Entries or Loop ? DB-Optimizer Issue

sinan_keklik — Mon, 14 May 2007 16:06:59 GMT

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

Re: For all Entries or Loop ? DB-Optimizer Issue

Former Member — Tue, 15 May 2007 04:04:20 GMT

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..

Re: For all Entries or Loop ? DB-Optimizer Issue

sinan_keklik — Tue, 15 May 2007 06:31:47 GMT

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

Re: For all Entries or Loop ? DB-Optimizer Issue

christian_wohlfahrt — Tue, 15 May 2007 07:18:23 GMT

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