topic Re: Inner join in Application Development and Automation Discussions

Inner join

Former Member — Fri, 05 Oct 2007 12:29:52 GMT

hi,

I have got a scenario in which i need to fetch values from 5 tables which contains more that 2 lakh entries.

If i use inner join query it takes 2 min for executing that select query. Sometimes it goes to dump.

Can anyone pls help me.

Re: Inner join

Former Member — Fri, 05 Oct 2007 12:33:48 GMT

try "FOR ALL Entries"....

search for the string "FOR ALL Entries" in the forum and you will get lot of entries.

Please reward the helpful entries.

Regards,

Raman.

Re: Inner join

Former Member — Fri, 05 Oct 2007 12:34:07 GMT

It depends on the tables data from which you are fetching

All tables should have proper keys linked with each other then it works fine

other wise it may lead to dump

unless you tell the tables it is not easy to comment on joins

otherwise better to use join between first 2 then use for all entries and fetch data from other tables

Regards

Anji

Re: Inner join

Former Member — Fri, 05 Oct 2007 12:34:51 GMT

Hi,

performance will get affected when u join more tables.

Try changing it to FOR ALL ENTRIES

Re: Inner join

Former Member — Fri, 05 Oct 2007 12:35:31 GMT

Hi Shori,

For better performance you can join only four tables in one select query.

Regards,

Hemnat

Re: Inner join

asik_shameem — Fri, 05 Oct 2007 12:36:08 GMT

Hi Shori,

Use database view. That will be efficient in this kind of scenarios.

Re: Inner join

Former Member — Fri, 05 Oct 2007 12:37:04 GMT

Hi,

maybe this solve your problem

+The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table:

SELECT ... FOR ALL ENTRIES IN must be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN, and IN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

and

+Tabular conditions

DATA: BEGIN OF LINE,

CARRID TYPE SPFLI-CARRID,

CONNID TYPE SPFLI-CONNID,

CITYFROM TYPE SPFLI-CITYFROM,

CITYTO TYPE SPFLI-CITYTO,

END OF LINE,

ITAB LIKE TABLE OF LINE.

LINE-CITYFROM = 'FRANKFURT'.

LINE-CITYTO = 'BERLIN'.

APPEND LINE TO ITAB.

LINE-CITYFROM = 'NEW YORK'.

LINE-CITYTO = 'SAN FRANCISCO'.

APPEND LINE TO ITAB.

SELECT CARRID CONNID CITYFROM CITYTO

INTO CORRESPONDING FIELDS OF LINE

FROM SPFLI

FOR ALL ENTRIES IN ITAB

WHERE CITYFROM = ITAB-CITYFROM AND CITYTO = ITAB-CITYTO.

WRITE: / LINE-CARRID, LINE-CONNID, LINE-CITYFROM, LINE-CITYTO.

ENDSELECT.

The output is as follows:

This example selects all lines in which the following conditions are fulfilled:

· The CITYFROM column contains FRANKFURT and the CITYTO column contains BERLIN.

· The CITYFROM column contains NEW YORK and the CITYTO column contains SAN FRANCISCO.

Tabular conditions

DATA: TAB_SPFLI TYPE TABLE OF SPFLI,

TAB_SFLIGHT TYPE SORTED TABLE OF SFLIGHT

WITH UNIQUE KEY TABLE LINE,

WA LIKE LINE OF TAB_SFLIGHT.

SELECT CARRID CONNID

INTO CORRESPONDING FIELDS OF TABLE TAB_SPFLI

FROM SPFLI

WHERE CITYFROM = 'NEW YORK'.

SELECT CARRID CONNID FLDATE

INTO CORRESPONDING FIELDS OF TABLE TAB_SFLIGHT

FROM SFLIGHT

FOR ALL ENTRIES IN TAB_SPFLI

WHERE CARRID = TAB_SPFLI-CARRID AND

CONNID = TAB_SPFLI-CONNID.

LOOP AT TAB_SFLIGHT INTO WA.

AT NEW CONNID.

WRITE: / WA-CARRID, WA-CONNID.

ENDAT.

WRITE: / WA-FLDATE.

ENDLOOP.

The output is as follows:

This example selects flight data from SFLIGHT for all connections for which the column CITYFROM in table SPFLI has the value NEW YORK. You could also use a join in the FROM clause to select the same data in a single SELECT statement.

Regards,

Gianpietro

Re: Inner join

Former Member — Fri, 05 Oct 2007 12:38:22 GMT

hi, It is not adviceable to use inner joins with more than 3 tables, and tables with size category 4. You can check the size category in se11 (technical settings). I think you should do the selection not in one step. Mention the size category, and you can join the smaller tables, then you can do the remaining selection in another simple selection.