10-05-2007 1:29 PM
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.
10-05-2007 1:33 PM
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.
10-05-2007 1:33 PM
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.
10-05-2007 1:34 PM
Hi
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
10-05-2007 1:34 PM
Hi,
performance will get affected when u join more tables.
Try changing it to FOR ALL ENTRIES
10-05-2007 1:35 PM
Hi Shori,
For better performance you can join only four tables in one select query.
Regards,
Hemnat
10-05-2007 1:36 PM
Hi Shori,
Use database view. That will be efficient in this kind of scenarios.
10-05-2007 1:37 PM
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
10-05-2007 1:38 PM
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.