topic Re: multiple table fields in an itab. in Application Development and Automation Discussions

multiple table fields in an itab.

rnb86 — Wed, 27 Dec 2006 10:45:02 GMT

Hi friends,

i have a query about a report where i have to display fields from 4 different tables in a report output by taking all those fields into a single internal table.

mara,marc,mard, makt are the tables involved.

now the only way to do this is join condition. Am i right or is there a better way of getting the required output without using joins??

any suggestion is welcome.

Re: multiple table fields in an itab.

Former Member — Wed, 27 Dec 2006 10:47:42 GMT

Select the data separately into different internal tables. Then loop into the internal tables and finally populate the internal table that will be displayed.

Note: join on MARA & other table will be big performance issue

Regards

Kathirvel

Re: multiple table fields in an itab.

Former Member — Wed, 27 Dec 2006 10:49:03 GMT

You can use FOR ALL ENTRIES also

1. First select data from MARA table
     select * from MARA into table itab1.

2. if not itab1[] is initial.
       select * from marc into table itab2 for all entries in itab1 where matnr = itab1-matnr.
    endif.

3. similarly for the other 2 tables

Re: multiple table fields in an itab.

Former Member — Wed, 27 Dec 2006 10:52:45 GMT

yes create an work area which is combination of 4 tables & create a IT.

write join & fetch the data into IT.

Generate the report using tht IT because if u use more loops its a performance issue

Re: multiple table fields in an itab.

Former Member — Wed, 27 Dec 2006 11:06:59 GMT

Hi Raghu,

what you have told is correct i.e joining all the 4 tables with material number as a key but there will be a performance issue so better do this way...

select on mara & marc.....then using for all entries go and select on mard & makt tables....

Thanks

Vikranth Khimavath

Re: multiple table fields in an itab.

rnb86 — Wed, 27 Dec 2006 11:47:37 GMT

Hi friends,

first of all, thank you for your time and suggestions for my question. Im trying to get the output.

I think this can be done by using for all entries. But i dont have many fields to do that. i only have 5 fields from 4 tables to do the report. For that i need 4 itabs(for all entries method).i will publish the report once i get the output.

hey, can anybody send me the performance enhancing techniques in ABAP programming.

thanx again :Kathirvel, Chandrasekhar, Kalpanashri, Khimavath.

Re: multiple table fields in an itab.

Former Member — Wed, 27 Dec 2006 12:02:35 GMT

u can check some of them here

goto ur program in SE38

in the menu Environment~~->Examples~~>Performance examples

Re: multiple table fields in an itab.

Former Member — Wed, 27 Dec 2006 17:22:35 GMT

I've done some experiments and found that a join (even on multiple tables) usually gives somewhat better performance than using FOR ALL ENTRIES. The best thing for you to do would be to do what I did - write them both ways and see which is better.

You can also look at:

/people/rob.burbank/blog/2006/11/16/performance--what-will-kill-you-and-what-will-leave-you-with-only-a-flesh-wound

Rob

Re: multiple table fields in an itab.

Former Member — Thu, 28 Dec 2006 19:32:46 GMT

Rob,

to see which variant is better he needs to test on big enough data amount (close to production, if there is one). I think you agree that some variants which work fine on small data volumes may work bad on a really big data volumes...

I would use the simple rule to select between join vs for all entries :

- join is more DB-optimizer dependent and if wrong path is used - it's going to be a problem, from the other hand if all join conditions are defined well and WHERE is not very complex - it may work faster, besides - it returns just the data you need... so no unnecessary transfers from DB to applicaiton server. More tables you add to join -> more depends on optimizer to choose the right access path.

- for all entries less dependent on DB optimizer as usually you provide key fields in WHERE clause for corresponding table, works fast enough assuming it's written properly (check for empty intenal table before selecting, no duplicates in the internal table,...), BUT it requires more memory on application server especially when you need to select just a couple of fields but your where condition is contains fields which you don't need in your result, OR if your selection is not very restrictive on some tables which can result in big enough internal table... so if the space is an issue - I would select join.

I had to replace join with select for all entries in some cases as optimizer selected wrong path (it may be a problem with optimizer and not with join itself,,, but often we need to write code which wors fast now and not after SAP fixed error somewhere in optimizer ).

Re: multiple table fields in an itab.

Former Member — Thu, 28 Dec 2006 23:18:30 GMT

When in doubt, do it both ways and see which is better. But take buffering into account.

Without seeing the exact requirements, I wouldn't want to say for sure which is better.

Rob

Message was edited by:

Rob Burbank

Re: multiple table fields in an itab.

rnb86 — Tue, 02 Jan 2007 04:49:28 GMT

Hi Rob,

I took the long weekend off and couldnt reply to you on time.Please excuse my delayed response. Anyways, the requirement is not that huge. Im sending you the exact output i need to publish in the report.

Plant
Storage location
Material number
Description
Stock

(unrestricted)

MARC MARD-LGORT MARA-MATNR MAKT-MAKTX MARD-LABST

-WERKS

Grand total * * * * * *

The report shud give the existing stock for a material. The report should have subtotal of the stock for each storage location and Grand total of the stock at the end of the plant.

<b>Plant data should start at new page.

Input: Selection screen which will allow one to select a range of materials.</b>

Re: multiple table fields in an itab.

Former Member — Tue, 02 Jan 2007 17:27:56 GMT

Well, it doesn't seem too complicated. What I would do is select all required materials from MARA into an internal table and then use that table with FOR ALL ENTRIES in separate selects against the other tables. I think a JOIN would probably be more efficient from a tuning perspective, but not by much.

If you must have the most efficient code, code it both ways, execute both multiple times (to avoid buffering effects) and take the best.

Rob