Hi

[Former Member]

IN THE PERFORMANCE POINT OF VIEW WHICH IS BETTER INNER JOIN OR FOR ALL ENTRIES

[Former Member]

IN THE PERFORMANCE POINT OF VIEW WHICH IS BETTER INNER JOIN OR FOR ALL ENTRIES

[Former Member]

Hi Deepu,

Welcome to SDN.....

Its a trade off. Some times INNER JOINS will give more performance rather FOR ALL ENTRIES and vice versa. But INNER JOINS with more than 2 levels will have major impact on performance.

Thanks,

Vinay

[Former Member]

Hi

Prformance point of view FOR ALL ENTRIES is very good comapred to JOINS

if use more than 3 tables than join will make big issue regarding performance

if use for all entries for any number of tables then performance won't be effected

.. [FOR ALL ENTRIES IN itab] WHERE sql_cond ... .

Effect:

The addition WHERE restricts the number of lines included in the result set by the statement SELECT, by using a logical expression sql_cond. The logical expression compares the content of columns in the database with the content of ABAP data objects, or with the content of other columns. You can use the optional addition FOR ALL ENTRIES to compare the content of a column in the database with a component with all lines of a structured internal table itab.

The logical expression sql_cond is either true, false, or unknown. The expression is unknown if one of the columns involved in the database contains a null value and is evaluated with another comparison as IS NULL. A line is only included in the resulting set if the logical expression is true.

Except for columns of type STRING or RAWSTRING, all columns of the database tables or views listed after FROM can be evaluated after the WHERE condition. The columns do not necessarily have to be a part of the resulting set.

Notes:

The client identifier cannot be queried in the WHERE condition, if automatic client handling is not deactivated using the addition CLIENT SPECIFIED after FROM.

The WHERE condition of the SELECT statement described here includes the WHERE conditions of the Open SQL statements DELETE, OPEN CURSOR, and UPDATE.

The logical expressions of the WHERE condition can also all be used after the addition HAVING, and sometimes in the ON condition of a Join expression after the addition FROM.

... [(] {dbtab_left [AS tabalias_left]} | join

{[INNER] JOIN}|{LEFT [OUTER] JOIN}

{dbtab_right [AS tabalias_right] ON join_cond} [)] ... .

Effect

The join syntax represents a recursively nestable join expression. A join expression consists of a left-hand and a right- hand side, which are joined either by means of [INNER] JOIN or LEFT [OUTER] JOIN . Depending on the type of join, a join expression can be either an inner ( INNER) or an outer (LEFT OUTER) join. Every join expression can be enclosed in round brackets. If a join expression is used, the SELECT command circumvents SAP buffering.

On the left-hand side, either a single database table, a view dbtab_left, or a join expression join can be specified. On the right-hand side, a single database table or a view dbtab_right as well as join conditions join_cond can be specified after ON. In this way, a maximum of 24 join expressions that join 25 database tables or views with each other can be specified after FROM.

AS can be used to specify an alternative table name tabalias for each of the specified database table names or for every view. A database table or a view can occur multiple times within a join expression and, in this case, have various alternative names.

The syntax of the join conditions join_cond is the same as that of the sql_cond conditions after the addition WHERE, with the following differences:

At least one comparison must be specified after ON.

Individual comparisons may be joined using AND only.

All comparisons must contain a column in the database table or the view dbtab_right on the right-hand side as an operand.

The following language elements may not be used: BETWEEN, LIKE, IN.

No sub-queries may be used.

For outer joins, only equality comparisons (=, EQ) are possible.

If an outer join occurs after FROM, the join condition of every join expression must contain at least one comparison between columns on the left-hand and the right-hand side.

In outer joins, all comparisons that contain columns as operands in the database table or the view dbtab_right on the right-hand side must be specified in the corresponding join condition. In the WHERE condition of the same SELECT command, these columns are not allowed as operands.

Resulting set for inner join

The inner join joins the columns of every selected line on the left- hand side with the columns of all lines on the right-hand side that jointly fulfil the join_cond condition. A line in the resulting set is created for every such line on the right-hand side. The content of the column on the left-hand side may be duplicated in this case. If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set.

Resulting set for outer join

The outer join basically creates the same resulting set as the inner join, with the difference that at least one line is created in the resulting set for every selected line on the left-hand side, even if no line on the right-hand side fulfils the join_cond condition. The columns on the right-hand side that do not fulfil the join_cond condition are filled with null values.

reward if usefull

[former_member189596]

HI deepu........

I am adding more to Vinay's answer........

i writing all the things which we need to take care while fetch data from DATABASE.

<b><u>Database Table Operations</u></b>

When data retrieved from the DB needs to be validated, then it should be applied to a bunch of records rather than individual records, wherever possible.

Consider the following examples:

SELECT EBELN, EKORG… INTO (EKPO-EBELN,…) FROM EKPO.

