topic Re: Problem with nested loops with huge data in Application Development and Automation Discussions

Problem with nested loops with huge data

Former Member — Tue, 21 Sep 2010 05:34:42 GMT

Hi Developing experts,

Here I have a problem with nested loops, hopefully I will get a solutin for it here.

My situation is as below.



LOOP AT ........ITAB1 <------------- with 2000 records

    LOOP AT ........ITAB2 <------------- with 600 records

      LOOP AT ........ITAB3 <------------- with 1,60,000 records

       LOOP AT ........ITAB4 <------------- with 30,000 records

       ENDLOOP.

     ENDLOOP.

   ENDLOOP.

ENDLOOP.

I am getting one dump afetr executing this.

" No more storage space available for extending an internal table"

This is my dump. Please suggest me how can I manage this.

Thanks in advance

G.S.Naidu

Re: Problem with nested loops with huge data

Former Member — Tue, 21 Sep 2010 05:37:48 GMT

Put where condition for the loops which are inside the loops.

Re: Problem with nested loops with huge data

Former Member — Tue, 21 Sep 2010 05:39:50 GMT

Hi Naidu,

Definitely this is a HUGE problem ..... that too Nesting Level is 4

Possible Solution

Do Parallel Cursor on 2 - 2 internal tables and consolidate in another internal tables Xand Y ......

and finally do another parallel cursor between X and Y .....

Like .....

Sort itabA by f1 f2..

Sort itabB by f2 f3.

Sort itabC by f1 f2..

Sort itabD by f2 f3.

Loop at itabA.

read table itabB into wa_b with key f1 = itabA-f1.

if sy-subrc = 0.

loop at itabB.

<logic>

Consolidate into itabX

EXIT.

endif.

endloop.

endif.

endloop.

Loop at itabC.

read table itabD into wa_b with key f1 = itabC-f1.

if sy-subrc = 0.

loop at itabB.

<logic>

Consolidate into itabY

EXIT.

endif.

endloop.

endif.

endloop.

Loop at itabX.

read table itabY into wa_b with key f1 = itabX-f1.

if sy-subrc = 0.

loop at itabB.

<logic>

Consolidate into itabY

EXIT.

endif.

endloop.

endif.

endloop.

Hope this helps u ......

Cheers,

Kripa Rangachari...

Re: Problem with nested loops with huge data

Former Member — Tue, 21 Sep 2010 05:41:14 GMT

Use READ with BINARY SEARCH statement instead of multiple loops. This will reduce the execution time.

Regards,

Sridhar

Re: Problem with nested loops with huge data

Former Member — Tue, 21 Sep 2010 05:51:18 GMT

Hi,

First sort all the tables.

first use the loop for inernal table 1,

read table itab2 with binary search

rad table 3 itab3 with binary search,

read table itab4 with binary search,

endloop.

Re: Problem with nested loops with huge data

rohit_kaikala — Tue, 21 Sep 2010 06:01:07 GMT

Hi,

Use the loopings separately.

First sort all the internal tables with binary search,

After that use first 2 loops get the data into a internal table then the next internal table in the same way.

Regards,

Rohith.

Re: Problem with nested loops with huge data

Former Member — Tue, 21 Sep 2010 06:02:23 GMT

Hi,

At any situation do not use more then 2 level of nesting of loop.

Just try with AT NEW , AT END etc or READ TABLE statments this will solve your problem

Regards

Bikas

Re: Problem with nested loops with huge data

Former Member — Tue, 21 Sep 2010 06:31:52 GMT

Hi,

In addition to the WHERE clause for inner tables, sort the inner tables accordingly and apply BINARY search for every inner table to reach the index of first record statisfying WHERE clause. Use that INDEX as starting point while looping through the inner tables.

Regards,

V Joshi

Re: Problem with nested loops with huge data

Former Member — Tue, 21 Sep 2010 06:32:50 GMT

Hi.

Use Parallel Cursor Technique..

for better understanding plaese go through the beloe link.

http://wiki.sdn.sap.com/wiki/display/Community/AlternativesforNested+Loops

hope this will help you..

Regards,

Kiran

Re: Problem with nested loops with huge data

Former Member — Tue, 21 Sep 2010 06:38:57 GMT

Hi Kripa,

Thanki you for the immediate response.

I have used the parallel cursur method. But still I am getting the same dump but with in less time its going in to the dump and saying the same error. "No more storage space available for extending an internal table."

Since It is a problem of internal table memory can we increase the ITAB memory in any way?

Thanks in advance

G.S.Naidu

Re: Problem with nested loops with huge data

Former Member — Tue, 21 Sep 2010 06:54:00 GMT

hi Naidu,

We have to opt the solution of memory and other harware stuffs at the end only when we are sure that we dont have any other optimization techniques....

I guess the Nesting levels should be avoided and usage of binary search, index, where clause and parallel cursor between 2 loops! ... will increase the perfocmance ...

may be i STRONGLY suggest to first opt for code optimization and then we can have a look into other possiblie solutions ...

