topic How to avoid data selection inside the loop? in Application Development and Automation Discussions

How to avoid data selection inside the loop?

naveen_inuganti2 — Tue, 06 May 2014 20:48:25 GMT

Hello Experts,

I am working on one performance item and I have already applied some changes to the original version.

Now, If I compare my new program with old program, I have good improvement in performance. I am checking if I can do anything on statements that are top on the below list. I think 40% for Modify statement is acceptable after my research (Below run is updating around 20M records which is real time volume for this application).

As we can see 34% of run time to going for one SELECT query on custom table. Take a look at below high level flow of my program to understand above select query.

1. Select data from ZABC

2. Select data from Variant Table (Var1, Var2, Var3 etc.., 12 in real time)

3. Loop Variant Table

4. Select data from X, Y, Z table for Var<n>.

5. Populate final internal table from ZABC, X, Y and Z table

6. Modify ZTABLE with Final Internal table data

7. End Loop on Variant Table

As described in the flow of the program, ZABC table data is common for all the variants and need not to fetch multiple times. Hence I am doing it only once in my program. Below is that select query:


select rrcty ryear rbukrs racct rcntr sum( amt1) as amt1  "Like I have 32 amount fields in original query
           from zabc
            into table i_zabc
            where ryear in r_year    " Two records in ranges with I and EQ
            and rvers = '001'
            and rrcty in r_rrcty        "Three records in ranges with I and EQ
            and rldnr = 'DT'
            group by rrcty ryear rbukrs racct rcntr
            order by rrcty ryear rbukrs racct rcntr.

ZABC table is again having huge volume of data and we are fetching millions of records with above query. That is primary reason to take long time. May be that is okay as I am already using Indexes of this table. But, I am not comfortable with it as it can reach max. memory point and through run time error. Fetch Cursor is one reliable option that I can see here, but with that, I should move ZABC selection inside the variant loop which can cause fetching ZABC data 12 times (Let me know If I am missing anything here).

Now, third statement in my trace results, with 10% of overall time is this:

loop at i_abc assigning <fs_abc>.

...

loop at i_table assigning <fs_table> where low <= <fs_abc>-racct and high >= <fs_abc>-racct.

...

endloop.

...

endloop.

6 million executions with this complex WHERE condition is causing this statement to get 3rd position in trace results. I tried below two options which are, I think, taking even more time - (I am still monitoring these options)

1) Removed WHERE condition on LOW, HIGH and applied filter inside the loop.

2) Removed WHERE condition on HIGH only and applied filter inside the loop.

Any suggestions on how to proceed with ZABC selection and I_TABLE loop.

Let me know if you have any questions on above compose.

Re: How to avoid data selection inside the loop?

ThomasZloch — Wed, 07 May 2014 07:30:07 GMT

Since I looked at this case before, let me try some quick suggestions:

Is table ZABC related to table X, Y and/or Z and can the selection be limited by applying those 12 selection variants?

If yes, try a join select involving these tables that could make your step 1 obsolete and replace steps 4 and 5.

This might also get rid of the "loop inside loop" problem. Generally, make sure that the inner table is declared as a sorted table with a key that consists of the fields as used in the WHERE-condition of the inner loop. Use secondary keys for internal tables if your ABAP release permits and the task at hand warrants it.

Finally, look at PACKAGE SIZE option for the (join) select to reduce memory consumption.

Thomas

Re: How to avoid data selection inside the loop?

naveen_inuganti2 — Wed, 07 May 2014 11:55:38 GMT

Hi Thomas,

I can try that... but I am not sure if that is going to work in my case. Because....

1. Table X and Y are coming from one standard function module.

2. Table Z is already result of inner join on Z1 and Z2. It has around 30 fields and good amount of data in target table, now if I link this to ZABC, it will increase total field count to 60-70 and program cannot deal with that much data (I will verify this once again).

3. Most important thing is ZABC data is same for all 12 variants. Hence, even if I go for inner join with variant specific info, query is still going to fetch entire data.

4. I cannot get data from X, Y, Z at once for all variants because program cannot process entire data in one go! (It has to update custom db table within this loop).

Regards,

Naveen

Re: How to avoid data selection inside the loop?

naveen_inuganti2 — Sun, 11 May 2014 20:12:20 GMT

Thank you! Now, I am using inner join to filter some data and applied grouping and it has significantly decrease data volume... now it there shouldn't be any memory allocation issues for my program or query. It also resolved the issue with that where condition!

Regards,

Naveen