topic Re: Data retrieval from 8 db tables in Application Development and Automation Discussions

Data retrieval from 8 db tables

Former Member — Thu, 01 Nov 2007 07:54:15 GMT

Hi Folks,

We haev a requirement where we need to query 7 tables to retrieve some data and display it in the form of a list.

This report is to be executed in background.

Right now we have 3 select statements, each of which has inner joins on 2-3 tables and uses "for all entries".

Performance wise which would be a better option - to keep it this way, or have separate select statement for each table and then read statements on internal tabels for output?

Thanks in advance,

Munish

Re: Data retrieval from 8 db tables

Former Member — Thu, 01 Nov 2007 07:57:04 GMT

Hi Munish,

This depend on the table in consideration and the number of entries, Selction screen fields which can be used in WHERE clause, etc

So I think you can take a decision on these parameters as well you can do the sql trace using ST05 and find out which is better.

Regards,

Atish

Re: Data retrieval from 8 db tables

Former Member — Thu, 01 Nov 2007 08:17:44 GMT

Hi,

I think it depends on the balance between ABAP time and Database time.

If you select all the data from tables independently and then do looping etc,

it will increase the hits as well as the ABAP time.

So the next best option is a mix of both. I believe the current approach holds good.

As, has been mentioned before also in this thread, you can improve the performance if the joins are being made on key fields since this reduced overhead.

If I were using tables which had all been joined with key fields I would have gone as high as with 5 Joins but I guess this is an ideal scenario so a join of 2-3 tables is a decent approach.

And the subsequent handling in ABAP is the only next step.

So this approach is fine and you should incorporate correct performance guidelines which you can find in this forum if you search for them.

Regards

Nishant

Re: Data retrieval from 8 db tables

Former Member — Thu, 01 Nov 2007 09:21:15 GMT

Thanks for the replies guys!!

For the last 2 hours I have been going through the posts looking for the clues.. and i guess its gonna take a bit more than simply choosing between inner join or for all entries

Re: Data retrieval from 8 db tables

Former Member — Thu, 01 Nov 2007 13:15:11 GMT

The performance differences between FOR ALL ENTRIES and JOINs is not that great; the important thing is the effective use of indexes.

Rob

Re: Data retrieval from 8 db tables

Former Member — Thu, 01 Nov 2007 14:04:47 GMT

Create a view joining (inner joins) as many tables as possible. You can also specify the selection conditions in your view. This will improve the performance.

Re: Data retrieval from 8 db tables

Former Member — Fri, 02 Nov 2007 14:25:18 GMT

Thanks Rob!

Infact yesterday I went through your and Siegfried's posts and articles only. Its only then that I realized that there s no step-by-step procedure for this. Really enjoyed reading your insightful posts. Thanks again

Re: Data retrieval from 8 db tables

Former Member — Fri, 02 Nov 2007 15:15:28 GMT

Thanks Sam!