Skip to Content
author's profile photo Former Member
Former Member

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

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

8 Answers

  • Posted on Oct 04, 2010 at 08:20 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 30, 2010 at 10:25 AM

    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 😊

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 30, 2010 at 10:26 AM

    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 ''.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 01, 2010 at 07:42 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • Posted on Sep 30, 2010 at 10:05 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 01, 2010 at 03:54 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 06, 2010 at 09:15 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • Posted on Oct 07, 2010 at 02:28 PM

    >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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.