Problem solve Get help with specific problems with your technologies, process and projects.

20 Performance hints for OPEN-SQL

20 performance hints for OPEN-SQL

1. Keep the hit list small

  • Use WHERE clause wherever possible.
  • Do not select a large dataset and then check it with CHECK.

2. Transfer small amount of data to internal table (i.e., if you want to use few fields from the database table use SELECT with fields not to use SELECT *).

3. If you use views, the SAP buffering is switched off.

4. You should use aggregate functions rather than selecting data and grouping it yourself. SAP buffering will be switched off if you use aggregate functions.

5. Use a small number of database access. You can use a nested select statement when the outermost level is small. Otherwise it is better to avoid nested a select statement. Sometimes you only want to know if certain records in the second table exist or not. You do not want actual content. In this case you can use a nested select statement. For example:

SELECT carrid connid INTO (xcarrid, xconnid) FROM sflight AS f WHERE NOT EXISTS (SELECT * FROM spfli WHERE carrid = f~carrid AND connid = f~connid ).

6. Use SELECT … FOR ALL ENTRIES.

7. Create secondary indexes it will vastly improve the data base selection and slow down the data base insertion and updation.

8. some times full table scan is faster. The optimizer should do it right.

9. APPLY HAVING clause if it necessary.

10. Apply the INNER JOIN. Avoid nested SELECT-ENDSELECT loops.

11. You cannot process NOT operators in SELECT using an index.

12. Remove the load from the database! Avoid reading the same data again and again.

13. Avoid the ORDER BY clause if the desired sorting doesn't correspond to the index used. Use SORT... BY. Instead of order by clause.

14. In WHERE clause use EQ linked with AND as ofton as possible. Because index can support search criteria such as EQ and LIKE. Always try to use positive condition ie EQ, LIKE _.

15. The response time of conditions including NEQ is not improved by an index.

16 Do not use IS NULL. Use GE instead of that.

17. NOT, OR and IN are not supported by indexes unless all of the fields in the SELECT clause and WHERE condition are also contained in the index.

18. Replace the inner OR with an IN operator.
SELECT * FROM sflight INTO xflight WHERE carrid = 'LH ' AND (connid = '0300' OR connid = '0302') AND fldate LIKE '2002%'.
WRITE: / xflight-fldate.
ENDSELECT.

Use the following:
SELECT * FROM sflight INTO xflight
WHERE carrid = 'LH ' AND connid IN ('0300', '0302') AND
fldate LIKE '2002%'.
WRITE: / xflight-fldate.

19. You should check whether you can delete duplicates using the DELETE ADJACENT DUPLICATES FROM itab. instead of using SELECT DISTINCT

20. To start the Performance Trace, choose Test Performance Trace from the initial screen of the ABAP Workbench (Transaction ST05).

This was last published in May 2004

Dig Deeper on SAP Web applications

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchManufacturingERP

SearchOracle

SearchDataManagement

SearchAWS

SearchBusinessAnalytics

SearchCRM

SearchContentManagement

SearchFinancialApplications

Close