Inner Join. How to improve the performance of inner join query

[Former Member]

Inner Join. How to improve the performance of inner join query.

Query is :

select f1~ablbelnr

f1~gernr

f1~equnr

f1~zwnummer

f1~adat

f1~atim

f1~v_zwstand

f1~n_zwstand

f1~aktiv

f1~adatsoll

f1~pruefzahl

f1~ablstat

f1~pruefpkt

f1~popcode

f1~erdat

f1~istablart

f2~anlage

f2~ablesgr

f2~abrdats

f2~ableinh

from eabl as f1

inner join eablg as f2

on f1_{ablbelnr = f2}ablbelnr

into corresponding fields of table it_list

where f1~ablstat in s_mrstat

%_HINTS ORACLE 'USE_NL (T_00 T_01) index(T_01 "EABLG~0")'.

I wanted to modify the query, since its taking lot of time to load the data.

Please suggest : -

---

Treat this is very urgent.

[Former Member]

hi Shyamal,

1. do not use " <b>into corresponding fields of table it_list</b> " and

2. the order of selection should be of the same order of your internal table i.e, it_list declaration.

Check this Out...

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

Regards,

Santosh

[Former Member]

Hi

Remove the " into corresponding fields of table it_list "

and use into table it_list

selected field should be in the same order that of it_list.

-

---

or -

---

use two different internal table

[Former Member]

Hi !!

If posiible don't use into corresponding fields of table .

insteed of that use into table.

Try to creat index on ablstat field for table eabl, then turn off hint ore creata one using that new index..

.If it already exists try to turn off hint and let the optimizer decide which method is the best.

BR, JAcek

[Former Member]

Hi Shyamal,

In your program , you are using "into corresponding fields of ".

Try not to use this addition in your select query.

Instead, just use "into table it_list".

As an example,

Just give a normal query using "into corresponding fields of" in a program. Now go to se30 ( Runtime analysis), and give the program name and execute it .

Now if you click on Analyze button , you can see, the analysis given for the query.The one given in "Red" line informs you that you need to find for alternate methods.

On the other hand, if you are using "into table itab", it will give you an entirely different analysis.

So try not to give "into corresponding fields" in your query.

Regards,

SP.

[Former Member]

Hi Kumar,

Trying creating a view using the required table and conditions

Some views already present for table EABL and EABLG are

U_30015

U_30100

V_EABL

Regards,

Sameena

[Former Member]

Neither table EAGL nor EAGLB are in our system, so I can't give you specific advice; however, there are a few things that you can do:

Get rid of the hints. This forces the use of an index even if it's not the best one to use. Oracle is good at figuring out which index to use on it's own.

Check that s_mrstat is not emnpty. If it is, you will select all entries from the tables.

Ensure that field ablstat is the first field in an index on table eabl.

Rob

[abdul_hakim]

hi

remove <b>into corresponding</b> addition use <b>into table</b> instead.

<b>into corresponding</b> will cause performance issue if the number of round trip is very small.

try to remove the join altogether split ur select into two statements and use <b>for all entries</b>.

Cheers,

Abdul Hakim

[Former Member]

Hi Shyamal,

There are many ways of improving the performance. In addtion to the ones suggested by other members:

1. you can create a view of the tables EABL and EABLG. Use this view to query using your SELECT statement.

2. Use indexing, if not there on these 2 tables to improve the query. Check indexes tab in SE11 for these 2 tables.

3. Retrieve data in these 2 tables (EABL and EABLG)in internal tables and then use these internal tables in your program. This is might work at times depending on various parameters.