Searching a sorted table with nonunique key with both key fields and nonkey

[Former Member]

A quick question, if I search a sorted table with non-unique key

lt_vzzbepp TYPE SORTED TABLE OF vzzbepp WITH NON-UNIQUE KEY BUKRS RANL,

and loop at it with the following statement

  loop at lt_vzzbepp into ls_vzzbepp
        where sbewart in lt_sbewart
        and dvorgang in it_val_date
        and dbudat in it_pos_date
        and splanist eq 'I'
        and sstorno eq ''.

will the loop make use of the key to limit the number of checks or will it still loop through every record in the table. Suppose there are a million records in the internal table, will it still check every single record for the criteria in the 'loop at statement' or will it limit the 'loop at' to the bukrs and ranl

[ravi_lanjewar]

Hi,

Your sorted table has non unique key BUKRS RANL.

If you put where condition on both the fields (ie Bukrs and RANL) or BUKRS it will used the binary search to get first record and upto mathing record by sequencial search.

But In case of where condition other than the which is not match left most part ot the key or non-unique key fields consider the search as linear search.

 loop at lt_vzzbepp into ls_vzzbepp
        where sbewart in lt_sbewart
        and dvorgang in it_val_date
        and dbudat in it_pos_date
        and splanist eq 'I'
        and sstorno eq ''.

In your case it will consider as linear search (ie read all the table). It will not optimize to read record.

[kesavadas_thekkillath]

For your uderstaiding, While selecting data from a db table without specifying the primary of secondary keys , will it fetch based on the key fields ?

The answer is same for sorted internal tables too

[Former Member]

sorry, I made a mistake in the opening post

the loop statement I am planning to use is

loop at lt_vzzbepp into ls_vzzbepp
        where bukrs in s_bukrs
        and ranl in s_Ranl
        and sbewart in lt_sbewart
        and dvorgang in it_val_date
        and dbudat in it_pos_date
        and splanist eq 'I'
        and sstorno eq ''.

[ravi_lanjewar]

Hi,

loop at lt_vzzbepp into ls_vzzbepp
        where bukrs in s_bukrs
        and ranl in s_Ranl
        and sbewart in lt_sbewart
        and dvorgang in it_val_date
        and dbudat in it_pos_date
        and splanist eq 'I'
        and sstorno eq ''.

Is It now correct your Question ?

It will consider depend on value in range table and most probable it will used the linear search because range table can have different sinario, it may be blank, exclude or include or any other condition.

if s_bukrs is initial and not execlude condition then only binary search consider and rest of other cases will consider as linear search.

[Former Member]

Hi Ravishankr,

What about in the even that I limit it to specific selections for bukrs and ranl, like the code below

loop at lt_vzzbepp into ls_vzzbepp
        where bukrs eq t_bukrs
        and ranl eq t_ranl
        and sbewart in lt_sbewart
        and dvorgang in it_val_date
        and dbudat in it_pos_date
        and splanist eq 'I'
        and sstorno eq ''.

[ravi_lanjewar]

IT will used the binary search and gives the better performance.

Using the binary search it will jump to first record and match the other condition. if match consider the record, if not ignore the

record.

[ThomasZloch]

Please also search for available information here on SCN, for example there is a blog by Siegfried Boes that covers the topic of your thread:

/people/siegfried.boes/blog/2007/09/12/runtimes-of-reads-and-loops-on-internal-tables

Also check the sticky threads of this forum.

Thomas

[Former Member]

thanks for clearing that up

[former_member194613]

Hi,

thanks your Thomas for recommending my blog, this can help a little for your question, but the solution is not in the blog.

The LOOP WHERE on sorted table can only automatically use the table key, if the WHERE clause consists ONLY of conditions with EQUAl ( = ) which are connected by AND. Any other WHERE-clause which is more general (as your case) is not supported by the kernel.

You must do the optimization by yourself, there the blog can help.

[Former Member]

Hi,

So what you are saying is

the statement below will use an optimised search

loop at lt_vzzbepp into ls_vzzbepp
        where bukrs eq t_bukrs
        and ranl eq t_ranl

but not in this case?

loop at lt_vzzbepp into ls_vzzbepp
        where bukrs eq t_bukrs
        and ranl eq t_ranl
        and sbewart in lt_sbewart
        and dvorgang in it_val_date
        and dbudat in it_pos_date
        and splanist eq 'I'
        and sstorno eq ''.

?

thanks

[ThomasZloch]

