topic Performance in Application Development and Automation Discussions

Performance

former_member582701 — Tue, 16 Oct 2007 11:34:09 GMT

Hi!

What is better:

SELECT docnum

FROM edids

INTO TABLE i_edids

FOR ALL ENTRIES IN it_workitem

WHERE stapa1 = it_wotktiem-id AND

status NE '68'.

With an index for stapa1 and status.

Or:

SELECT docnum

FROM edids

INTO TABLE i_edids

FOR ALL ENTRIES IN it_workitem

WHERE stapa1 = it_wotktiem-id.

DELETE i_edidds WHERE status = '68'.

With an index for stapa1.

Thank you!!

pd: edids has around 65M of entries.

Re: Performance

Former Member — Tue, 16 Oct 2007 11:35:41 GMT

SELECT docnum

FROM edids

INTO TABLE i_edids

FOR ALL ENTRIES IN it_workitem

WHERE stapa1 = it_wotktiem-id AND

status NE '68'.

is better which you can check in SE30 also

• In a SELECT statement, only the fields (field-list) which are needed are selected in the order that they reside on the database, thus network load is considerably less. The number of fields can be restricted in two ways using a field list in the SELECT clause of the statement or by using a view defined in ABAP/4 Dictionary. The usage of view has the advantage of better reusability.

• SELECT SINGLE is used instead of SELECT-ENDSELECT loop when the entire key is available. SELECT SINGLE requires one communication with the database system, whereas SELECT-ENDSELECT needs two.

• Always specify the conditions in the WHERE-clause instead of checking them with check-statements, the database system can then use an index (if possible) and the network load is considerably less. You should not check the conditions with the CHECK statement because the contents of the whole table must be read from the database files into DBMS cache and transferred over the network. If the conditions are specified in the where clause DBMS reads exactly the needed data

• Complex code is not embedded within a SELECT / ENDSELECT statement.

• No complex WHERE clauses, since complex where clauses are poison for the statement optimizer in any database system.

• For all frequently used SELECT statements, try to use an index. You always use an index if you specify (a generic part of) the index fields concatenated with logical ANDs in the Select statement's WHERE clause

• When loading data into Internal table, INTO TABLE OR APPENDING TABLE is used instead of a SELECT/APPEND combination. It is always faster to use the INTO TABLE version of a Select statement than to use APPEND statements

• Use a select list with aggregate functions instead of checking and computing, when trying to find the maximum, minimum, sum and average value or the count of a database column.

• VIEW or JOIN is used to replace nested SELECT statements. This decreases the network load caused by database selects. If a nested SELECT is used, the inner select statement is executed several times which might cause unnecessary overhead.

• Use SAP buffering for accessing frequently used, read-only tables, network load can be considerably less. However, in case of retrieving latest data in multi-user environment, you may need to bypass the buffer

• Whenever possible, use array operations instead of single-row operations to modify your database tables. The frequent communication between the application program and database system produces considerable overhead.

• EXIT is used within SELECT / ENDSELECT if the entire key is not available and you only want to retrieve the first matching record.

• ORDER BY statement is used in SELECT only if it can use an index, otherwise, sorting is more effective. Read into an internal table and use the SORT statement in the program.

Message was edited by:

Minal Nampalliwar

Re: Performance

Former Member — Tue, 16 Oct 2007 11:37:56 GMT

The below statement is better performance:

SELECT docnum

FROM edids

INTO TABLE i_edids

FOR ALL ENTRIES IN it_workitem

WHERE stapa1 = it_wotktiem-id AND

status NE '68'.

Please check Se30 as well as ST05

Please give me reward points....

Re: Performance

Former Member — Tue, 16 Oct 2007 11:38:48 GMT

I think the first query is better, as during retrieval of data itself we r mentioning the condition status NE '68'. So the records which satisfy the condition are retrived hereitself.

But in the second case, after retrieval we are deleting. So time consumption may be there.

Re: Performance

Former Member — Tue, 16 Oct 2007 11:39:40 GMT

Hi,

It will better to use the first one since in a single statment all relevant records has been fetched whereas in the second one has two statment which onces again goes through the selected record for deleting for the condition.

Thanks,

Muthu.

Re: Performance

former_member582701 — Tue, 16 Oct 2007 12:08:04 GMT

Hmmm....

But the delete is from an internal table. The max consumption is always the select statement. My doubt has appears because we are using the NE comparation. If it would be EQ i have no doubt that first is better, but with NE i am not sure that we exploit the index