IF SY-DBCNT > 1000. EXIT. ENDIF.

WRITE:/ EKPO-EBELN, …

ENDSELECT.

The purpose is to fetch 1000 records from the DB table EKPO. The above statements retrieve a record from DB and checks its count (in the application server) and if it is > 1000 exits the loop. This means that the DB server is accessed 1000 times and also in the application server, the validation is done 1000 times. This is not advisable.

SELECT EBELN, EKORG… INTO (EKPO-EBELN,…) FROM EKPO.

IF SY-DBCNT > 1000. EXIT. ENDIF.

WRITE:/ EKPO-EBELN, …

ENDSELECT.

In the second statement above, though 1000 records are fetched one at a time, the count of the record is also made simultaneously unlike the first example where its done in the application server.

Another example on similar lines

PARAMETERS: PARAM1,

DATA: BEGIN OF SEARCH_STRING,

FIRST (9) VALUE ‘----

---

‘

PARAM,

END OF SEARCH_STRING.

SELECT VBELN, AUART…INTO (VBAK-VBELN,…..) FROM VBAK.

CHECK VBAK-VBELN+9(1) = PARAM1

WRITE:/ VBAK-VBELN, VBAK-AUART,….

ENDSELECT

In this case, the ‘check ‘ is done in the application server. Thus every record fetched from the DB has to be transferred to the application server.

SEARCH_STRING = PARAM1.

SELECT VBELN AUART…INTO (VBAK-VBELN…..) FROM VBAK

WHERE VBELN LIKE SEARCH_STRING.

WRITE:/ VBAK-VBELN, VBAK-AUART,….

ENDSELECT

In this case, the check is preferred to be done in the SELECT statement itself with LIKE , instead of a separate CHECK statement (that executes in the application server). This statement takes only 12% - 15% time of that of the first SELECT statement.

Note: When there are complex ‘checks’ to be done, it could be better to fetch the records first and then CHECK them separately. It’s a tradeoff again.

<b><u>Comment:</u></b>

After the DB record fetch if any validation is to be done, then explore the DB (SQL) features available to accomplish the same. This is a tradeoff between load on DB and application servers.

<b><u>• • Cursor Caching</u></b>

SQL statements are cached for improved performance on 2 levels, application server and DB server.

Consider the examples below:

SELECT EBELN EKORG INTO (EKPO-EBELN, EKPO-EKORG) FROM EKPO

WHERE EBELN = aaa AND EKORG = xxx.

SELECT EBELN EKORG INTO (EKPO-EBELN, EKPO-EKORG) FROM EKPO

WHERE EKORG = xxx AND EBELN = aaa.

The above 2 statements though give the same result, they require their own cursor since the statements are not identical (see the bold fonts above). This means there would be DECLARE and PREPARE statements internally, which are overheads for performance.

Secondly the sequence of fields specified in the WHERE clause matters as far as the performance is concerned. Its always advisable to have the sequence of fields in synch with that defined in the Data dictionary.

Comment:

When the same result is expected at more than one instance in a program, use identical SQL statements. This enables the usage of common cursor cache and improves the performance. The sequence of fields in the WHERE clause should be same as in the Data Dictionary.

<b><u>• • Select from Table Order By</u></b>

Consider the following examples:

SELECT EBELN EKORG INTO (EKPO-EBELN, EKPO-EKORG) FROM EKPO

WHERE EBELN IN PO_EBELN

ORDER BY EBELN

ENDSELECT.

In this case, the SORTing is done in the DB server. When the data volume is huge, it affects the performance of all other users accessing the DB server. If an index exists that can be used for sorting,

Then the strain is much lesser.

SELECT EBELN, EKORG INTO TABLE IT_EKPO FROM EKPO

WHERE EBELN IN PO_EBELN.

SORT IT_EKPO BY EBELN.

In this case, the sorting is done on the application server and it might strain only to that server .

Comment: Always sort the records fetched from DB, in the application server. Only under few exceptions where there is an index, DB sort is OK.

<b><u>• • Transferring DB contents to an Internal table.</u></b>

There are more than one ways of transferring the records read from the DB into an internal table.

Consider the following cases:

SELECT EBELN EKORG…. INTO (IT_EKPO-EBELN,…..) FROM EKPO

WHERE EBELN IN EBELN_PO.

APPEND IT_EKPO.

…

…

ENDSELECT.

In this case there are significant reasons, not to prefer this. The processing time taken for the SQL is huge since each record fetched (DB server) is coupled with an APPEND (App. Server). These long running SELECTs are in contention with INSERTS/UPDATES to those tables.

SELECT EBELN EKORG…. INTO TABLE IT_EKPO FROM EKPO

WHERE EBELN IN EBELN_PO.

LOOP AT IT_EKPO.

…

…

ENDLOOP.

In this case, all the required records are fetched at one go and populated in an internal table, there by reducing the processing time of SQL statement. Later the internal table is looped for further processing.