The second case should use it as well by all my understanding. Why don't you just try it out and compare runtimes and SE30 traces for both options? There are many local unknown variables that we cannot know about elsewhere (amount and distribution of data, hardware, network, ...), in the end only you can find out what's better for you, factoring in general principles that you can learn about here in the forum, blogs, articles, etc.

Thomas

[Former Member]

Hi Bijo,

No. It will not use the index. It will do a linear search.

The following definition would be more productive.

DATA: lt_vzzbepp TYPE SORTED TABLE OF vzzbepp
        WITH NON-UNIQUE KEY sbewart
                            dvorgang
                            dbudat
                            splanist
                            sstorno.

Regards,

Mark

[former_member194613]

once more, the last 2 comments are wrong:

> The LOOP WHERE on sorted table can only automatically use the table key, if the WHERE clause consists ONLY of conditions

> with EQUAL ( = ) which are connected by AND.

This is correct and in sync with the head of the internal table development!

Any additional needs extra handling inside the LOOP. Any IN-condition is not supported. This numerous exceptions are simply too rare and too diverse, therefore they are not supported.

And be aware, that even the support of the LOOP WHERE on sorted tables does not work in all old releases, only with 6.40 it should definitely work. Before not all operations use the keys automatically.

Siegfried

[Former Member]

Thanks Siegfried and everyone else who contributed, I am still a little unclear though...

here are a few scenarios, can you tell me which of the two will perform the quickest

DATA: lt_vzzbepp_sort TYPE SORTED TABLE OF vzzbepp WITH NON-UNIQUE KEY bukrs
                                                                           ranl
                                                                           sbewart
                                                                           dvorgang.

LOOP AT lt_vdarl into ls_vdarl.
   LOOP AT lt_vzzbepp_sort into ls_vzzbepp
        where bukrs = ls_vdarl-bukrs
        and ranl = ls_vdarl-ranl
        and sbewart in lt_sbewart
        and dvorgang in it_val_date.
.
.
  ENDLOOP. 
ENDLOOP.

OR

DATA: lt_vzzbepp_sort TYPE SORTED TABLE OF vzzbepp WITH NON-UNIQUE KEY bukrs
                                                                           ranl .
LOOP AT lt_vdarl into ls_vdarl.
  LOOP at lt_vzzbepp_sort into ls_vzzbepp
        where bukrs = ls_vdarl-bukrs
        and ranl = ls_vdarl-ranl.

     IF sbewart in lt_sbewart and dvorgang in it_val_date.
.
.
    ENDIF.
  ENDLOOP.
ENDLOOP.

conditions:

- lt_vdarl has +-20000 records (Loans master) (key fields are lt_vdarl-bukrs and lt_vdarl-ranl)

- for each record in lt_vdarl(for each unique combination of lt_vdarl-bukrs and lt_vdarl-ranl) there is an average of about 300 records in lt_vzzbepp_sort.

- lt_vzzbepp_sort has over a million records

Thanks

[Former Member]

Hi Bijo,

How are you populating internal table lt_vzzbepp_sort? Is it via a select statement. Why don't you eliminate the records that do not meet the following requirements while populating internal table lt_vzzbepp_sort.

Criteria

sbewart in lt_sbewart

dvorgang in it_val_date

If you do this your second solution would be more potent.

LOOP AT lt_vdarl into ls_vdarl.
  LOOP at lt_vzzbepp_sort into ls_vzzbepp
        where bukrs = ls_vdarl-bukrs
        and ranl = ls_vdarl-ranl.
 
  ENDLOOP.
ENDLOOP.

Regards,

Mark

[SuhaSaha]

> once more, the last 2 comments are wrong:
> The LOOP WHERE on sorted table can only automatically use the table key, if the WHERE clause consists ONLY of conditions
> with EQUAL ( = ) which are connected by AND.
> This is correct and in sync with the head of the internal table development!

"Head of Internal Table Development"?? Wow!! I wish i had the same access as you do.

[Former Member]

Thanks Mark. *slaps self on the forhead*, that would be the obvious thing to do. I came to the same conclusion after posting that.

[Former Member]

hello bijo,

LOOP AT lt_vdarl into ls_vdarl.
  LOOP at lt_vzzbepp_sort into ls_vzzbepp
        where bukrs = ls_vdarl-bukrs
        and ranl = ls_vdarl-ranl.
 
  ENDLOOP.
ENDLOOP.

if you want to make your routine even faster

