...
- Dead Code (Program -> Check -> Extended Prog. Check) - unused subroutines appear as warnings under PERFORM/FORM interfaces. - unused variables appear as warnings under Field attributes. Transaction code is SLIN. This will also catch literals (section III below).
Wiki Markup When possible use *MOVE* instead of *MOVE-CORRESPONDING* (move bseg to \*bseg or move t_prps\[\] to t_prps2\[\] if you want to copy entire table or t_prps to t_prps2 if you only want to copy header line.)
- Code executed more than once should be placed in a form routine.
- SORT andREAD TABLE t_tab WITH KEY ... BINARY SEARCH when possible especially against non-buffered table (Data Dictionary -> Technical Info)
- SORT tables BY fields
- Avoid unnecessary moves to table header areas.
- Subroutine parameters should be typed for efficiency and to help prevent coding and runtime errors.
II. Database
...
Performance
- Avoid ORDER BY unless there is index on the columns - sort internal table instead
- SELECTSINGLE when possible
- SELECTfieldsFROMdatabase table INTO TABLE t_tab (an internaltable) - Lengthy discussion.
- Views (inner join) are a fast way to access information from multiple tables. Be aware that the result set only includes rows that appear in both tables.
- Use subqueries when possible.
- "FOR ALL ENTRIES IN..." (outer join) are very fast but keep in the mind the special features and 3 pitfalls of using it.
(a) Duplicates are removed from the answer set as if you had specified "SELECT DISTINCT"... So unless you intend for duplicates to be deleted include the unique key of the detail line items in your select statement. In the data dictionary (SE11) the fields belonging to the unique key are marked with an "X" in the key column.
(b) If the "one" table (the table that appears in the clause FOR ALL ENTRIES IN) is empty, all rows in the "many" table (the table that appears in the SELECT INTO clause ) are selected. Therefore make sure you check that the "one" table has rows before issuing a select with the "FOR ALL ENTRIES IN..." clause.
(c) If the 'one' table (the table that appears in the clause FOR ALL ENTRIES IN) is very large there is performance degradation Steven Buttiglieri created sample code to illustrate this. - Where clause should be in order of index See example.
This is important when there are multiple indexes for a table and you want to make sure a specific index is used. This will change when we convert from a "rules based" Oracle optimizer to a "cost based" Oracle optimizer. You should be aware of a bug in Oracle, lovingly referred to as the "3rd Column Blues". Click here for more information on indexes. - Where clause should containkeyfields in an appropriatedb index or bufferedtables. As long as we are using the Oracle Cost Based Optimizer, be aware fo the "Third Column Blues", an Oracle bug.
- Avoid nested SELECTs (SELECT...ENDSELECT within another SELECT...ENDSELECT). Load data in internal tables instead. See item 3 above.
- Use SQL statistical functions when possible (max, sum, ...)
- Delete all rows from a table. A where clause is mandatory. Specifying the client is the most efficient way.
- PutCheckstatements into where clause - caveat: Make sure that the index is still being used after you add the additional selection criteria. If the select statement goes from using an index to doing a db scan (reading each row in the database without going through an index) get it out of the where clause and go back to using "Check"!
...