topic Re: Infoset vs Indexed nested loop in Application Development and Automation Discussions

Infoset vs Indexed nested loop

Former Member — Tue, 20 Jan 2009 14:47:35 GMT

Hello all,

i have an requirement in which i need to fetch data from several tables almost 6-8 tables (eventually to IT). right now i am fetching data using Select stmt using For all entries addition. So my requirement is to combine them into a big table and again i need to perform some aggregate functions on the final result. each table is supposed to have has 70000 to 100000 records. So my question what is better approach to combine them into a single internal table?? i have 2-3 options what i understood as per requirement but i want better performance and your opinion.

1. Use indexed loop or parallel cursor to combine all 6-8 tables

2. Create Infosets to fetch the desired final internal table at once and call infoset through abap stmt but i fear its gonna be slow too.

3. Create views to fetch database record and it will lower down my select queries n eventually use Indexed loop to build final internal table.

i am extracting following data in one snapshot( but its dynamic )

PA0000 Active Employees

HRP1001 Employee to Position Relation

HRP1001 Position to Job Relation

PA0002 Employee Personal Data

HRP1000 Employee Position Information

HRP1000 Employee Job Information

HRP1001 Employee Talent Relation

HRP1001 Talent To Job Family Relation

HRP1000 Job Family Information

HRP*custom

Z*table

do you have any other suggestions ??

keep in mind i am passing all tables names and their fields at runtime(dynamically).

i will appreciate your reply on which method will be more efficient for me.

mani

Re: Infoset vs Indexed nested loop

Former Member — Tue, 20 Jan 2009 14:54:05 GMT

Hi Mani,

I would fetch data from such related tables making use of a join.

However i could not understand how you intend to dynamically process multiple tables and correlate the data..

Re: Infoset vs Indexed nested loop

Former Member — Tue, 20 Jan 2009 15:09:47 GMT

But will Join work on Same tables??? i guess no. well for parameter i am passing complex input parameter which include the table name to extract data from including all fileds in output table, where clause, sort order etc. each row contain data for one table or you can say output for one IT. similarly im suppose to pass another input parameter which include the output table name, IT tables used to build new table, there join condition, sort order, 1to1 or 1ton indicator etc.

this will build me a final output table based on join on several IT tables.

then i need to perform some final aggregate functions on the final output tables.

Well using join even is not a good option as far as i know if number of records exceed 10k correct me if im wrong.

infoset dont support same table join either.

any suggestion will be appreciated.

Mani

Re: Infoset vs Indexed nested loop

Former Member — Wed, 21 Jan 2009 00:18:10 GMT

Moved to ABAP Performance and Tuning Forum...

Re: Infoset vs Indexed nested loop

Former Member — Wed, 21 Jan 2009 08:48:37 GMT

> keep in mind i am passing all tables names and their fields at runtime(dynamically).

try to avoid that if possible. I would say, it is more often avoidable than people think.

Of course you should try to use joins. And of course a join does not care whether the tables a different or the same.

Forget the parallel cursor, it is a myth, which nearly nobody uses, but it is not faster than a loop with a hash table inside. Use loop and inside the loop either sorted tables (non-unique keys) or hash tables (if you know the unique key).

Overall calculate the size of your final table lines times width, can you manage such a table in memory?

What is the actual use of such a big table? I think your task is a bit overambitious. Even optimzed it will take a whil, with bugs it will take ages

Siegfried

Re: Infoset vs Indexed nested loop

Former Member — Wed, 21 Jan 2009 14:38:52 GMT

Well with this function, i am going to do work of almost 50 FM with one FM if i pass parameters dynamically. So i think its better idea to give it a try rather then to make 50 FM seperately. i dont know if you hv used Indexed loop to its fullest. i read an article thats why i am deciding to use it.

I am not in favour of Join as out tables involves more than 70k data. so i dont think it is advisable to use Join if we hv more than 50k records or in other way as records will grow in Join stmt it will be more expensive in performance wise.

i will appreciate any reply.

Mandeep

Re: Infoset vs Indexed nested loop

Former Member — Thu, 22 Jan 2009 09:45:56 GMT

I do not see all details of your problem but from performance I see big issues.

You want to select a lot of data

And you want to create dynamically quite a lot of different accesses.

I would be surprised if all these access are automatically working fine. So from performance side your problem is probably not solvable.

Re: Infoset vs Indexed nested loop

Former Member — Thu, 22 Jan 2009 15:47:23 GMT

ok right now, this FM is returning all the tables in one shot and we are performing combiniing all the tables returned part in .net side: to build final table(s) and then perform some aggreagation and other display function. But as we are transferring lot of data to .net side its scaring me of network performance. so we decided to do Aggregation part on SAP server side so we will only return aggregate FM parameters instead of returnign all the tables(raw) to .net side. so yes it will effact performance but on the other side we will gain also to slow down network congestion. for example right now we are transferring say 100MB data to .net side but we need only 100kb data atlast after combinig them.

yes i will encounter a lot of problem during the transition thats true but i hc no choice then to do.

mani