Hi, regarding Database selection.

[Former Member]

Hi,

what are the basic things i need to take care while reading data from database.....

Thanks in advance,

charan

[former_member196299]

hi ,

While reading data from DB , you should take care of the proper indexes of the tables and use them in your select querry. Use the maximum no of primary keys in the select query with proper conditions .

Regards,

Ranjita

[Former Member]

Hi,

1.Avoid using nested loops.

2.Avoid using select..endselect

3.Avoid using select inside loops.

4.Always sorting table before reading it.

5.Use SELECT A B C INTO TABLE ITAB whenever possible. This will read all of the records into the itab in one operation, rather than repeated operations that result from a SELECT A B C INTO ITAB... ENDSELECT statement. Make sure that ITAB is declared with OCCURS NUM_RECS, where NUM_RECS is the number of records you expect to access.

6. Use joins where possible as redundant data is not fetched.

7. Use the SELECT SINGLE command whenever possible.

8.Avoid 'SELECT *', especially in tables that have a lot of fields. Use SELECT A B C INTO instead, so that fields are only read if they are used. This can make a very big difference.

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_PerformanceAnalysisTools.asp

Regards,

Priyanka.

[former_member189596]

Hi charan,

I am writing some database operation, i hope it will help you...

<b>Database Table Operations</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.

Comment:

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.

<u>• • Cursor Caching</u>

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.

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

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.

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

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.

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

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.

<u>Table Buffering</u>

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. When a buffered table is updated, the buffer may not get updated simultaneously.

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

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. 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.

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

• • 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)

<u>• • Update Table Records</u>

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.

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

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.

<u>Commit Work</u>

‘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.

i useful, reward points.....

Regards,

Rao

[Former Member]

Thank u friends.....

i got good staff.........

Regards,

Bhaskar

[Former Member]

I hope you understand it right, the shortest comment ist the most important!!

> While reading data from DB , you should take care of the proper indexes of the

> tables and use them in your select querry. Use the maximum no of primary

> keys in the select query with proper conditions .

the proper usage of indexs, the complete where clause is the most important point in database programming. A bug here causes serious performance problems, all other points are optimization.

Siegfried