read a tutorial about parallel cursors with nested loops

http://karsap.blogspot.com/2007/06/avoiding-nested-loops-using-parallel_19.html

hope it helps,

regards Sebastiá

[ravi_lanjewar]

Hi,

DATA: lt_vzzbepp_sort TYPE SORTED TABLE OF vzzbepp WITH NON-UNIQUE KEY bukrs
                                                                           ranl .
LOOP AT lt_vdarl into ls_vdarl.
  LOOP at lt_vzzbepp_sort into ls_vzzbepp
        where bukrs = ls_vdarl-bukrs
        and ranl = ls_vdarl-ranl.
 
     IF sbewart in lt_sbewart and dvorgang in it_val_date.
.
    ENDIF.
  ENDLOOP.
ENDLOOP.

Will be faster than first one.

Mean while there is one more scope to improvement of performance using fields symbols

LOOP AT lt_vdarl assign <fs_vdarl>.
  LOOP at lt_vzzbepp_sort assigning <fs_vzzbepp>
        where bukrs = <fs_vdarl>-bukrs  and ranl = <fs_vdarl>-ranl.
 .
.
.
   ENDLOOP.
ENDLOOP.

[Former Member]

Thanks Sebastian,

I will try that method and see if it makes a significant difference.

Thanks

Bijo

[former_member194613]

the second one will run faster.

Test it will with 100 lines in the outer table and 100 * 300 in the inner. There will be a huge difference. And in full load the first version will never come back.

But there is another issue, you internal table are too large. Sooner or later you will get a dump because of memory problems.

What is actually done with the data? Stored again in DB? Then you should split your processing into blocks, such that the table stay

smaller than 100.000 records. Process one block after the other.

[Former Member]

Thanks Siegfried

What is actually done with the data?

Its output via a alv grid report. Previously the program was using a sap standard function module to retrieve flows(VDBEKI & BEPI) per loan (VDARL). This incurred a lot of fetch requests to and from the DB, I rewrote this function module to retrieve all the flows for the selected loans (VDARL) using a for all entries in VDARL addition to the select statement...in so doing retrieving all the flows for the loans right at the beginning of the program.

Stored again in DB?

No

Thanks

Bijo

[former_member194613]

>Parallel cursor, just forget it!

The blog is not the solution to your problem, it leaves the loop when the 2 internal tables are not in sync. However, you must process both tables and you must handle additional entries in both tables which are not in the other. That is hard task with high risk of bugs, even when the inner operation is a READ with LOOPs even more.

From performance perspective it is not necessary, do not forget, that parallel cursor requires identcially sorted tables. That is not necessary in the normal solution, so use sorted table and you are fine! Keep you tables as small as possible right from the beginning, sorry I thought that this is obvious!

Siegfried

[Former Member]

^thanks for stating the obvious! I think everyone knows that keeping your tables as small as possible is the ideal situation. Try to understand the context before jumping to concIusions. I am deciding between either: (1)a standard function module which retrieves the flows(join of vdbeki and vdbepi) for each loan (vdarl-ranl) OR (2) retrieving ALL the flows for the SELECTED loans up front and storing it in an internal table (worst case scenario if they dont restrict the selection of loans, then all flows (>1000000) will be retrieved). So its a trade off between either getting flows per loan into an internal table but incurring multiple database fetches(which is a join between vdbeki and vdbepi for each vdarl-ranl) for each loan in the selected vdarl range OR getting all the flows upfront(in an internal table) and only incurring one database fetch.

Which is the better of the two evils?

-

---

>Parallel cursor, just forget it!
The blog is not the solution to your problem, it leaves the loop when the 2 internal tables are not in sync. However, you must process both tables and you must handle additional entries in both tables which are not in the other. That is hard task with high risk of bugs, even when the inner operation is a READ with LOOPs even more.

the fields bukrs and ranl exist in both tables and are the fields driving the selection on both tables in my case.

eg.

DATA: lt_vzzbepp type trty_vzzbepp,
lt_vdarl type standard table of vdarl.

SORT lt_vdarl BY bukrs ranl.
SORT lt_vzzbepp BY bukrs ranl.

LOOP AT lt_vdarl. 
   LOOP at lt_vzzbepp into ls_vzzbepp
        where bukrs = ls_vdarl-bukrs
           and ranl = ls_vdarl-ranl.
  ENDLOOP.
ENDLOOP.

The scenario above seems the ideal situation for application of cursors