topic Re: Select to Database or Loop in Program in Application Development and Automation Discussions

Select to Database or Loop in Program

Former Member — Tue, 29 Apr 2014 11:32:28 GMT

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.

Re: Select to Database or Loop in Program

Former Member — Tue, 29 Apr 2014 11:44:05 GMT

Hi,

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

Thanks,

Bhanu.

Re: Select to Database or Loop in Program

Former Member — Tue, 29 Apr 2014 11:45:49 GMT

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

Re: Select to Database or Loop in Program

Former Member — Tue, 29 Apr 2014 11:52:29 GMT

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

Re: Select to Database or Loop in Program

anubhab — Tue, 29 Apr 2014 11:55:03 GMT

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

Re: Select to Database or Loop in Program

former_member206650 — Tue, 29 Apr 2014 11:58:17 GMT

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

Re: Select to Database or Loop in Program

Former Member — Tue, 29 Apr 2014 12:03:26 GMT

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.

Re: Select to Database or Loop in Program

Former Member — Tue, 29 Apr 2014 12:04:43 GMT

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.

Re: Select to Database or Loop in Program

Former Member — Tue, 29 Apr 2014 12:12:35 GMT

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)

Re: Select to Database or Loop in Program

former_member206650 — Tue, 29 Apr 2014 12:16:55 GMT

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

Re: Select to Database or Loop in Program

Former Member — Tue, 29 Apr 2014 12:17:28 GMT

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

Re: Select to Database or Loop in Program

ThomasZloch — Tue, 29 Apr 2014 12:17:33 GMT

The best option is

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

Thomas

Re: Select to Database or Loop in Program

RaymondGiuseppi — Wed, 30 Apr 2014 07:17:13 GMT

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

Re: Select to Database or Loop in Program

Former Member — Mon, 16 Jun 2014 06:20:00 GMT

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 .