topic Re: Performance considerations for DB updates in Application Development and Automation Discussions

Performance considerations for DB updates

Former Member — Sat, 02 Feb 2008 06:27:33 GMT

Hi,

What are the Performance considerations for DB updates

Thanks,

Suneela.

Re: Performance considerations for DB updates

Former Member — Sat, 02 Feb 2008 06:40:51 GMT

Hi suneela,

try this linkit vl give some points about performance considerations .

http://www.erpgenie.com/abaptips/content/view/252/67/

Re: Performance considerations for DB updates

Former Member — Sat, 02 Feb 2008 06:45:42 GMT

hi,

plz consider Follwing points.

Transaction SE30 (ABAP Runtime Analysis) must be checked to measure/compare program performance/runtime if program has multiple inefficient databases selects or complicated internal table operations.

TYPE (data element) command is used while declaring the fields whenever feasible instead of LIKE. Remember not always the data element name matches with the table field name.

Internal Table is defined with "TYPE STANDARD TABLE OF" & Work-Areas is used instead of header lines.

No SELECT * is used

In SELECT statement, only the required fields are selected in the same order as they reside on the database table/structure/view.

For selecting single row from a database table, "SELECT UP to 1 Rows" is used. "Select Single" is used only when full primary key combination is known.

Use "SELECT INTO TABLE" rather than "SELECT INTO CORRESPONDING FIELDS OF TABLE".

Always specify as many primary keys as possible in WHERE clause to make the Select efficient.

Always select into an internal table, except when the table will be very large (i.e., when the internal table will be greater than 500,000 records). Use "Up to N Rows" when the number of records needed is known.

Nested Select is not used instead "Inner Join" and/or "For all Entries" is used. "For all Entries" is to be used over "Loop at ITAB / Select / ENDLOOP" (FOR ALL ENTRIES retrieves a unique result set so ensure you retrieve the full key from the database) .

When creating joins over database tables there should be an index at least on the inner table for the fields in the join condition else use " FOR ALL ENTRIES" select statement.

Usage of JOIN is limited to a maximum of 2 i.e. not more than 3 database tables are joined at one time.

CHECK that the internal table used in FOR ALL ENTRIES is NOT empty as this will retrieve all entries from the table.

Delete adjacent duplicate entries from internal table before selection from database table using " FOR ALL ENTRIES" statement.

For copying internal tables use '=' operator instead of Looping & Appending.

SORT inside a LOOP is not used.

Sort internal table by fields in the correct order, which are used in a READ TABLE statement using BINARY SEARCH. If the order of sorting is invalid the BINARY SEARCH will never work.

For large internal tables where only some rows are to be processed, use SORT and then the READ TABLE command is used to set index to first relevant row before looping from that index. Use CHECK or IF…EXIT…ENDIF as appropriate to exit from the loop .

Sort fields and Sort Order on the SORT statement should be mentioned explicitly (e.g. SORT ITAB BY FLD1 FLD2 ASCENDING)

DELETE or SORT is not used on a hashed table since it increases memory consumption.

Sorted table is used for range accesses involving table key or index accesses.

Fields specified in the WHERE condition with the critical operators NOT and <> (negative SQL statements) cannot be used for a search using database indexes. Whenever possible formulate SQL statements positively.

When coding IF or CASE, testing conditions are nested so that the most frequently true conditions are processed first. Also CASE is used instead of IF when testing multiple fields "equal to" something.

LOOP AT ITAB INTO WORKAREA WHERE K = 'XXX' should be used instead of LOOP AT ITAB INTO WORKAREA / CHECK ITAB-K = 'XXX'.

Also READ TABLE INTO WORKAREA should be used instead of only READ TABLE.

After the APPEND statement inside a loop, the work area that has been appended is cleared.

Do not delete the records of internal table inside the Loop – End loop.

Do not use:

LOOP AT ITAB WHERE EQUNR = '00001011'.

DELETE ITAB.

ENDLOOP.

Use:

DELETE ITAB WHERE EQUNR = '00001011'.

Re: Performance considerations for DB updates

Former Member — Sat, 02 Feb 2008 06:53:07 GMT

better u should go thru the following link

http://erpgenie.com/abaptips/content/view/235/67/

u ll get all the deatls reg do's and donts along with performance factors .

Re: Performance considerations for DB updates

Former Member — Sat, 02 Feb 2008 09:12:10 GMT

Hi,

Array updates

All Database manipulations in SAP (UPDATE, INSERT, DELETE) can be executed one record at a time or through an 'Array' operation.

Example:

No good

LOOP AT T_MAT.

Assign value to material group MARA-MATKL

……

UPDATE MARA SET MATKL = T_MAT-MATKL.

WHERE MATNR = T_MAT-MATNR.

ENDLOOP.

GOOD

LOOP AT T_MAT.

Assign value to material group MARA-MATKL

……

ENDLOOP

UPDATE MARA FROM TABLE T_MAT.

After the execution of the array update, the following system fields are populated:

SY-SUBRC - Contains 0 if all updates were successfully executed

SY-DBCNT -Contains the number of successfully updated records

Restrictions:

The Array update function can't be combined with UPDATE SET

Recommendation:

If a large number of fields are changed in multiple records ? Array update

If a small number of fields are changed in one record ? Update set

If a small number of fields are changed in multiple records

-> Define a modifiable view using the key fields and the fields which are to be changed

Updating Key fields

Key fields can be updated for transparent tables just like any other table.

Exception:

If synchronous match codes exist for the table, the key field update will be unsuccessful.

Reward Points if found helpfull..

Cheers,

Chandra Sekhar.