...
- 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...|hhttphttp://fuller.mit.edu/abap_review/sample_code.htm#faster appending|\" (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"!
...