queries

[Former Member]

hi

i have some queries wrt version 4.6c and above.

1. which would be better, using:

FOR ALL ENTRIES or using JOIN conditions

2. is it obeselte to use "CORRESPONDING"

FOR EG MOVE-CORRESPONDING: ITAB1-WERKS TO ITAB2-WERKS.

John

[Former Member · Accepted solution]

hi,

Corresponding should not be used in Selects as it affects the performance. Instead we can declare an internal table with hte required fields and say INTO TABLE ITAB instead of saying INTO CORRESPONDING FIELDS OF ITAB.

Regards,

Sailaja.

[Former Member · Accepted solution]

use FOR ALL ENTRIES. Performance still may be pretty slow considering that you are processing millions of records. But I think that it will be definitly better than doing Loop-Select-Endloop. The reason is that you will be making a round trip 4 million times when doing a loop. If you use FOR ALL ENTRIES, it is 1 round trip and the database is doing all of the work, not the appliation server. A round trip means from application server to database server and back to appication server. Make sense?

Steps that might make FOR ALL ENTRIES more efficient:

1.Removing duplicates from the the driver table

2.Sorting the driver table

3.If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement.

4.Use Package SIZE for memory critical operations

[Former Member]

Hi john,

If you have very huge amount of data i.e thousands of recs to tranfered,then you can go for For all entries with additional 'Appending table..' stmt . suppose if u want to tranfer data from table1(tb_bkpf) to table2(tb_bseg) , u need to take an intermidiate table (TB_BKPF_TEMP) and do the respective coding as below,

DATA : L_LINES TYPE I, " No. of lines in tb_bkpf

L_START TYPE I, " Starting line

L_END TYPE I, " End line

L_SYTABIX LIKE SY-TABIX . " Index

• Fetching the data from BSEG for each 1000 entries in BKPF to

• reduce the overhead of database extraction.

CLEAR L_LINES .

DESCRIBE TABLE TB_BKPF LINES L_LINES.

IF L_LINES >= 1.

CLEAR: L_START, L_END.

DO.

L_START = L_END + 1.

L_END = L_END + 1000.

IF L_END > L_LINES.

L_END = L_LINES.

ENDIF.

• Populating the tb_bseg_tmp in the order of the database table

APPEND LINES OF TB_BKPF FROM L_START TO L_END TO TB_BKPF_TEMP.

SELECT BUKRS

BELNR

GJAHR

BUZEI

DMBTR

WRBTR

ZUONR

HKONT

LIFNR

MATNR

WERKS

MENGE

MEINS

EBELN

EBELP

FROM BSEG

APPENDING TABLE TB_BSEG

FOR ALL ENTRIES IN TB_BKPF_TEMP

WHERE BUKRS = TB_BKPF_TEMP-BUKRS

AND BELNR = TB_BKPF_TEMP-BELNR

AND GJAHR = TB_BKPF_TEMP-GJAHR

AND LIFNR IN S_LIFNR

AND WERKS IN S_WERKS

AND EBELN IN S_EBELN.

REFRESH TB_BKPF_TEMP.

IF L_END >= L_LINES.

EXIT.

ENDIF.

ENDDO.

ENDIF.

ENDIF.

Regards,

Kiran

[Former Member]

thanks a lot for your suggestions

[Former Member]

anyways thanks

now my query is :

if i have huge data

then which would be better for all entries or join conditions.

[former_member927251]

Better to use for all entries.

For all entries takes less database time as compared to join

Regards,

Amit Mishra

[Former Member]

Hi,

For huge data, its better to use For all entries..

Check whether the itab is not initial before using For all entries.

Its saves database load.

Regards,

Tanveer.

Mark helpful answers

[Former Member]

if u use join of 2 or 3 table than it is good . but more than 2 or 3 table join is not suggested.

and for cluster table join is not possible..

[former_member188685]

Hi John,

Use for all entries if you have large data.

that will reduce some load on DB.

Regards

vijay

[Former Member]

Hi,

Whether to use for all entries or join condition depends on the amount of data in the table.

Its not obselete to use Move corresponding. However one should avoid using it.

Regards,

Tanveer.

Mark helpful answers

[former_member181962]

1) Both have same efficiency as long as the number of tables involved is not too large.Infact they are better alternatives to nested selects.

2) MOve correspoding takes more time because the system need to map the fields in the first structure with the second, and then move the data.

and moreover, you need to have the same field names in both internal tables.

Regards,

Ravi