Hope this helps..

Cheers,

Kripa Rangachari...

Re: Problem with nested loops with huge data

Former Member — Tue, 21 Sep 2010 07:06:11 GMT

Hi Kripa,

Yes you are right. But there is no other option for us.

As per the select query in the ITAB there are 1,60,000 records and in other ITAB 30,000 records are there.

we can't reduce these records and can't change the select query as its mandatory requirement.

Since I have used parallel cursur method and read statements and binary searches my performance got increased but to insert the data in to the ITAB there is no space afterwords . Thats why with less time I am getting dump. But before I was getting dump afetr 10minutes. Now after using all these performance increasing formulas I made the dump to come with in 2minutes..

So I strongly believe that we have to increate the ITAB's memory only. Please suggest in this area.

Thanks & Regards

G.S.Naidu

Re: Problem with nested loops with huge data

Former Member — Tue, 21 Sep 2010 07:19:22 GMT

It is indeed a memory problem and not a performance problem.

If you can't process the data in separate blocks (per block select less data) then the only solution is increasing memory.

Re: Problem with nested loops with huge data

Subhankar — Tue, 21 Sep 2010 07:23:15 GMT

Hi Naidu,

Your main problem is storage space of a internal table ." " No more storage space available for extending an internal table"

For this one parallel cursor is not helpful. it is help to make the processing fast.

As per my understanding from four ITAB you are building one final internal table. In your final table if you have any duplicate logic or you know that for some fields table data will be unique, then you can make table as hash table. Here instead of append you need to do insert.

If there is no such logic or any other way to make table size low, you can go for the application server file or custom table. Insert the records to the temporary app server file or to custom table.

Thanks

Subhankar

Re: Problem with nested loops with huge data

matt — Tue, 21 Sep 2010 07:39:01 GMT

You've got four internal tables with data already... so where is the amount of data increasing? What are you doing inside the loops? This is the information we need.

Incidentally - I just changed an old program from using BINARY SEARCH to SORTED and HASHED tables and achieved a 60x performance improvement. I frankly find it depressing that BINARY SEARCH is still being promoted as the solution to all performance issues. BINARY SEARCH is old technology Let's move on from the 1990s, people!

Re: Problem with nested loops with huge data

SuhaSaha — Tue, 21 Sep 2010 07:46:58 GMT


> Incidentally - I just changed an old program from using BINARY SEARCH to SORTED and HASHED tables and achieved a 60x performance improvement.

Hey Matt,

60X performance improvement is pretty cool !! I'm into the habit of using SORTED tables but still don't think HASHED tables are my cup of tea.

If you could share any specific reason why you used HASHED tables ?

Thanks,

Suhas

Re: Problem with nested loops with huge data

Former Member — Tue, 21 Sep 2010 07:58:40 GMT

Hi!

Removing a LOOP AT ... WHERE statement is pretty simple, you have to do the following:



  SORT itab2 BY key.   "sorting before LOOP is very important
  LOOP AT itab1.
    READ TABLE itab2
      WITH KEY key = itab1-field
      BINARY SEARCH.
    IF sy-subrc = 0.
      LOOP AT itab2 FROM sy-tabix.
        IF itab2-key = itab1-field.
          "process data from itab2 here
        ELSE.
          EXIT.
        ENDIF.
      ENDLOOP.
    ENDIF.
  ENDLOOP.

This is a very useful and pretty quick solution.

Regards

Tamá

Re: Problem with nested loops with huge data

matt — Tue, 21 Sep 2010 09:06:15 GMT

Let's take the example of the previous post:

DATA: itab2 TYPE HASHED TABLE OF desc WITH UNIQUE KEY key.   
  LOOP AT itab1.
    READ TABLE itab2
      WITH TABLE KEY key = itab1-field.
    CHECK sy-subrc = 0.
    LOOP AT itab2 FROM sy-tabix.
      IF itab2-key NE itab1-field.
        EXIT.
      ENDIF.
        "process data from itab2 here
    ENDLOOP.
  ENDLOOP.

So, whenever you're doing a lookup, and you've got a unique key, use a HASHED table.

Re: Problem with nested loops with huge data

Former Member — Wed, 22 Sep 2010 11:38:43 GMT

HI,

Have you tried to use ABAP statement FREE itabx?

It will not only clear/refresh the internal table but also release the memory

occupied by it.

Re: Problem with nested loops with huge data

Former Member — Wed, 22 Sep 2010 12:27:42 GMT

If you have where condition in your inner loops then use sorted tables with key

data itab1 TYPE sorted TABLE OF <structure> with unique key KEY1 KEY2.

data itab2 TYPE sorted TABLE OF <structure> with unique key KEY1 KEY2.

loop at itab1 assigning <fs1>. "use field symbol instead of work area

loop at itab2 assigning <fs2> where <fs2>-key1 = <fs1>-key1.

endloop.

this will improve performance by a big margin if you have similar scenario.

Regards

Vishal Kapoor