topic Re: LOOP AT vs INNER JOIN in Application Development and Automation Discussions

LOOP AT vs INNER JOIN

Former Member — Fri, 23 Mar 2012 22:31:28 GMT

Hello everyone.

I have been searching for hours in google about this question, but I have not found anything strictly related to this issue.

The programmers in my company uses "LOOP AT table_a" to match records in "Table_b", is better to use INNER JOIN to this proposes? why?

I think the INNER JOIN was made to match criteria at low level machine language, what represents an excellent performance, and LOOP AT was to made to print information, but I'm not much sure about this.

I appreciate your help, thank you.

Re: LOOP AT vs INNER JOIN

Former Member — Fri, 23 Mar 2012 23:07:30 GMT

Hi,

the main problem I see with an INNER JOIN is that you need an extra structure to hold the sum of the fields of both tables. At times the data in table_b has been loaded from the database already so it wouldn't make sense to hit the database again. Then there is the cases where you look up table_b to check whether a record doesn't exist. Then there is the case where you have an function module that selects the data from the database but it's missing the description to the value of one field. In that case I'd load the descriptions separately and read them during a LOOP AT if needed.

On the other hand there are definitely use cases where and inner join, or better yet a view on the 2 database tables makes more sense. As always it is a matter context what makes more sense.

I usually try to use standard SAP function modules or methods to retrieve data from standard tables and then work with 2 internal tables, instead of creating a view or join on database tables that might change during patching and mess up my code.

Cheers
Adi

Re: LOOP AT vs INNER JOIN

Former Member — Sat, 24 Mar 2012 09:25:23 GMT

Hi Ivan,

Try reading through this thread I started years go for some background information - http://scn.sap.com/thread/919187

I came to the conclusion that newer ABAP developers were favouring FOR ALL ENTRIES but none of them really knew why. It's good to see someone questioning this approach. I still maintain that an INNER JOIN is better in almost all scenarios, you just have to know how to write efficient SQL.

FOR ALL ENTRIES has the small benefit of making the code slightly easier to understand sometimes which may help with maintenance - I would argue that we should be up skilling our ABAP developers rather than dumbing down our code though.

Hope this helps,

Gareth.

Re: LOOP AT vs INNER JOIN

Former Member — Mon, 26 Mar 2012 10:39:55 GMT

For performance critical sections, I tend to do both and use whichever is faster. It isn't always obvious in advance which that is going to be. It is entirely likely that my SQL skills are the reason why sometimes a join is unexpectedly slower. In some cases, though, the join really is slower.

In cases where performance isn't an issue, I'd argue for using whichever method produces the most understandable code.

Re: LOOP AT vs INNER JOIN

UweFetzer_se38 — Mon, 26 Mar 2012 10:54:10 GMT

In most cases JOIN is faster, because we don't need the network traffic (for each select single), especially if you have different appl/db servers. Rule of thumb: "let do the DB all the work"

And btw: there is a third option:

SELECT * FROM SFLIGHT AS F INTO SFLIGHT_WA
    WHERE SEATSOCC < F~SEATSMAX
      AND EXISTS ( SELECT * FROM SPFLI
                     WHERE CARRID = F~CARRID
                       AND CONNID = F~CONNID
                       AND CITYFROM = 'FRANKFURT'
                       AND CITYTO = 'NEW YORK' )
      AND FLDATE BETWEEN '19990101' AND '19990331'.
ENDSELECT.

Best regards
(@se38)

Re: LOOP AT vs INNER JOIN

Former Member — Mon, 26 Mar 2012 11:35:22 GMT

this is an endless discussion, I cones up at least once every second week in this forum and it was always answered.

+ Try a join if you need information from 2 or more tables.

+ Use FOR ALL ENTRIES if one internal table was created or selected at an earlier point in the programs execution and later additional data mus be read from another DB table. Use the nested loops to combine to 2 result tables and be ware that you must use an optimized READ or LOOP for the inner operation of the nested loop, i.e. sorted or hashed table or BINARY SEARCH.

