topic Re: inner join and into corresponding fields performance, in Application Development and Automation Discussions

inner join and into corresponding fields performance,

Former Member — Mon, 06 Oct 2008 06:53:32 GMT

hi ,

i am still not very clear about the performance aspect of inner join and into corresponding fields...

i know that corresponding fields affects memory peformance ..but does it affect database performance also ie can we have time_out in a select into corresponding fields?

also i am not still clear why inner join sometimes improves databse performance for eg i want to select from mkpf and then mseg i have all the key fields so should i go for fae and select individually or do a inner join....

i am rellay confused with these two points and very interested to understand these concepts...

Re: inner join and into corresponding fields performance,

former_member195383 — Mon, 06 Oct 2008 07:02:16 GMT

Hi Sweta

If you use innerjoin timeout error may occur if there are a large number of entries...

But performance wise innerjoin is preferable...

if u are facing time out error the you can do one thing..

Loop at one table and put the select query on the other table inside that. Performance wise its not prefferable..But to avoid time out error you can use the same.

Re: inner join and into corresponding fields performance,

Former Member — Mon, 06 Oct 2008 07:22:54 GMT

Hi Sweta,

an inner join can be faster than several separated SQL calls because the database needs to scan the necessary tables/indexes only once - and in an efficient manner because it knows in advance that you have to retrieve data form another table via join.

The performance is mainly depending on

your WHERE clause for filtering the result set (should be effective reduce the number of rows you need for

processing after in your program)

if indexes can be used or not (indexes of the joined tables should contain the join fields and fields of the WHERE clause

If you have a join like

Select scarrid~carrid  spfli~cityfrom 
     from scarr innerjoin spfli on scarrid~carrid = spfli~carrid 
      into corresponding fields of table itab 
     where spfli~cityfrom ='New York'
endselect .

it is send to the database and it will be in general processed in these steps:

1. parse (compile the SQL)

2. open (define a memory area and pointer to the result set : the cursor)

3a. 1st fetch (get the result set once , set the cursor at the 1st row; fill the corresponding fields)

3b. next fetches ( loop through it using the cursor ; fill the corresponding fields)

4. close (free the memory area of the cursor)

You see the performance is mainly defined in step 3a where the complete result set is retrieved.

Storing the cursor values into the corresponding fields in your program has only a marginal effect on the performance - nevertheless you should always be carefull in using resources as memory and such.

bye

Re: inner join and into corresponding fields performance,

Former Member — Mon, 06 Oct 2008 07:49:37 GMT

@YukonKid

1. parse (compile the SQL)

2. open (define a memory area and pointer to the result set : the cursor)

3a. 1st fetch (get the result set once , set the cursor at the 1st row; fill the corresponding fields)

Are you really sure. I think the time for setting the first cursor is counted in the open operator, if we are talking of ST05 operations.

Re: inner join and into corresponding fields performance,

Former Member — Mon, 06 Oct 2008 07:54:52 GMT

The into corrsponding fields is definitely no large overhead.

And about the join, there is unfortunately no general solution, it depends on the available indexes for the involved tables.

A join should be good:

+ if the WHERE condition can restrict the number of hits on one table to a reasonable number and uses an index

+ and if the ON condition for the next table uses fields on an index

Join can go wrong, if too many indexes are available, then a wrong one can be used, when leads to a poor performance.

Always check SQL trace, check whether the join works correctly.

Siegfried

Re: inner join and into corresponding fields performance,

Former Member — Tue, 07 Oct 2008 10:28:31 GMT

Hi,

Inner joins are very fast when joining the header and item table with all the key fields in the statement. Also when joining 2 tables, check the technical setting of the tables you are joining. The combined no of records in the table should not exceed 100,000 records.

Instead of using into corrosponding , declare the internal table with only the fields that you are selecting in the same order and directly use into, the select won't search for corrosponding field in ITAB but directly move the data in the order of selection.

Regards,

Prashant.

Re: inner join and into corresponding fields performance,

Former Member — Tue, 07 Oct 2008 10:34:55 GMT

> The combined no of records in the table should not exceed 100,000 records.

of course smaller is faster, but if it does then the join is also the only option, a FAE is even worse.

And be aware that the into-corresponding is by orders faster than the select, so be careful with into corresponding with buffered tables, but with slow selects, the overhead is maybe 0.001%

Siegfried

Re: inner join and into corresponding fields performance,

Former Member — Tue, 14 Oct 2008 13:13:11 GMT

> @YukonKid

> 1. parse (compile the SQL)

> 2. open (define a memory area and pointer to the result set : the cursor)

> 3a. 1st fetch (get the result set once , set the cursor at the 1st row; fill the corresponding fields)

> Are you really sure. I think the time for setting the first cursor is counted in the open operator, if we are talking of ST05 operations.

Hi Siegfrid,

maybe it was not clear what I want to express:

if you issue a query such as:

select *  from a_millions_row_table

there will be no I/O , until you actually FETCH a row, then and only then data will be read and

processing starts.

maybe it's clear now.

bye