Select to Database or Loop in Program

[Former Member]

Hello gurus,

Please help me out for best programming skills, which is best using Select statement in Loop  to fetch data or get whole data from database using select statement once and looping with conditions.

Example  TAB is a table with A, B, C , D conditions.

1st method: Select A B C D from TAB into ITAB.

        then loop itab with some condtion.

2nd method : loop then Select statement with condition.

1. TAB1 and TAB2 , two select statements to fetch data from tables, then loop on TAB1 and then in this loop another loop for TAB2 for populate data into output table . output like

      TAB1-FIELD-A TAB2-FIELD-A

      TAB1-FIELD-A  TAB2 -FIELD-B

2. Or Loop at TAB1 and then select statement on TAB2 from database to fetch data.

So here my doubt is .. in 2 example heavy data is there, then under Loop of TAB1 , select statement hits database that many times  but in

    1 example select statement on database only one time then loop under loop.

So Loop under loop or Select statement to hit database repeats, which one better.

[Former Member]

Hi,

We should always try to hit the data base table only once in one program context .

Thanks,

Bhanu.

[Former Member]

Hi Consult,

i would select data of tab 1 into sorted table1, data of tab 2 into hashed table 2 for all entries in tab 1.

then loop at sorted tab 1 and read hashed tab 2.

+ loop at sorted itab is fastest way for loop

+ read big data on hashed itab is fastest way

+ loop at smaller itab and read hashed big itab

+ use field symbols for read and loop statement

+ always select data in one time if possible and not many single selects (big database traffic and overhead)

Just my 5 cents

Regards Wolfgang

[Former Member]

Hi,

Do not use SELECT in loop. Fetch the data first from the database table and do rest of the processing on the internal tables using LOOP, READ etc. Many times depending on business logic you might need to change the approach if it is completely unavoidable. So, for your question, method 1 is better.

Regards,

Shahir Mirza

[anubhab]

Hi,

     It's always better to hit the database as less as possible. So, get your data in one shot. And if the tables are related, better to use INNER JOIN to get final data. INNER JOIN will be much faster.

Thanks,

Anubhab

[former_member206650]

Hi,

first use select statement to get all the possible  values from database then loop it...inside loop you use read statement for operations..

hope this makes ur doubt clear...

[Former Member]

Yeah , bro if it is single value then we can use Read statement but, if we want multiple values from inner loop then we  need to provide loop not read statement.

[former_member206650]

ur observation is right but in the loop u can use where  statement then do the operations...

[Former Member]

Yes , Hitting database or select statement is the most important issue for ABAPer . Because we should not hit database more.

But there are times , when reports need data to be fetched from more tables like 10 above then that time , Loops will be more , then which one will be the best.

More loops under loop will give short dump..?? Loops will be problem..??

At any cost we should not use Select statement under loops..?

My view for above statement is . Yes we should not use Select statement under Loops it is much bigger problem than Loop under loops. 

[Former Member]

Loop about itabs is ALLWAYS faster than many single database selects!

Get the database data in one time (for all entries or inner Joins) if possible and do the rest by loops. Sometimes it is helpfull to create compressed itabs to read database in one time (for example you read 2000 orders with 10.000 items, but with only 100 different materials. To get data for material it is better to compress first materials into a own itab to read MARA etc. with 100 items instead of reading with 10.000 line items)

[Former Member]

Hi,

It is not good to select data in loop.

1. First select the data

2. Loop on selected data.

3. If there is a need of Loop inside Loop, Use Parallel Cursor Techinque.

Thanks and Regards,

Shakeer Hussain

[ThomasZloch]

The best option is

3. TAB1 and TAB2, one select statement (JOIN) to fetch data from both tables into output table, done.

Thomas

[RaymondGiuseppi]

If this is possible (no cluster/pool table) and useful (not a buffered table with a high hit ratio, and never for a fully buffered table) try to use a JOIN option in a single statement. As much as possible, only

SELECT data from buffered tables in a LOOP. There is also the option of using a second select with a FOR ALL ENTRIES IN the first extracted table but that may result in performance problem, especially for high volume of data and incomplete key mapping, but that is almost always better than the SELECT in the LOOP.

Regards,

Raymond

[Former Member]

So, actually when database is triggered multiple times , its a performance issue. Then how to know that database has been taken hit these many times. We can know the sql query time but how to know that ??, these many times database has been hit and due to this performance issue is ther .