Solved: Multiple Table Join instead of Nested Selects?

Former Member · ‎05-19-2006

Hi all,

I am in the process of writing a program that basically uses 6 tables. The first table holds information related to the second and the second holds info for the other 4 tables. For example, table 1 holds a key used in table 2. Table 2 holds keys used in tables A1 and A2, and also keys used in tables B1 and B2.

In tables A1, A2, B1, and B2, there can be multiple records related to the keys from table 2. For example, A1 might have 3 records, A2 might have 1 record, B1 might not have any records, and B2 might have 6 records. I'll need all the records in an internal table related to the keys from table 2.

My original extract runs very slow and is similar to this high level "pseudo-code":

select keyA keyB from table2 where table1 inner join with table2.

  select value from tableA1 where keyA = table2-keyA.
  * do some calculations, add to internal table
  endselect.

  select value from tableA2 where keyA = table2-keyA.
  * do some calculations, add to internal table
  endselect.

  select value from tableB1 where keyB = table2-keyB.
  * do some calculations, add to internal table
  endselect.

  select value from tableB2 where keyB = table2-keyB.
  * do some calculations, add to internal table 
  endselect.

endselect.

Essentially what I want to do is grab keys from table 2, look in table A1 and get all related records. Then look in table A2, and grab those related records. Then look in table B1, and grab those related records. Then look in table B2, and grab those related records.

How would it be possible to implement this without using nested selects? All tables are rather large so performance is a big deal. I know I'm supposed to avoid nested selects but conceptually this was an easy way to look at it. I've started on a new select statement that does joins but it's not producing the results I'm looking for as it tends to duplicate various records (for example, it'll grab from A1 all three of it's records, but then repeat A2's 1 record for each of A1's 3 records).

Any help would be greatly appreciated and points will be awarded to a working solution. I hope I haven't confused anyone. Thanks in advance.

Former Member · ‎05-19-2006

Hi john,

1. My personal recommendation would be somewhat like this :

a) Since this join involves almost six tables,

a single sql is not good.

b) Instead of using select-endselect,

we should use Select ,, into TABLE itab.

c) then we should ,

select from A FOR ALL ENTRIES WHERE ITAB-FIELD = FIEDL1

select from B FOR ALL ENTRIES WHERE ITAB-FIELD = FIEDL1

select from C FOR ALL ENTRIES WHERE ITAB-FIELD = FIEDL1

d) then we should

Loop at ITAB.

read internal table A with key.

modify itab.

read internal table B with key.

modify itab.

endloop.

2. Using this approach would

MINIMIZE DATABASE READS.

regards,

amit m.

Former Member · ‎05-19-2006

Hi john,

1. My personal recommendation would be somewhat like this :

a) Since this join involves almost six tables,

a single sql is not good.

b) Instead of using select-endselect,

we should use Select ,, into TABLE itab.

c) then we should ,

select from A FOR ALL ENTRIES WHERE ITAB-FIELD = FIEDL1

select from B FOR ALL ENTRIES WHERE ITAB-FIELD = FIEDL1

select from C FOR ALL ENTRIES WHERE ITAB-FIELD = FIEDL1

d) then we should

Loop at ITAB.

read internal table A with key.

modify itab.

read internal table B with key.

modify itab.

endloop.

2. Using this approach would

MINIMIZE DATABASE READS.

regards,

amit m.

Former Member · ‎05-19-2006

Instead of nested loops, why can't you use 'for all entries' for extracting data from tables.

IF NOT lt_table1[] IS INITIAL.

SELECT FIELD1 FIELD2

INTO TABLE lt_table2 FROM table2

FOR ALL ENTRIES IN lt_table1

WHERE FIELD2 = lt_table1-FIELD2.

ENDIF.

Former Member · ‎05-19-2006

Hi,

I think u can do like this:


1. Select from Table1 into Internal Table1
2. Select from Table2 into Internal Table2
   with: FOR ALL ENTRIES IN Table1
         WHERE Key2 = Table1-Key1.
3. Select from A1, A2, B1, B2 Into Internal Table 
   A1, A2, A3, A4 like step number 2.
4. Do your calculations process in Looping at Internal Table.

Regards,

Former Member · ‎05-19-2006

It is always better to use SELECT .. INTO TABLE FOR ALL ENTRIES IN INTERNAL TABLE rather then SELECT and ENDSELECT.

Select data from table 1 and table 2 using join into internal table.

Use this internal table to select data using option FOR ALL ENTRIES IN ITAB from each table A1, A2, B1, and B2.

Sort internal table on column which forms the part of WHERE STATEMENT so data will be retrieved faster than unsorted table.

Eg. SORT itab by F1.

SELECT C1, C2 FROM A1 into table itab1

for all entries in F1

where C1 = ITAB-F1.

Please check then internal table is not empty when you make use of FOR ALL ENTRIES option.

Use for SORT and proper usage of table indexes will redue improve performance of query.

Hope this will help you to code your queries.

Former Member · ‎05-19-2006

All,

Thank you for your replies. I will try the suggestion and award points once I am sure it has improved performance.

One question though - considering the four tables can contain millions of records each, does the

select from table for all entries

significantly improve performance?

RichHeilman · ‎05-19-2006