* Do not use nested SELECTs or SELECTs inside LOOPs (exception: SELECT used single-record table buffer)

Using a FOR ALL NTRIES to replace a join is NOT recommended. It can make sense, if the join becomes very large, many tables with conditions which do not use primary keys. Then the DB optimizer can become confused. But then you will also have to invest some time for the optimization.

Be aware, that the JOIN can be flexible to some extent, if the WHERE-clause varies a lot, then the DB optimizer can choose differend access pathes, for example different order of table accesses.

Note, the above mentioned SUBSELECT is very special, it can not replace all joins. However, if applicable then it can be even faster than the join. Again everything is done inside the DB.

Background: The Join does more inside the DB and trys to avoid unnecessary data transfers and accesses.

Siegfried

Re: LOOP AT vs INNER JOIN

abdul_hakim — Mon, 26 Mar 2012 12:59:04 GMT

Ivan,

It all depends on the context you are using both the approaches.

Making use of Join:

If you want to read logically related information from tables then it is advisable to use the JOIN over Nested SELECTs.

Making use of Nested LOOP:

If you want to optimize the performance of nested LOOP then you may go for Parallel Cursor.

As i mentioned above the best approach depends on the requirement and the context where it is being implemented in ABAP Code.

You can use the Runtime analysis and compare the performance.

Hope this helps.

Thanks

Abdul Hakim

Re: LOOP AT vs INNER JOIN

Former Member — Mon, 26 Mar 2012 13:05:08 GMT

please do not recommend parallel cursor anymore.

It is possible to use parallel index with internal tables, but it is cumbersome and cann easily lead to functional bugs and is not necessary if you use an optimized inner access => sorted table, read my blogs on internal tables.

it is also possible to use parallel cursors on the database but this should also not done out of performance reasons but only if you need the OPEN CURSOr with HOLD (was also explained lots of times).

Siegfried

Re: LOOP AT vs INNER JOIN

Former Member — Mon, 26 Mar 2012 13:47:26 GMT

Hello everyone, thanks for your answers.

I'm just trying to RETURN RELATED ROWS WHEN THERE IS A MATCH IN BOTH TABLES ONLY, in whatever SQL Language (Microsoft SQL Server, Oracle, MySQL, etc) it's advised the INNER JOIN for this proposes, but the company where I am working at, ABAP programmers uses to write a LOOP, what I think it's wrong, adding this takes 1 hour to return related rows, there are a lot of bad practices else in the company and I want to expose all of them, but I need strong arguments to explain it. Actually, I am a C# - Oracle developer, and I like the best practices even if I have to spent time to learn about them and to apply them. I have learned ABAP, I understand clearly its syntax, but I think, for this simple case, it's wrong use LOOP instead INNER JOIN.

Thank you for your help

Re: LOOP AT vs INNER JOIN

abdul_hakim — Mon, 26 Mar 2012 13:59:25 GMT

Siegfried,

I have used Parallel cursors on many occasions and it has helped me in improving the performance when compared over nested LOOPs.

Thanks

Abdul Hakim

Re: LOOP AT vs INNER JOIN

Former Member — Mon, 26 Mar 2012 14:18:23 GMT

@abdul,

yes, because you did use a standard table in the nested loop. The performance gain over a key access to a sorted table is marginal, it might be lost because you need to sort both tables for parallel cursor.

Re: LOOP AT vs INNER JOIN

abdul_hakim — Mon, 26 Mar 2012 14:26:58 GMT

Siegfried, Yes i have used standard tables and the performane gain was marginal. So for no issues. Things are going well. I have read your blogs about performance tuning and they are really excellent. Keep up the good work.

Thanks,

Abdul Hakim

Re: LOOP AT vs INNER JOIN

HermannGahm — Mon, 26 Mar 2012 20:09:09 GMT

Hi Ivan,

or should i say Don Quijote? (since i think you have a loooong fight)

Write small examples that prove your points (e.g. join vs. loop and selects). Ask