Comment: Always reduce the SQL processing time by fetching all the required records from the DB at one go and then do the required processing of those records.

<b><u>• • SELECT for all entries Vs Nested Selects</u></b>

SELECT EBELN EKORG.. INTO..FROM EKKO WHERE….

SELECT POSNR MATNR INTO… FROM EKPO

WHERE EBELN = EKKO-EBELN.

……

ENDSELECT.

ENDSELECT.

The above is a case of nested select.

SELECT EBELN EKORG.. INTO..TABLE IT_EKKO FROM EKKO

WHERE EBELN IN EBELN_PO.

SELECT EBELN POSNR MATNR FROM EKPO INTO TABLE

IT_EKPO FROM EKPO FOR ALL ENTRIES IN IT_EKKO

WHERE EBELN = IT_EKKO-VBELN.

LOOP AT IT_EKPO.

….

….

ENDLOOP.

This is a case of FOR ALL ENTRIES.

Comment: It is found that FOR ALL ENTRIES is better than nested selects in terms of performance and hence recommended.

<b><u>• • Table Buffering</u></b>

When the user requests an execution of a program, the program is executed in the Application server and also the SQL statements interpreted. The SQL requests are passed on to DB server for the required data. But usually that’s not the case always. Before the SQL request is passed on to the DB server,

The application server queries its local table buffer to see if the corresponding table is loaded into the buffer. If so, the records are retrieved from the buffer, thus resulting in faster retrieval.

If the buffers are not loaded, then the requests are passed on to the DB server.

Issues:

1. 1. When a buffered table is updated, the buffer may not get updated simultaneously.

2. 2. When a buffered table is updated through one application server, the corresponding buffer in another application server is not updated immediately.

3. 3. Each application server reads the contents of DDLOG in order to invalidate the contents of its buffer if necessary. This happens every 1- 2 minutes (depending on the profile setting). Within this time if there is any read on the table buffer, then it would result in fetching ‘old data’.

4. 4. The tables that qualify for buffering are those, which are small in size, accessed mostly for READ purposes, and those, which are changed very infrequently. These could be Control tables, customizing tables and small master tables.

Comment:

Buffer the tables, which qualify for the same. Usage of table buffers helps in faster retrieval of results and reduces the load on DB servers.

<b><u>SQL statements that bypass the Table Buffer</u></b>

• • SELECT ….BYPASSING THE BUFFER

• • SELECT…DISTINCT

• • SELECT…COUNT, SUM, AVG, MIN, MAX

• • SELECT…ORDER BY (other than Primary key)

• • SELECT…FOR UPDATE

• • WHERE clause contains IS NULL statement.

• • Native SQL statements (EXEC SQL….END EXEC)

• • Update Table Records

Consider the following cases of Update statements:

PARAMETERS: VKBUR_PA LIKE ZTABLE-VKBUR.

SELECT-OPTIONS: VBELN_SO FOR ZTABLE-VBELN.

1.

SELECT * FROM ZTABLE WHERE VBELN–VBELN_SO.

ZTABLE-VKBUR = VKBUR_PA

UPDATE ZTABLE.

ENDSELECT.

In this case, the data selected for updating is transferred to application server from the DB server across the network, and after the update, it is transferred back again.

2.

UPDATE ZTABLE SET VKBUR = VKBUR_PA

WHERE VBELN–VBELN_SO.

In this case, the updating of records happens more efficiently, in the DB server itself.

The second one is roughly 10 times faster than the first one for about 1000 records.

Comment: Avoid updating records by bring them unnecessarily to the application server, rather update them together in the DB server itself.

<b><u>• • Deleting Table Records</u></b>

Consider the 2 examples below:

1.

SELECT * FROM ZEKPO WHERE EBELN = EBELN_PO.

DELETE ZEKPO.

ENDSELECT.

In this case, the data records selected for deletion are transferred to application server from the DB server across the network and the same data is carried back to the DB server without any change and then the update takes place.

2.

DELETE FROM ZEKPO WHERE EBELN IN EBELN_PO.

This is a very efficient way of deleting records since no data travels across the network except for the ‘delete’ instruction from the application server to DB server.

Comment: The delete logic coded in the program should be passing only a delete instruction to the DB server and not get the data to the application server, except for, as a last resort.

<b><u>• • Commit Work</u></b>

‘Commit work’ needs to be issued after a logical unit of work is completed. This is because the DBMA keeps track of the changes made to the DB via the rollback segments, in order to enable rollback of all the changes made since the last Commit. The rollback segments are in memory and can lead to overflows. Issuing COMMIT WORK after the DB changes makes it unnecessary to keep track of changes by the DBMS.

Secondly, the DBMS holds a lock on all changed records until the COMMIT time. This again can be avoided by issuing COMMIT WORK in the program itself.

Comment:

COMMIT WORK after the DB change reduces unnecessary load on the system.