Compared to doing nested SELECT ENDSELECT, it should be significant, but be careful. Make sure that you check that the internal table in which you are linking to has some data in it, and also it is a good idea to sort that table by the key that you are linking.

Select * into table itab1
        from some_table
               where field1 in s_field1.

check not itab1[] is initial.
sort itab1 ascending by field1.

select * into table itab2
         from some_table
               for all entries in itab1
                      where field1 = itab1-field1.

Regards,

Rich Heilman

Former Member · ‎05-22-2006

<i>Mea culpa</i> Rich. The nested select performed quite a bit worse than I expected.

Rob

Former Member · ‎05-19-2006

hi

this better explains the performance related issues

plz chk this out

General Performance hints for Open SQL Programming

Keep the hit list small

Wherever possible, you should include all selection conditions in the WHERE clause, using AND and checking for equality. Do not select a large dataset and then check it with CHECK. If you want to read the whole table, you do not have to specify a WHERE condition at all.

Transfer small amounts of data

If you only want to transfer a few fields, use SELECT with a structure, not SELECT *. Alternatively, you can use one of the views in the ABAP Dictionary to select data. If you do use a view, the SAP buffering is switched off.

You should use the aggregate functions rather than selecting data and grouping it yourself. SAP buffering is switched off when you use aggregate functions.

When you UPDATE a database record, you should only update those columns that have been changed.

Use a small number of database accesses

When you INSERT, UPDATE or DELETE, you should use sets of data instead of individual table entries. This ensures that the index only has to be maintained once, which relieves the load on the database.

You should only use nested SELECT loops when the hit list in the outermost level is very small. There are various ways of avoiding nested SELECT loops:

Building a JOIN in the FROM clause

Joins as views defined in the ABAP Dictionary.

SELECT ... FOR ALL ENTRIES

In the outermost loop, the database table (PACKAGE SIZE) is read section-by-section into an internal table, sorted by its primary key (SORT on the internal table, or read in using ORDER BY PRIMARY KEY). For each data record in the internal table, all associated, dependent records are read into a further internal table (using SELECT ... FOR ALL ENTRIES). This is also sorted. You can then carry on processing using a nested LOOP.

The advantage of SELECT ... FOR ALL ENTRIES is that it provides good performance regardless of the selectivity of the condition on the outermost table, since, in contrast to the nested SELECT, it works in a data-oriented way in the database, but still only picks out the relevant database entries (different to parallel cursor processing).

You should use the addition FOR ALL ENTRIES if a JOIN is not possible for syntactical reasons or if the JOIN would result in high redundancy due to the constantly repeated fields from the left table.

Explicit cursor handling (OPEN CURSOR [WITH HOLD]...)

In this processing type, a separate cursor is opened for each table involved. These are processed in parallel. In order for the system to recognize control breaks, the tables must be sorted ( ORDER BY PRIMARY KEY) before being read. You should only use parallel cursor processing when you want to process the outermost table completely or to a large extent, since WHERE conditions for the outermost table cannot be passed on to other tables (in other words, you might read more data than is necessary).

Caution: RANGES tables

You should use explicit cursor handling for large quantities of data and logical databases.

Search through small amounts of data

In WHERE conditions, you should use EQ comparisons linked with AND as often as possible. This means that the system can use indexes in the search.

NOT, OR and IN are not supported by indexes unless all of the fields in the SELECT clause and WHERE condition are also contained in the index.

Reduce the database load where possible

SAP table buffering

The SAP buffering is switched off:

When you use SELECT FOR UPDATE or SELECT DISTINCT in the SELECT clause,

When you use BYPASSING BUFFER in the FROM clause,

When you use JOINs and subqueries

When you use ORDER BY f1 ... fn in the ORDER-BY clause.

When you use aggregate functions in the SELECT clause.

When you use IS [NOT] NULL in the WHERE condition.

You cannot process a query in the SAP buffer if the generic key section is not specified in the WHERE condition

Avoid re-reading the same data.

Before you change a table using DELETE, INSERT or UPDATE, you should check whether you need to read entries using SELECT.

If you wannt to sort data, it is more efficient to read them into the internal table and sort them using SORT than to use the ORDER-BY clause, where the sort is not supported by an index.

You should check whether you can delete duplicates using the DELETE ADJACENT DUPLICATES FROM itab. instead of using SELECT DISTINCT.

You should use logical databases if possible.

Further Help

Remarks on Performance

General Performance Notes for Internal Tables

Sharing Internal Tables

When you assign internal tables using itab1 = itab2, the data of itab2 is not copied physically. The data is only copied if you access one of these tables in change mode.

OCCURS Value or INITIAL SIZE Specification

Internal tables are a dynamic data structure. Their memory requirements are met in blocks. The initial memory allocation (hereafter called the OCCURS area), can be controlled using the " OCCURS n" or "INITIAL SIZE n " addition in the table definition (see DATA, TYPES). Once the OCCURS area is full, the next block to be created is twice as big as the OCCURS area (as long as this is not greater than 8 KB). All further blocks are then created with a constant size of 12 KB.

You can leave it to the system to determine the size of the OCCURS area by specifying n = 0. In this case, the system allocates only a "small" portion of memory at the first INSERT or APPEND statement. "OCCURS 0" or "INITIAL SIZE 0" means that 16 <= n <= 100 (depending on the line width).