them to prove their points / practises (with examples). Slowly and surely you might

be able to convince them. Oh and never mind... some people simply can't be convinced

they simply don't want to change something or learn something new.

Kind regards,

Hermann

Re: LOOP AT vs INNER JOIN

Former Member — Mon, 26 Mar 2012 20:28:34 GMT

Hello Hermann, it's very funny your analogue with Don Quijote hahahaha, perfect to me I say because you right, people don't want to change them mind.

I think it has not been clear the fact INNER JOIN is better than LOOP AT for return rows where there is a match in two tables.... a lot of people has talk about parallel cursors, performance tips, etc.... but, just for this strictly case, very few post are useful to answer my question, I don't want anything else than know its low level behavior, which is better for a simple SELECT.

Thank you Hermann.

Re: LOOP AT vs INNER JOIN

HermannGahm — Mon, 26 Mar 2012 20:38:01 GMT

Hi,

i think the best thing is to write a small test porgram and prove your points. You can use DD02L (tables) and DD03L (fields) for that, these tables are available everywhere.

Kind regards,

Hermann

Re: LOOP AT vs INNER JOIN

joao_sousa2 — Mon, 26 Mar 2012 20:42:09 GMT

Database engines are optimized for this kind of operation so anyone will tell you that it's more efficient to use the inner join. There are two problems with the inner join which are not really performance related (well sort of):

Inner join makes you create an specific structure and you have to map the fields you really want. ABAPers are lazy (full disclosure, I'm also guilty), so it's much more comfortable to write select * into table X, where X type standard table of Y. This is bad memory wise, but hey, deadlines are deadlines
You can have 50 application servers, but only one database server. The inner join may have superior performance, but if you have a application server dedicated to data crunching it doesn't really matter. The database is being shared, the application server is yours. This is very special situation, and makes little sense from a landscape point of view, but in the end the ABAPer doesn't control the landscape he has to work with what he's been given.

Re: LOOP AT vs INNER JOIN

yuri_ziryukin — Tue, 27 Mar 2012 04:38:31 GMT

Joao Sousa wrote:
You can have 50 application servers, but only one database server. The inner join may have superior performance, but if you have a application server dedicated to data crunching it doesn't really matter. The database is being shared, the application server is yours. This is very special situation, and makes little sense from a landscape point of view, but in the end the ABAPer doesn't control the landscape he has to work with what he's been given.

Hello Joao,

the second point is not that much relevant actually. In the JOIN the database needs to do the following:

- read blocks for the first table+index from disk (time!) and buffer;

- loop over the records in these blocks and read relevant blocks for the second table+index from disk (time!) and buffer;

- combine the result set and retrieve it back.

In the case of for all entries/loop the first two parts need to be done in the same way, but you'll have more overhead from transferring data to the application server and back. More records will be moved over the network. Actually combining the result is not that critical to move it form DB to the app server.

Regards,
Yuri

Re: LOOP AT vs INNER JOIN

Former Member — Tue, 27 Mar 2012 08:17:12 GMT

... Ivan maybe you should read the answers again, the answers which you defined useful, are wrong or misleading ... the others are the good ones.

Application server versus database server is completely misleading, the FOR ALL ENTRIES is slower on the database, the join is the fastest even on the database! The ABAP time is orders faster if done correctly.

Re: LOOP AT vs INNER JOIN

joao_sousa2 — Tue, 27 Mar 2012 17:20:25 GMT

"In the case of for all entries/loop the first two parts need to be done in the same way".

I'm sorry, but it's not "in the same way". If you are doing a non-index search on the second table, retrieving the second table can be extremely time consuming. And I'm not saying it's more efficient, quite the opposite.

Re: LOOP AT vs INNER JOIN

joao_sousa2 — Tue, 27 Mar 2012 17:52:37 GMT

I made it quite clear that the "solutions" I posted were NOT the most eficient, and just tried to explain the psychology behind ABAPer choice.

Sometimes its interesting to understand your ABAPers and their choices, beyond the technical performance issues.