How to improve program execution efficiency during ABAP program writing

The operation efficiency of the ABAP program is mainly the acquisition of large amount of data in the program, if no method is taken, it affects the running efficiency of the report, and all the optimized ABAP programs are the methods to optimize the data fetching. The following points can effectively improve the efficiency of the number of fetching, so as to improve the efficiency of the program operation.

1. Select the most efficient table name order (valid only in the rule-based optimizer):
The Oracle parser processes the table names in the FROM clause in a right-to-left order, and the FROM clause is written in the last table (base table Drivingtable), which is processed first, with multiple tables in the FROM clause, You must select the table with the lowest number of records as the base table. If you have more than 3 tables connected to the query, you need to select the crosstab (intersectiontable) as the base table, which refers to the table that is referenced by other tables.

Connection order in the 2.WHERE clause:

Oracle uses a bottom-up sequential parsing where clause, according to which the connection between tables must be written before other where conditions, and those that can filter out the maximum number of records must be written at the end of the WHERE clause.

Avoid using ' * ' in the 3.SELECT clause:

During the parsing process, Oracle converts ' * ' to all column names, which is done by querying the number of dictionaries, which means more time is spent

4. Reduce the number of Access databases:
Oracle has done a lot of work internally: Parsing SQL statements, estimating index utilization, binding variables, reading chunks, and so on;

5. Reset the ArraySize parameter in Sql*plus, sql*forms and pro*c to increase the amount of data retrieved per database access, with a recommended value of

6. Use the Decode function to reduce processing time:

Use the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table.

7. Integrated simple, no associated database access:

If you have a few simple database query statements, you can integrate them into a single query (even if they are not related to each other)

8. Delete duplicate records:

The most efficient method of deleting duplicate records (because of the use of rowID) Example:

DELETE from emp E where E.rowid > (SELECT MIN (x.rowid) from emp X where x.emp_no = E.emp_no);

9. Replace Delete with truncate:

When you delete a record in a table, in general, the rollback segment (rollbacksegments) is used to store information that can be restored . If you do not have a COMMIT transaction, Oracle restores the data to the state it was before it was deleted (it is accurate to revert to the condition before the delete command was executed) When using truncate, the rollback segment no longer holds any information that can be recovered. When the command is run, the data cannot be restored. So very few resources are invoked and the execution time is very short. (Translator Press: truncate only in Delete full table applies, truncate is DDL is not DML)

10. Use commit as much as possible:

Whenever possible, use commit as many of the programs as possible, so that the performance of the program is improved and the requirements are reduced by the resources freed up for commit:

Resources Freed by Commit:

A. Information for recovering data on a rollback segment.

B. Locks acquired by program statements

C.redo space in the log buffer

D.oracle to manage the internal costs of the above 3 resources

11. Replace the HAVING clause with a WHERE clause:

Avoid having a HAVING clause, which will filter the result set only after retrieving all records.

This processing requires sorting, totals, and so on. If you can limit the number of records through the WHERE clause, you can reduce this overhead. (Non-Oracle) on, where, have the three clauses that can be added conditionally, on is the first execution, where the second, having the last, because on is the non-qualifying records filtered before the statistics, it can reduce the intermediate operation to process the data, It should be said that the speed is the fastest, where should also be faster than having to, because it filters the data before the sum, in two table joins only use on, so in a table, the left where and have compared. In the case of this single-table query statistics, if the conditions to be filtered do not involve the fields to be calculated, then they will be the same result, but where you can use the Rushmore technology, and have not, at the speed of the latter slow if you want to relate to the calculated field, it means that before the calculation, The value of this field is indeterminate, according to the workflow of the previous write, where the action time is done before the calculation, and having is calculated after the function, so in this case, the results will be different. On a multi-table join query, on has an earlier effect than where. The system first synthesizes a temporary table based on the conditions of the joins between the tables, then the where is filtered, then calculated, and then filtered by having. Thus, to filter the conditions to play the right role, first of all to understand when this condition should play a role, and then decided to put it there

12. Reduce the query on the table:

In the SQL statement that contains the subquery, pay particular attention to reducing the query on the table. Example:

SELECT Tab_name from TABLES where (tab_name,db_ver) = (Selecttab_name,db_ver from tab_columns where VERSION = 604)

13. Improve SQL efficiency with intrinsic functions.:

Complex SQL often sacrifices execution efficiency. The ability to master the above application function to solve problems is very meaningful in the practical work.

14. Alias using the table:

When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the aliases on each column. In this way, you can reduce the parsing time and reduce the syntax errors caused by the column ambiguity.

15. Replace in with exists instead of not exists instead of in:

In many base-table-based queries, it is often necessary to join another table in order to satisfy one condition. In this case, using EXISTS (or not EXISTS) will usually improve the efficiency of the query. In a subquery, the NOT IN clause performs an internal sort and merge. In either case, not in is the least effective (because it performs a full table traversal of the table in the subquery). To avoid using not, we can change it to an outer join (Outerjoins) or notexists.

Example: (efficient) Select *from EMP (base table) where EMPNO >0 and EXISTS (select ' X ' fromdept where DEPT. DEPTNO = EMP. DEPTNO and LOC = ' Melb ')

(Low efficiency) SELECT * from EMP (base table) where EMPNO >0 and DEPTNO in (SELECT DEPTNO from DEPT where LOC = ' Melb ')

How to improve program execution efficiency during ABAP program writing