It only makes sense to specify a concrete value of n > 0 when you know exactly how many entries the table will have, and you want to set up the OCCURS area exactly. This can be particularly important if you want to nest internal tables (where an "outer" internal table contains one or more other internal tables in each line, and the "inner" tables only have a few entries (no more than 5, for example).

To avoid excessive memory requirements, the system handles large values of n as follows: The largest possible value of n is n_max = 8 KB divided by the line width. For larger values, n is set such that n multiplied by the line width is around 12 KB.

Index Management

As soon as you change an internal table using INSERT, DELETE or SORT, the logical sequence of the table entries will no longer correspond with the physical sequence in the memory. When this happens, the system creates a logical index, which also requires memory. Furthermore, each INSERT or DELETE statement requires further memory. If your internal table is very large, changing the index can result in significantly increased runtime.

The system does not administer secondary indexes for internal tables.

Filling Line-by-line

Unlike filling a table using the INSERT statement, using APPEND does not cost runtime in terms of maintaining the index. If the sequence of the entries is unimportant, or they are already in the correct order, you should use APPEND instead of INSERT.

Filling and Deleting by Blocks

If you want to add or append whole blocks of a table to an internal table, you should do this using the following block operations, not line-by-line:

INSERT LINES OF itab1 [FROM i] [TO j] INTO itab2 [INDEX k]. or

APPEND LINES OF itab1 [FROM i] [TO j] TO itab2.

Reading from the Datbase

If you want to read several records from an internal table into a database table using SELECT, you should not do it record-by-record, but using the INTO TABLE or APPENDING TABLE additions.

Changing Fields Using Selective Field Transport

If you want to change individual fields of one or more database tables, you can transport fields selectively using the TRANSPORTING f1 ... fn [WHERE cond] addition. This can increase performance considerably in comparison with a simple MODIFY statement, in which the whole work area is copied back into the table. This applies particuarly if you exclude components that are themseleves tables using the TRANSPORTING addition. You can also speed up the frequent task of resetting a flag for all lines meeting a certain condition by using MODIFY ... TRANSPORTING.

<b>please reward if useful</b>

Former Member · ‎05-19-2006

Hii

use for all entries

Data : Begin of I_vbak occurs 0,

Vbeln like vbak-vbeln,

End of I_vbak.

Data : Begin I_vbap occurs 0,

Vbeln like vbap-vbeln,

Posnr like vbap-posnr,

Matnr like vbap-matnr,

Kwmeng like vbap-kwmeng,

End of I_vbap.

Data : Begin of I_makt occurs 0,

Matnr like makt-matnr,

Maktx like makt-maktx,

End of I_makt.

Data : d_lines like sy-subrc.

Start-of-selection.

Refresh : I_vbak , I_vbap , I_makt.

Select vbeln from VBAK into table I_vbak

Where erdat = p_erdat And

vkorg = p_vkorg And

vtweg = p_vtweg And

spart = p_spart.

Clear d_lines.

Describe table I_vbak lines d_lines.

Check d_lines <> 0.

Select vbeln posnr matnr kwmeng from VBAP into table I_vbap

For all entries in table I_vbak

Where vbeln = I_vbak-vbeln.

Clear d_lines.

Describe table I_vbap lines d_lines.

Check d_lines <> 0.

End-of-selection.

Sort I_vbap by vbeln matnr.

Select matnr maktx from makt into I_makt

For all entries in I_vbap

Where spras = sy-langu And

matnr = I_vbap-matnr.

Sort I_makt by matnr.

Loop at I_vbap.

Clear I_makt.

Read I_makt with key matnr = I_vbap-matnr binary search.

Write 😕 I_vbnap-vbeln,

I_vbap-psonr,

I_vbap-matnr,

I_Makt-matnr,

I_vbap-kwmeng.

Endloop.

FOR ALL ENTRIES retrieves all the table entries depending on another table.

Inner Join retrieves table entries which match the specified condition between two tables.

Do not use JOIN if the number of tables is greater than 3.

chk this link

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

Former Member · ‎05-19-2006

I believe you'll find there's not much difference. There shouldn't be a significant difference between "for all entries" and a join for properly constructed selects. Nested selects get a lot of bad publicity on this forum, but so long as you manage to use indexes effectively, the difference shouldn't be enough to be considered a performance 'hit'. Selecting without an index is the cause of most performance problems.

I'll try to work up some examples and post the results.

Rob

Former Member · ‎05-22-2006

OK - I wrote a program that compares a join over six tables against "for all entries" and nested selects. I've included the program below. I was a bit surprised by the results. "FOR ALL ENTRIES" took almost twice as long as the joins. And the nested select took about five times as long as "for all entries".

However -

I checked the results of the joins by separating the results into tables similar to the other methods and eliminated duplicates from the other tables. The joins picked up fewer records. I think if it were possible to do outer joins on more than one table, you might be able to pick up all the records, but outer joins don't work that way.

'FOR ALL ENTRIES' also has difficulties. If you don't select all the key fields, it will eliminate duplicate entries.

The results are for the system that I tested in. With a different system, different tables and different numbers of records in the tables, you might get different results.

So I think it depends more on your program requirements which and the relationships between the tables to determine the method you use. The best way is for you to test using your own tables and requirements.

The program also includes a 'control' form that does an unindexed select. It always performed the worst. The program does each select or group of selects twice in each form. The first one is to eliminate the effects of buffering. I've also executed each form a number of times. SAP recommends doing this and taking the result with the least amount of time for comparisons.

Rob

Code follows:


REPORT ztest LINE-SIZE 80 MESSAGE-ID 00.

TABLES: ekko, ekpo, ekbe, ekkn, lfa1, lfb1.

PARAMETERS:     p_bukrs LIKE lfb1-bukrs OBLIGATORY. SELECT-OPTIONS: s_ebeln FOR ekko-ebeln OBLIGATORY,
                s_aedat FOR ekko-aedat OBLIGATORY.

DATA: BEGIN OF po_int OCCURS 0,
        ebeln LIKE ekko-ebeln,
        ebelp LIKE ekpo-ebelp,
        lifnr LIKE ekko-lifnr,
        zekkn LIKE ekbe-zekkn,
        gjahr LIKE ekbe-gjahr,
        belnr LIKE ekbe-belnr,
        buzei LIKE ekbe-buzei,
        name1 LIKE lfa1-name1,
        ernam LIKE lfb1-ernam,
        sakto LIKE ekkn-sakto,
        gsber LIKE ekkn-gsber,
        kostl LIKE ekkn-kostl,
      END   OF po_int,
      po_wa LIKE po_int.

DATA: BEGIN OF ekko_int1 OCCURS 0,
        ebeln LIKE ekko-ebeln,
        lifnr LIKE ekko-lifnr,
      END   OF ekko_int1.
DATA: BEGIN OF ekko_int2 OCCURS 0.
        INCLUDE STRUCTURE ekko_int1.
DATA: END   OF ekko_int2.
DATA: BEGIN OF ekko_int3 OCCURS 0.
        INCLUDE STRUCTURE ekko_int1.
DATA: END   OF ekko_int3.

DATA: BEGIN OF ekpo_int1 OCCURS 0,
        ebeln LIKE ekpo-ebeln,
        ebelp LIKE ekpo-ebelp,
      END   OF ekpo_int1.
DATA: BEGIN OF ekpo_int2 OCCURS 0.
        INCLUDE STRUCTURE ekpo_int1.
DATA: END   OF ekpo_int2.
DATA: BEGIN OF ekpo_int3 OCCURS 0.
        INCLUDE STRUCTURE ekpo_int1.
DATA: END   OF ekpo_int3.

DATA: BEGIN OF ekbe_int1 OCCURS 0,
        ebeln LIKE ekbe-ebeln,
        ebelp LIKE ekbe-ebelp,
        zekkn LIKE ekbe-zekkn,
        gjahr LIKE ekbe-gjahr,
        belnr LIKE ekbe-belnr,
        buzei LIKE ekbe-buzei,
      END   OF ekbe_int1.
DATA: BEGIN OF ekbe_int2 OCCURS 0.
        INCLUDE STRUCTURE ekbe_int1.
DATA: END   OF ekbe_int2.
DATA: BEGIN OF ekbe_int3 OCCURS 0.
        INCLUDE STRUCTURE ekbe_int1.
DATA: END   OF ekbe_int3.

DATA: BEGIN OF lfa1_int1 OCCURS 0,
        lifnr LIKE lfa1-lifnr,
        name1 LIKE lfa1-name1,
      END   OF lfa1_int1.
DATA: BEGIN OF lfa1_int2 OCCURS 0.
        INCLUDE STRUCTURE lfa1_int1.
DATA: END   OF lfa1_int2.
DATA: BEGIN OF lfa1_int3 OCCURS 0.
        INCLUDE STRUCTURE lfa1_int1.
DATA: END   OF lfa1_int3.

DATA: BEGIN OF lfb1_int1 OCCURS 0,
        lifnr LIKE lfb1-lifnr,
        ernam LIKE lfb1-ernam,
      END   OF lfb1_int1.
DATA: BEGIN OF lfb1_int2 OCCURS 0.
        INCLUDE STRUCTURE lfb1_int1.
DATA: END   OF lfb1_int2.
DATA: BEGIN OF lfb1_int3 OCCURS 0.
        INCLUDE STRUCTURE lfb1_int1.
DATA: END   OF lfb1_int3.

DATA: BEGIN OF ekkn_int1 OCCURS 0,
        ebeln LIKE ekkn-ebeln,
        ebelp LIKE ekkn-ebelp,
        sakto LIKE ekkn-sakto,
        gsber LIKE ekkn-gsber,
        kostl LIKE ekkn-kostl,
      END   OF ekkn_int1.
DATA: BEGIN OF ekkn_int2 OCCURS 0.
        INCLUDE STRUCTURE ekkn_int1.
DATA: END   OF ekkn_int2.
DATA: BEGIN OF ekkn_int3 OCCURS 0.
        INCLUDE STRUCTURE ekkn_int1.
DATA: END   OF ekkn_int3.

DATA: start   TYPE i,
      end     TYPE i,
      dif     TYPE i.

************************************************************************
START-OF-SELECTION.
  PERFORM join.
  PERFORM outer_join.
  PERFORM for_all_entries.
  PERFORM nested.
  PERFORM unindexed_select.
  PERFORM join.
  PERFORM outer_join.
  PERFORM for_all_entries.
  PERFORM nested.
  PERFORM unindexed_select.
  PERFORM join.
  PERFORM outer_join.
  PERFORM for_all_entries.
  PERFORM nested.
  PERFORM unindexed_select.
  PERFORM join.
  PERFORM outer_join.
  PERFORM for_all_entries.
  PERFORM nested.
  PERFORM unindexed_select.
  PERFORM join.
  PERFORM outer_join.
  PERFORM for_all_entries.
  PERFORM nested.
  PERFORM unindexed_select.

*&---------------------------------------------------------------------*
*&      Form  join
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM join.

  REFRESH po_int.
  CLEAR   po_int.
  SELECT ekko~ebeln
         ekpo~ebelp ekko~lifnr
         ekbe~zekkn ekbe~gjahr ekbe~belnr ekbe~buzei
         lfa1~name1
         lfb1~ernam
         ekkn~sakto ekkn~gsber ekkn~kostl
    INTO  TABLE po_int
    FROM  ekko
    JOIN  ekpo ON   ekpo~ebeln = ekko~ebeln
    JOIN  lfa1 ON   lfa1~lifnr = ekko~lifnr
    JOIN  ekbe ON   ekbe~ebeln = ekpo~ebeln
               AND  ekbe~ebelp = ekpo~ebelp
    JOIN  lfb1 ON   lfb1~lifnr = ekko~lifnr
    JOIN  ekkn ON   ekkn~ebeln = ekpo~ebeln
               AND  ekkn~ebelp = ekpo~ebelp
    WHERE ekko~ebeln IN s_ebeln
    AND   ekbe~bwart = '101'
    AND   lfb1~bukrs = p_bukrs.

  REFRESH po_int.
  CLEAR   po_int.
  GET RUN TIME FIELD start.

  SELECT ekko~ebeln
         ekpo~ebelp ekko~lifnr
         ekbe~zekkn ekbe~gjahr ekbe~belnr ekbe~buzei
         lfa1~name1
         lfb1~ernam
         ekkn~sakto ekkn~gsber ekkn~kostl
    INTO  TABLE po_int
    FROM  ekko
    JOIN  ekpo ON   ekpo~ebeln = ekko~ebeln
    JOIN  lfa1 ON   lfa1~lifnr = ekko~lifnr
    JOIN  ekbe ON   ekbe~ebeln = ekpo~ebeln
               AND  ekbe~ebelp = ekpo~ebelp
    JOIN  lfb1 ON   lfb1~lifnr = ekko~lifnr
    JOIN  ekkn ON   ekkn~ebeln = ekpo~ebeln
               AND  ekkn~ebelp = ekpo~ebelp
    WHERE ekko~ebeln IN s_ebeln
    AND   ekbe~bwart = '101'
    AND   lfb1~bukrs = p_bukrs.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for inner joined select', ':', dif, 'microseconds'.

ENDFORM.                    " join

*&---------------------------------------------------------------------*
*&      Form  outer_join
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM outer_join.

  REFRESH po_int.
  CLEAR   po_int.
  SELECT ekko~ebeln
         ekpo~ebelp ekko~lifnr
         ekbe~zekkn ekbe~gjahr ekbe~belnr ekbe~buzei
         lfa1~name1
         lfb1~ernam
         ekkn~sakto ekkn~gsber ekkn~kostl
    INTO  TABLE po_int
    FROM  ekko
               JOIN  ekpo ON   ekpo~ebeln = ekko~ebeln
               JOIN  lfa1 ON   lfa1~lifnr = ekko~lifnr
               JOIN  ekbe ON   ekbe~ebeln = ekpo~ebeln
                          AND  ekbe~ebelp = ekpo~ebelp
               JOIN  lfb1 ON   lfb1~lifnr = ekko~lifnr
    LEFT OUTER JOIN  ekkn ON   ekkn~ebeln = ekpo~ebeln
                          AND  ekkn~ebelp = ekpo~ebelp
    WHERE ekko~ebeln IN s_ebeln
    AND   ekbe~bwart = '101'
    AND   lfb1~bukrs = p_bukrs.

  REFRESH po_int.
  CLEAR   po_int.
  GET RUN TIME FIELD start.

  SELECT ekko~ebeln
         ekpo~ebelp ekko~lifnr
         ekbe~zekkn ekbe~gjahr ekbe~belnr ekbe~buzei
         lfa1~name1
         lfb1~ernam
         ekkn~sakto ekkn~gsber ekkn~kostl
    INTO  TABLE po_int
    FROM  ekko
               JOIN  ekpo ON   ekpo~ebeln = ekko~ebeln
               JOIN  lfa1 ON   lfa1~lifnr = ekko~lifnr
               JOIN  ekbe ON   ekbe~ebeln = ekpo~ebeln
                          AND  ekbe~ebelp = ekpo~ebelp
               JOIN  lfb1 ON   lfb1~lifnr = ekko~lifnr
    LEFT OUTER JOIN  ekkn ON   ekkn~ebeln = ekpo~ebeln
                          AND  ekkn~ebelp = ekpo~ebelp
    WHERE ekko~ebeln IN s_ebeln
    AND   ekbe~bwart = '101'
    AND   lfb1~bukrs = p_bukrs.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for outer joined select', ':', dif, 'microseconds'.

ENDFORM.                    " outer_join

*&---------------------------------------------------------------------*
*&      Form  for_all_entries
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM for_all_entries.

  REFRESH: ekko_int1,
           ekpo_int1,
           ekbe_int1,
           ekkn_int1,
           lfa1_int1,
           lfb1_int1.
  CLEAR  : ekko_int1,
           ekpo_int1,
           ekbe_int1,
           ekkn_int1,
           lfa1_int1,
           lfb1_int1.
  SELECT ebeln lifnr
    FROM ekko
    INTO TABLE ekko_int1
    WHERE ebeln IN s_ebeln.

  SELECT ebeln ebelp
    FROM ekpo
    INTO TABLE ekpo_int1
    FOR ALL ENTRIES IN ekko_int1
    WHERE ebeln = ekko_int1-ebeln.

  SELECT lifnr name1
    FROM lfa1
    INTO TABLE lfa1_int1
    FOR ALL ENTRIES IN ekko_int1
    WHERE lifnr = ekko_int1-lifnr.

  SELECT ebeln ebelp zekkn gjahr belnr buzei
    FROM ekbe
    INTO TABLE ekbe_int1
    FOR ALL ENTRIES IN ekpo_int1
    WHERE ebeln = ekpo_int1-ebeln
    AND   ebelp = ekpo_int1-ebelp
    AND   ekbe~bwart = '101'.

  SELECT lifnr ernam
    FROM lfb1
    INTO TABLE lfb1_int1
    FOR ALL ENTRIES IN lfa1_int1
    WHERE lifnr = lfa1_int1-lifnr
    AND   bukrs = p_bukrs.

  SELECT ebeln ebelp sakto gsber kostl
    FROM ekkn
    INTO TABLE ekkn_int1
    FOR ALL ENTRIES IN ekpo_int1
    WHERE ebeln = ekpo_int1-ebeln
    AND   ebelp = ekpo_int1-ebelp.

  REFRESH: ekko_int1,
           ekpo_int1,
           ekbe_int1,
           ekkn_int1,
           lfa1_int1,
           lfb1_int1.
  CLEAR  : ekko_int1,
           ekpo_int1,
           ekbe_int1,
           ekkn_int1,
           lfa1_int1,
           lfb1_int1.
  GET RUN TIME FIELD start.

  SELECT ebeln lifnr
    FROM ekko
    INTO TABLE ekko_int1
    WHERE ebeln IN s_ebeln.

  SELECT ebeln ebelp
    FROM ekpo
    INTO TABLE ekpo_int1
    FOR ALL ENTRIES IN ekko_int1
    WHERE ebeln = ekko_int1-ebeln.

  SELECT lifnr name1
    FROM lfa1
    INTO TABLE lfa1_int1
    FOR ALL ENTRIES IN ekko_int1
    WHERE lifnr = ekko_int1-lifnr.

  SELECT ebeln ebelp zekkn gjahr belnr buzei
    FROM ekbe
    INTO TABLE ekbe_int1
    FOR ALL ENTRIES IN ekpo_int1
    WHERE ebeln = ekpo_int1-ebeln
    AND   ebelp = ekpo_int1-ebelp
    AND   ekbe~bwart = '101'.

  SELECT lifnr ernam
    FROM lfb1
    INTO TABLE lfb1_int1
    FOR ALL ENTRIES IN lfa1_int1
    WHERE lifnr = lfa1_int1-lifnr
    AND   bukrs = p_bukrs.

  SELECT ebeln ebelp sakto gsber kostl
    FROM ekkn
    INTO TABLE ekkn_int1
    FOR ALL ENTRIES IN ekpo_int1
    WHERE ebeln = ekpo_int1-ebeln
    AND   ebelp = ekpo_int1-ebelp.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for all entries        ', ':', dif, 'microseconds'.

ENDFORM.                    " for_all_entries

*&---------------------------------------------------------------------*
*&      Form  nested
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM nested.

  REFRESH: ekko_int2,
           ekpo_int2,
           ekbe_int2,
           ekkn_int2,
           lfa1_int2,
           lfb1_int2.
  CLEAR  : ekko_int2,
           ekpo_int2,
           ekbe_int2,
           ekkn_int2,
           lfa1_int2,
           lfb1_int2.
  SELECT ebeln lifnr
    FROM ekko
    INTO ekko_int2
    WHERE ebeln IN s_ebeln.

    APPEND ekko_int2.

    SELECT ebeln ebelp
      FROM ekpo
      INTO ekpo_int2
      WHERE ebeln = ekko_int2-ebeln.

      APPEND ekpo_int2.

      SELECT ebeln ebelp zekkn gjahr belnr buzei
        FROM ekbe
        INTO ekbe_int2
        WHERE ebeln = ekpo_int2-ebeln
        AND   ebelp = ekpo_int2-ebelp
        AND   bwart = '101'.

        APPEND ekbe_int2.

      ENDSELECT.

      SELECT ebeln ebelp sakto gsber kostl
        FROM ekkn
        INTO ekkn_int2
        WHERE ebeln = ekpo_int2-ebeln
        AND   ebelp = ekpo_int2-ebelp.

        APPEND ekkn_int2.

      ENDSELECT.

    ENDSELECT.

    SELECT lifnr name1
      FROM lfa1
      INTO lfa1_int2
      WHERE lifnr = ekko_int2-lifnr.

      APPEND lfa1_int2.

      SELECT lifnr ernam
        FROM lfb1
        INTO  lfb1_int2
        WHERE lifnr = lfa1_int2-lifnr
        AND   bukrs = p_bukrs.

        APPEND lfb1_int2.

      ENDSELECT.

    ENDSELECT.

  ENDSELECT.

  REFRESH: ekko_int2,
           ekpo_int2,
           ekbe_int2,
           ekkn_int2,
           lfa1_int2,
           lfb1_int2.
  CLEAR  : ekko_int2,
           ekpo_int2,
           ekbe_int2,
           ekkn_int2,
           lfa1_int2,
           lfb1_int2.
  GET RUN TIME FIELD start.

  SELECT ebeln lifnr
    FROM ekko
    INTO ekko_int2
    WHERE ebeln IN s_ebeln.

    APPEND ekko_int2.

    SELECT ebeln ebelp
      FROM ekpo
      INTO ekpo_int2
      WHERE ebeln = ekko_int2-ebeln.

      APPEND ekpo_int2.

      SELECT ebeln ebelp zekkn gjahr belnr buzei
        FROM ekbe
        INTO ekbe_int2
        WHERE ebeln = ekpo_int2-ebeln
        AND   ebelp = ekpo_int2-ebelp
        AND   bwart = '101'.

        APPEND ekbe_int2.

      ENDSELECT.

      SELECT ebeln ebelp sakto gsber kostl
        FROM ekkn
        INTO ekkn_int2
        WHERE ebeln = ekpo_int2-ebeln
        AND   ebelp = ekpo_int2-ebelp.

        APPEND ekkn_int2.

      ENDSELECT.

    ENDSELECT.

    SELECT lifnr name1
      FROM lfa1
      INTO lfa1_int2
      WHERE lifnr = ekko_int2-lifnr.

      APPEND lfa1_int2.

      SELECT lifnr ernam
        FROM lfb1
        INTO  lfb1_int2
        WHERE lifnr = lfa1_int2-lifnr
        AND   bukrs = p_bukrs.

        APPEND lfb1_int2.

      ENDSELECT.

    ENDSELECT.

  ENDSELECT.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for nested select      ', ':', dif, 'microseconds'.

ENDFORM.                    " nested

*&---------------------------------------------------------------------*
*&      Form  unindexed_select
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM unindexed_select.

  REFRESH po_int.
  CLEAR   po_int.
  SELECT ekko~ebeln
         ekpo~ebelp ekko~lifnr
         ekbe~zekkn ekbe~gjahr ekbe~belnr ekbe~buzei
         lfa1~name1
         lfb1~ernam
         ekkn~sakto ekkn~gsber ekkn~kostl
    INTO  TABLE po_int
    FROM  ekko
    JOIN  ekpo ON   ekpo~ebeln = ekko~ebeln
    JOIN  lfa1 ON   lfa1~lifnr = ekko~lifnr
    JOIN  ekbe ON   ekbe~ebeln = ekpo~ebeln
               AND  ekbe~ebelp = ekpo~ebelp
    JOIN  lfb1 ON   lfb1~lifnr = ekko~lifnr
    JOIN  ekkn ON   ekkn~ebeln = ekpo~ebeln
               AND  ekkn~ebelp = ekpo~ebelp
    WHERE ekko~aedat IN s_aedat
    AND   ekbe~bwart = '101'
    AND   lfb1~bukrs = p_bukrs.

  REFRESH po_int.
  CLEAR   po_int.
  GET RUN TIME FIELD start.

  SELECT ekko~ebeln
         ekpo~ebelp ekko~lifnr
         ekbe~zekkn ekbe~gjahr ekbe~belnr ekbe~buzei
         lfa1~name1
         lfb1~ernam
         ekkn~sakto ekkn~gsber ekkn~kostl
    INTO  TABLE po_int
    FROM  ekko
    JOIN  ekpo ON   ekpo~ebeln = ekko~ebeln
    JOIN  lfa1 ON   lfa1~lifnr = ekko~lifnr
    JOIN  ekbe ON   ekbe~ebeln = ekpo~ebeln
               AND  ekbe~ebelp = ekpo~ebelp
    JOIN  lfb1 ON   lfb1~lifnr = ekko~lifnr
    JOIN  ekkn ON   ekkn~ebeln = ekpo~ebeln
               AND  ekkn~ebelp = ekpo~ebelp
    WHERE ekko~aedat IN s_aedat
    AND   ekbe~bwart = '101'
    AND   lfb1~bukrs = p_bukrs.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for unindexed select   ', ':', dif, 'microseconds'.

  SKIP.

ENDFORM.                    " unindexed_select

Message was edited by: Rob Burbank

Former Member · ‎05-22-2006

Hai John

Check with the following Code

************************************************************************

Table Declaration *

************************************************************************

TABLES: mara,

marc,

mard.

************************************************************************

Types Declaration *

************************************************************************

TYPES: BEGIN OF typ_mara,

matnr TYPE mara-matnr, "Material Number"

mbrsh TYPE mara-mbrsh, "Industrial Sector"

mtart TYPE mara-mtart, "Material Type"

meins TYPE mara-meins, "Base Unit of Measure"

END OF typ_mara.

TYPES: BEGIN OF typ_makt,

matnr TYPE makt-matnr, "Material Number"

maktx TYPE makt-maktx, "Material Description"

END OF typ_makt.

TYPES: BEGIN OF typ_marc,

matnr TYPE marc-matnr, "Material Number"

werks TYPE marc-werks, "Plant Number"

END OF typ_marc.

TYPES: BEGIN OF typ_mard,

matnr TYPE marc-matnr, "Material Number"

werks TYPE marc-werks, "Plant Number"

lgort TYPE mard-lgort, "Storage Location"

END OF typ_mard.

************************************************************************

Intrnal tables Declaration *

************************************************************************

DATA: it_mara TYPE STANDARD TABLE OF typ_mara WITH HEADER LINE.

DATA: it_makt TYPE STANDARD TABLE OF typ_makt WITH HEADER LINE.

DATA: it_marc TYPE STANDARD TABLE OF typ_marc WITH HEADER LINE.

DATA: it_mard TYPE STANDARD TABLE OF typ_mard WITH HEADER LINE.

************************************************************************

Variable Declaration *

************************************************************************

DATA: v_count TYPE i.

************************************************************************

Selection Screen *

************************************************************************

SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS : s_matnr FOR mara-matnr.

SELECTION-SCREEN : SKIP.

PARAMETERS : p_mtart LIKE mara-mtart.

SELECTION-SCREEN : END OF BLOCK b1.

************************************************************************

Start Events *

************************************************************************

Initialization *

************************************************************************

INITIALIZATION.

***

PERFORM initial_input.

************************************************************************

At Selection-screen *

************************************************************************

AT SELECTION-SCREEN.

***

PERFORM validte_inputdata.

************************************************************************

Start of Selection *

************************************************************************

START-OF-SELECTION.

***

SELECT matnr

mbrsh

mtart

meins

INTO TABLE it_mara

FROM mara

WHERE matnr IN s_matnr and

mtart = p_mtart.

if sy-subrc = 0.

sort it_mara by matnr.

else.

MESSAGE e001 WITH 'No data Found' ' For the Given'

'Selection Criteria'(400).

endif.

IF NOT it_mara[] IS INITIAL.

SELECT matnr

maktx

INTO TABLE it_makt FROM makt

FOR ALL ENTRIES IN it_mara

WHERE matnr = it_mara-matnr

AND spras = sy-langu.

if sy-subrc = 0.

sort it_makt by matnr.

endif.

ENDIF.

IF NOT it_mara[] IS INITIAL.

select matnr

werks

from marc

into table it_marc

for all entries in it_mara

where matnr = it_mara-matnr.

if sy-subrc = 0.

sort it_marc by matnr werks.

endif.

IF NOT it_marc[] IS INITIAL.

select matnr

werks

lgort

from mard

into table it_mard

for all entries in it_marc

where matnr = it_marc-matnr and

werks = it_marc-werks.

if sy-subrc = 0.

sort it_mard by matnr werks lgort.

endif.

DATA: a TYPE i.

loop at it_mara.

a = sy-tabix MOD 2.

IF a = 1.

FORMAT COLOR 5.

ELSE.

FORMAT COLOR OFF.

ENDIF.

read table it_marc with key matnr = it_mara-matnr

binary search.

if sy-subrc = 0.

read table it_mard with key matnr = it_marc-matnr

werks = it_marc-werks

binary search.

if sy-subrc = 0.

read table it_makt with key matnr = it_mara-matnr

binary search.

if sy-subrc = 0.

WRITE:/ sy-vline.

WRITE: 2 it_mara-matnr,

18 sy-vline, it_mara-mbrsh,

38 sy-vline, it_mara-mtart,

54 sy-vline, it_mara-meins,

78 sy-vline, it_makt-maktx,

120 sy-vline, it_marc-werks,

135 sy-vline, it_mard-lgort, 155 sy-vline.

clear : it_mara.

endif.

clear : it_makt.

endif.

endloop.

WRITE: /(155) sy-uline.

&----

*& Form Initial_Input

&----

Initailization of Select Option

----

FORM initial_input .

CLEAR s_matnr.

REFRESH s_matnr.

s_matnr-low = '100-100'.

s_matnr-high = '111-111'.

s_matnr-sign = 'I'.

s_matnr-option = 'BT'.

APPEND s_matnr.

p_mtart = 'ROH'.

ENDFORM. " Initial_Input

&----

*& Form Validte_Inputdta

&----

Validation of Select Option

----

FORM validte_inputdata .

SELECT SINGLE * FROM mara

WHERE matnr IN s_matnr.

IF sy-subrc <> 0.

MESSAGE e001 WITH 'Material'(002) 'Type'(003) 'Does Not Exit'(400).

ENDIF.

ENDFORM. " Validte_Inputdta

Thanks & regards

Sreeni

Former Member · ‎05-22-2006

Hi,

Usually, the first thing I would do for these kind of requirements is to use the SAP tool called Quick Viewer (T.Code: SQVI).

This is handy and convenient tool to desing queries.

I would first create a query here with all my tables (6) and then analyse the code it generates.

Note: By default, joins between the tables are of type INNER. In somecases you need to right click on the join link and change it to OUTER.

Hope this helps.

Regards,

Sumant.