topic Re: Select single within Loop in Application Development and Automation Discussions

Select single within Loop

Former Member — Fri, 22 Jun 2007 01:05:16 GMT

Hi,

I have to optimize an existing code, this program runs monthly with huge data. It is using an inner join with more than 2 database tables. Also, which is better:

1. Select into itab2 for all entries in itab1, then Loop at itab1 and Read itab2.

2. Loop at itab1, then select single from database table.

select afpo~matnr

makt~maktx

afpo~charg

afpo~wempf

afko~aufnr

afpo~wemng

afko~gamng

afko~stlbez

afko~stlst

afko~stlnr

afko~sdatv

mara~ferth

s022~arbpl

appending corresponding fields of table it_porder

from afpo inner join afko on afpo_{aufnr = afko}aufnr

inner join aufk on afpo_{aufnr = aufk}aufnr

inner join mara on afpo_{matnr = mara}matnr

inner join makt on afpo_{matnr = makt}matnr

and makt~spras = sy-langu

inner join s022 on afpo_{aufnr = s022}aufnr

and afpo_{matnr = s022}matnr

where afpo~matnr in p_matnr

and afko~aufnr in p_aufnr

and afko~gstrp in p_date

and mara~mtart in ('FERT', 'HALB')

and aufk~auart in ('FO11', 'PO11')

and afko~gltri ne '00000000'.

Re: Select single within Loop

Former Member — Fri, 22 Jun 2007 01:08:03 GMT

Hi,

The first option is always better. But make sure your itab1 has entries.

Reward points if useful.

Regards,

Atish

Re: Select single within Loop

Former Member — Fri, 22 Jun 2007 01:48:34 GMT

As per my analysis since i have done Trace for complete select query's

You can use Inner joins max 3 tables ,if it is more than 3 tables then go for all entries

Use select single if you have primary key condition otherwise use Select upto 1 row

it is not good idea use select single within loop intead of that use normal select query outside of the loop and within loop use Read table with binary search.

Use Clear,refresh,free command properly

if you want to compare diffrent select query use get run time field command.

simple example :

data : a type i,

b type i,

c type i.

start-of-selection.

get runtime field a.

here select query

get runtime field b.

here a is starting time ,b ending time

c = b - a.

write 😕 c.

Thanks

Seshu

Re: Select single within Loop

Former Member — Fri, 22 Jun 2007 06:15:47 GMT

It all depends upon the no of comparision primary fields available.

write the code for both and try running and check the performance during the peak hours then remove the other one

Comment the some tables check the performance andf remove the tables which are taking more time. for them use FOR ALL ENTRIES

and then use binary and read table

This will surely increase the performance of the report

Regards

Shiva

Re: Select single within Loop

Former Member — Fri, 22 Jun 2007 08:08:07 GMT

Thanks for the responses.

I have a question on inner join. In the select statement above, if a record does not exist on table S022 but all the conditions in the where clause are satisfied, will the record still be included?

Re: Select single within Loop

Former Member — Fri, 22 Jun 2007 11:01:00 GMT

The answer to your last question about an entry not exisitng in one table but existing in other is no. An inner join only returns records that match all join conditions. An outer join can be used to return values that exist in on table but not on another along with those that do match. But generally you are safer removing that tbale from your SELECT and peforming an additinal select using FOR ALL ENTRIES

Re: Select single within Loop

Former Member — Fri, 22 Jun 2007 13:35:42 GMT

The join on S022 isn't using any key fields. If it is a large table, that is probably the source of your problem. Try taking just that out to see if the remaining SELECT runs more quickly.

rob

Re: Select single within Loop

Former Member — Sat, 23 Jun 2007 21:20:39 GMT

While using INNER JOINS, if any one condition fails for any of db table, it will not fetch the record. So, if there is no record existing in S022, it will not return any record.

Regarding you question on for all entries and select single in loop,

Do not use Select statement in loop.

FOR ALL ENTRIES: When the records in the internal table are in millions, then the select query will take lot of time.

Inorder to optimize the query, push the records of internal table into a buffer table and sort the records by the field used for comparing. Now delete adjacent duplicates from the buffer table.

This will reduce the number of records and hence optimize the query using for all entries.

Regards

Navneet

Re: Select single within Loop

Former Member — Tue, 26 Jun 2007 01:31:39 GMT

Thanks everyone.