topic Re: For All Entries causes performance issues. in Application Development and Automation Discussions

For All Entries causes performance issues.

Former Member — Wed, 21 Dec 2005 10:11:15 GMT

Hi,

The following slect statement causes performance issues

SELECT * FROM (LF_A) INTO TABLE lt_B

FOR ALL ENTRIES IN it

WHERE id = it-id.

The LF_A has more than 300000 thousand records.Is there any alternative to improve this select call.

Best Regards,

Arun

Re: For All Entries causes performance issues.

Former Member — Wed, 21 Dec 2005 10:15:07 GMT

Hi,

If your internal table lt_B does not require all the fields of table (LF_A ), then select only the requried fields from the table into internal table.

eg.

select <field 1 >

from ( LF_A )

into table lt_b

for all entried in it

where id = lt-id.

Also, if there are more than 1 primary keys, then do include them in the where clause of the select query.

Best regards,

Prashant

Re: For All Entries causes performance issues.

Former Member — Wed, 21 Dec 2005 10:17:37 GMT

Use ranges ! This method will reduce the database access!!!!!

FOR ALL ENTRIES MEANS : LOOP .... SELECT ... ENDLOOP.

SO FOR EACH ID A SELECT WILL BE DONE!!! no performant

better to use ranges!

data it type XXXX

ranges rg for it-id.

loop at it.

rg-low = it.

rg-sign = I

rg-option = EQ

append rg.

check sy-tabix eq 3000.

SELECT * FROM (LF_A) appending TABLE lt_B

WHERE id in rg.

free rg.

endloop.

LAST SELECT !!!! for the id not selected

if not rg[] is initial.

SELECT * FROM (LF_A) appending TABLE lt_B

WHERE id in rg.

endif

Message was edited by: STEPHAN KAMINSKI

Re: For All Entries causes performance issues.

Former Member — Wed, 21 Dec 2005 10:18:05 GMT

Hi Arun,

Firstly, Is id the primary key in LF_A?

Secondly, do you need all the fields from LF_A?

What you can do is, have iterations, and in each iteration get records from LF_A based on it_B records(say 1000 records in each iteration) and process.

Regards,

Raj

Re: For All Entries causes performance issues.

manuel_bassani — Wed, 21 Dec 2005 10:20:48 GMT

Hi Arunkumar,

for an exhaustive answer you should specify the index of LF_A table but consifer the following:

1. Be sure that internal table "it" must be filled (empty tables in FOR ALL ENTRIES statement means a full table scan in LF_A table: all entries will be selected)

2. Id field ID is not the primary index, you could define an index to improve select in this table.

3. Try to use ranges instead. Even if FOR ALL ENTRIES statement is more efficient than LOOP...SELECT...ENDLOOP. Using ranges is a best way.

4. Try to limit the number of fields (use select field1 field2... instead of Select *)

Best Regards, Manuel

Re: For All Entries causes performance issues.

Former Member — Wed, 21 Dec 2005 10:24:06 GMT

Hi Arun,

From ur code i can see that,ur using *.Using * will drastically decreases performance specially to ur case with more than 300000 records.My question is do u need all the fields instead u can guve the field name under select instead * which ll definitely improve ur performance.and whats ur primary key.

Message was edited by: manikandan rajendran

Re: For All Entries causes performance issues.

former_member221770 — Wed, 21 Dec 2005 22:24:29 GMT

Hi Arun,

As most of the replies have suggested, try not to use a SELECT *, rather get the necessary fields. One other thing is to make sure that table IT actually has values. If table IT is empty, FOR ALL ENTRIES will retrieve all the records in LF_A! Also depending on whether you are using the primary key (or secondary index) when you hit LF_A will also affect your performance.


* Make sure IT is not empty
check not it[] is initial.

SELECT * FROM (LF_A) INTO TABLE lt_B
FOR ALL ENTRIES IN it
WHERE id = it-id.

Hope this helps.

Cheers,

Pat.

Re: For All Entries causes performance issues.

RichHeilman — Wed, 21 Dec 2005 22:54:35 GMT

One more thing that might help. Make sure that IT is sorted by ID before doing the select.



* Make sure IT is not empty
check not it[] is initial.

<b>sort it ascending by id.</b>
 
SELECT * FROM (LF_A) INTO TABLE lt_B
FOR ALL ENTRIES IN it
WHERE id = it-id.

Regards,

Rich Heilman

Re: For All Entries causes performance issues.

suresh_datti — Wed, 21 Dec 2005 23:07:37 GMT

Hi,

just want to add that, when using FOR ALL ENTRIES, if you do not give the complete key in SELECT XXXX XXXX fields , duplicate entries will not be picked.. So you must be wary of your SELECT depending on the requirement.

Regards,

Suresh Datti

Re: For All Entries causes performance issues.

former_member186741 — Thu, 22 Dec 2005 04:12:13 GMT

with 'for all entries' ABAP generates an expanded SQL select which it passes to the database system. The more entries you have in your table the bigger this internal select becomes and the less efficient it is. To stop this occuring you can use the 'package size' addition,

i.e change your select to:

..........

SELECT * FROM (LF_A) appending TABLE lt_B PACKAGE SIZE 1000

FOR ALL ENTRIES IN it

WHERE id = it-id.

endselect.

.............

You can put code within the select clause if it makes sense to process before all the data is retrieved or you can wait until the select is exited and the table is full.

You will need to experiment to find the optimum package size.

As others have said:

- sort and remove duplicates from table it

- change * to the actual fields you want to retrieve

- do not allow table it to be empty

- does (lf_A) have to be a variable? a hard-coded table name may be more efficient