Performance Tunning

[Former Member]

which is better performance wise,

for all entries

views

or subquries

please help

[Former Member]

Hi

FOR ALL ENTRIES is the berret way than out of these 3 ways

1) For all Entreis used mainly to retrive the data from more than one table

instead of Using Nested Selected and Joins ..

2) Performance wise , For all Entreis is better than the joins and also Nested Selected Statements.

<b>Reward if usefull</b>

[Former Member]

HI

1)The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

The plus

Large amount of data

Mixing processing and reading of data

Fast internal reprocessing of data

Fast

The Minus

Difficult to program/understand

Memory could be critical (use FREE or PACKAGE size)

Points to be must considered FOR ALL ENTRIES

Check that data is present in the driver table

Sorting the driver table 

Removing duplicates from the driver table

Consider the following piece of extract

Loop at int_cntry.

       Select single * from zfligh into int_fligh

 where cntry = int_cntry-cntry.

 Append int_fligh.

 Endloop.

The above mentioned can be more optimized by using the following code.

Sort int_cntry by cntry.

Delete adjacent duplicates from int_cntry.

If NOT int_cntry[] is INITIAL.

            Select * from zfligh appending table int_fligh

            For all entries in int_cntry

            Where cntry = int_cntry-cntry.

Endif.

1)Its better to use a views instead of nested Select statements.

2)To read data from several logically connected tables use a join instead of nested Select statements. Joins are preferred only if all the primary key are available in WHERE clause for the tables that are joined. If the primary keys are not provided in join the Joining of tables itself takes time.

3)Instead of using nested Select loops it is often better to use subqueries.

Point # 1

SELECT * FROM DD01L INTO DD01L_WA
WHERE DOMNAME LIKE 'CHAR%'
AND AS4LOCAL = 'A'.
SELECT SINGLE * FROM DD01T INTO DD01T_WA
WHERE DOMNAME = DD01L_WA-DOMNAME
AND AS4LOCAL = 'A'
AND AS4VERS = DD01L_WA-AS4VERS
AND DDLANGUAGE = SY-LANGU.
ENDSELECT.

The above code can be more optimized by extracting all the data from view DD01V_WA
SELECT * FROM DD01V INTO DD01V_WA
WHERE DOMNAME LIKE 'CHAR%'
AND DDLANGUAGE = SY-LANGU.
ENDSELECT

Point # 2

SELECT * FROM EKKO INTO EKKO_WA.

SELECT * FROM EKAN INTO EKAN_WA

WHERE EBELN = EKKO_WA-EBELN.

ENDSELECT.

ENDSELECT.

The above code can be much more optimized by the code written below.

SELECT P_{F1 P}F2 F_{F3 F}F4 INTO TABLE ITAB

FROM EKKO AS P INNER JOIN EKAN AS F

ON P_{EBELN = F}EBELN.

Point # 3

SELECT * FROM SPFLI
INTO TABLE T_SPFLI
WHERE CITYFROM = 'FRANKFURT'
AND CITYTO = 'NEW YORK'.
SELECT * FROM SFLIGHT AS F
INTO SFLIGHT_WA
FOR ALL ENTRIES IN T_SPFLI
WHERE SEATSOCC < F~SEATSMAX
AND CARRID = T_SPFLI-CARRID
AND CONNID = T_SPFLI-CONNID
AND FLDATE BETWEEN '19990101' AND '19990331'.
ENDSELECT.
The above mentioned code can be even more optimized by using subqueries instead of for all entries.
SELECT * FROM SFLIGHT AS F INTO SFLIGHT_WA
WHERE SEATSOCC < F~SEATSMAX
AND EXISTS ( SELECT * FROM SPFLI
WHERE CARRID = F~CARRID
AND CONNID = F~CONNID
AND CITYFROM = 'FRANKFURT'
AND CITYTO = 'NEW YORK' )
AND FLDATE BETWEEN '19990101' AND '19990331'.
ENDSELECT.

Reward if usefull

[Former Member]

Hi Sirisha,

FOR ALL ENTRIES is not always faster then joins or subqueries. The correct answer is: It depends.

From an performance perspective views are not relevant. In relational systems views are designed to reduce the amount of data shown to the user instead of increasing the performance of an application. All queries formulated with views could also be formulated without the view. But subqueries and joins (may be you mean joins where you've written view) could improve performance dramatically. In SAP R3 in earlier releases, views were the only possibility to formulate joins.

Which statement is the best from a performance perspective depends on the type of statement. The difference between the different statements is where the load to the database server is higher. The goal should be to reduce the load to the database server, because it will improve scalability of applicationw. But if your application sends millions of rows to the database server to query lots of rows by primary key, it could be better to use a join or subquery instead of FOR ALL ENTRIES. If the SQL statement includes complex expressions, then it is probably a candidate for FOR ALL ENTRIES because the database load will be much higher executing those expressions instead of distribute them to the application servers (if you have not only a central instance together with the database).

Regards

Ralph

[Former Member]

Usually, JOINs are a bit quicker:

<a href="/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better">JOINS vs. FOR ALL ENTRIES - Which Performs Better?</a>

Rob

[Former Member]

@Naresh,

I am getting tired of your answers, they are always the same (copied), and often wrong!

Actually there is not general answer for this question. In principle the three access types are no really comparable.

Join and view are similar, a view is join definition in the ddic. The execution is always the join. They are used if you need combined data from several db tables.

FOR ALL ENTRIES are mainly used, if you needing corresponding data for record which you have already in an internal table.

Sometimes joins are not processed in the optimal way, the optimzer starts with the wrong table or the wrong index for the first table, making the performance very bad. Then you should whether you can improve the join behavior. In some cases,

a split into a select and select FOR ALL ENTRIES can be the only option. But these cases are anyway compromises.

Subqueries fit only in special cases.

Siegfried

[Former Member]

TABLES: bsik, bsak, bseg.

SELECT-OPTIONS: s_bukrs FOR bsik-bukrs OBLIGATORY,

s_lifnr FOR bsik-lifnr OBLIGATORY.

DATA: BEGIN OF bsik_int OCCURS 0.

INCLUDE STRUCTURE bsik.

DATA: END OF bsik_int.

DATA: BEGIN OF bseg_int OCCURS 0.

INCLUDE STRUCTURE bseg.

DATA: END OF bseg_int.

SELECT * FROM bsik

INTO TABLE bsik_int

WHERE bukrs IN s_bukrs

AND lifnr IN s_lifnr.

SELECT * FROM bsak

APPENDING TABLE bsik_int

WHERE bukrs IN s_bukrs

AND lifnr IN s_lifnr.

SELECT * FROM bseg

INTO TABLE bseg_int

FOR ALL ENTRIES IN bsik_int

WHERE bukrs = bsik_int-bukrs

AND belnr = bsik_int-belnr

AND gjahr = bsik_int-gjahr

AND ebeln = space.

from

mukesh goyal