05-19-2006 9:24 AM
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.
05-19-2006 9:36 AM
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.
05-19-2006 9:36 AM
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.
05-19-2006 9:38 AM
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.
05-19-2006 9:37 AM
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,
05-19-2006 9:46 AM
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.
05-19-2006 4:11 PM
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?
05-19-2006 4:16 PM
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
05-22-2006 5:14 PM
<i>Mea culpa</i> Rich. The nested select performed quite a bit worse than I expected.
Rob
05-19-2006 4:13 PM
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>
05-19-2006 4:15 PM
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
05-19-2006 7:59 PM
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
05-22-2006 4:22 AM
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
05-22-2006 5:48 AM
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.
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.
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.
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
05-22-2006 5:49 PM
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.