Using a JOIN Statement for performance tuning

[Former Member]

Hi,

How can i performance tune these statements using JOIN.

SELECT * FROM table1 WHERE Pernr EQ A

AND begda EQ B

SELECT field3 field 4 INTO (Table2-field3, table2-field4) WHERE pernr = table1-pernr AND

field5 = table1-field 5 .

Is this statement by itself enough or its better to change it using JOIN?

Please Advise.

[Former Member]

Hi Celina,

Welcome to SDN

Can you give the exact name of tables. Whether performance will increase or not using JOINS is totally depend on the tables and the fields you have in WHERE clause.

Regards,

Atish

[Former Member]

Hi ,

Here is the SELECT STATEMENTS statement with table names.

DATA: pernrtab LIKE ptex2010-pernr OCCURS 0 WITH HEADER LINE.

SELECT * FROM ptex2010 WHERE statu3 = ' '

AND pernr EQ pernrtab.

SELECT zzsaut zzwork_cd zz_post1 INTO (cats_temp-zzsaut, cats_temp-zzwork_cd, cats_temp-zz_post1)

UP TO 1 ROWS FROM cats_temp

WHERE pernr = ptex2010-pernr

ENDSELECT.

Does this help you ?

Cheers,

C.J

[Former Member]

Hi Celina,

Change code as below

DATA: pernrtab LIKE ptex2010-pernr OCCURS 0 WITH HEADER LINE.

DATA : lt_ptex2010 TYPE TABLE OF ptex2010 OCCURS 0.

SELECT * FROM ptex2010 INTO TABLE lt_ptex2010 WHERE statu3 = ' '

AND pernr EQ pernrtab.

SELECT zzsaut zzwork_cd zz_post1 INTO TABLE <create a local table using thsese 3 fields>

FROM cats_temp

FOR ALL ENTRIES IN TABLE lt_ptex2010

WHERE pernr = lt_ptex2010 -pernr

Regards,

Atish

[Former Member]

JOINs generally give better performance than nested SELECTs, but there are other issues you should consider before worrying about this. The most important thing to consider here is the effective use of an index when doing the SELECT.

Rob

[Former Member]

Celina,

Instead of Join Statements Use FOR ALL ENTRIES .That will increase your select query performance.

SELECT * FROM table1 INTO TABLE i_table

WHERE Pernr EQ A AND

begda EQ B.

Here i_table1 is internal table which is having the same structure of

table1.

SELECT field3 field4

INTO CORRESPONDING FIELDS OF TABLE i_table2

FROM table2

FOR ALL ENTRIES IN i_table1

WHERE pernr = i_table1-pernr AND

field5 = i_table1-field 5 .

In i_table2 you will get all required values From table2 by comparing the value with i_table1.

[Former Member]

Hi,

Check if u can combine table1 and table2 with the key fields. (U haven't mentioned the table names). Using 'for all entires in table1' is also effective. But, make sure that u check table1 is not initial before using 'for all entries'.

Hope this helps u.

Regards,

Ramya

[Former Member]

Hi ,

Here is the SELECT STATEMENTS statement with table names.

DATA: pernrtab LIKE ptex2010-pernr OCCURS 0 WITH HEADER LINE.

SELECT * FROM ptex2010 WHERE statu3 = ' '

AND pernr EQ pernrtab.

SELECT zzsaut zzwork_cd zz_post1 INTO (cats_temp-zzsaut, cats_temp-zzwork_cd, cats_temp-zz_post1)

UP TO 1 ROWS FROM cats_temp

WHERE pernr = ptex2010-pernr

ENDSELECT.

Does this help you guys ?

Cheers,

C.J

[Former Member]

> Instead of Join Statements Use FOR ALL ENTRIES .That will increase your select query > performance.

not true in general, give back 6 points : )

[Former Member]

Hi celina,

If you are using all the required key fields than join will improve the performance of the program but if that is not the case than use FOR ALL ENTRIES and fetch all the relavant data in internal table and use this data to process in a loop.

And make sure the internal table which you are using in FOR ALL ENTRIES must be filled.