topic Re: Using a JOIN Statement for performance tuning in Application Development and Automation Discussions

Using a JOIN Statement for performance tuning

Former Member — Thu, 28 Feb 2008 01:33:42 GMT

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.

Re: Using a JOIN Statement for performance tuning

Former Member — Thu, 28 Feb 2008 01:37:15 GMT

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

Re: Using a JOIN Statement for performance tuning

Former Member — Thu, 28 Feb 2008 02:55:27 GMT

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

Re: Using a JOIN Statement for performance tuning

Former Member — Thu, 28 Feb 2008 03:44:43 GMT

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.

Re: Using a JOIN Statement for performance tuning

Former Member — Thu, 28 Feb 2008 03:54:57 GMT

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

Re: Using a JOIN Statement for performance tuning

Former Member — Thu, 28 Feb 2008 09:01:54 GMT

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

not true in general, give back 6 points : )

Re: Using a JOIN Statement for performance tuning

Former Member — Fri, 29 Feb 2008 02:05:58 GMT

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

Re: Using a JOIN Statement for performance tuning

Former Member — Fri, 29 Feb 2008 02:06:34 GMT

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

Re: Using a JOIN Statement for performance tuning

Former Member — Fri, 29 Feb 2008 02:18:41 GMT

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

Re: Using a JOIN Statement for performance tuning

Former Member — Tue, 15 Apr 2008 06:22:54 GMT

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.