Normal 0 false false false EN-US ZH-CN X-NONE SELECT statements

· Inner OR statements can cause problems with indexes. To prevent this, the statement should be converted to an outer OR:

SELECT col1 col2 FROM Txxx

WHERE F1 = ‘A’

AND ( F2 = ‘B’ OR F2 = ‘C’ )

Becomes …

SELECT col1 col2 FROM Txxx

WHERE ( F1 = ‘A’ AND F2 = ‘B’ )

OR ( F1 = ‘A’ AND F2 = ‘C’ ).

· ORDER BY PRIMARY KEY should only be used under special circumstances.

· In order to retrieve the last record, one should use the following:

SELECT FROM UP TO 1 ROWS

WHERE ….

ORDERED BY DESCENDING. (Dev. Note)

· To determine if a record exists instead of using SELECT … ENDSELECT use

SELECT COUNT (*) FROM

UP TO 1 ROWS

WHERE ….

IF SY-SUBRC = 0 “Record exists

· If all key values are known, use SELECT SINGLE because it is faster and more efficient

· Always supply as much of the key as possible when retrieving. Use the primary keys in the order in which they occur on the table. (Dev. Note)

· Select the best table for the job. If more than one table exists, pay special attention to keys and the size of the table. Eg. BSEG/BSIS/BSIK

· Use the shadow table feature Eg. BKPF and *BKPF when 2 entries are required from the same table (Dev. Note)

· It is more efficient for the system to transfer the entire contents of an internal table at once.

Eg.

SELECT VBELN AUART INTO TABLE IVBAK FROM VBAK

WHERE BNAME IN BNAME_SO.

LOOP AT IVBAK.

…

ENDLOOP.

· SELECT * should only be used when:

· The table is very small (ie. consists of few fields or the fields are not very long)

· Truly all fields from the table are required by the program

· SELECT * is against a database/projection view that is specifically designed for this SELECT.

· Check conditions should rather be incorporated into the WHERE clause.

Eg.

SELECT SINGLE VBELN AUART … INTO (VBAK-VBELN, …) FROM VBAK

WHERE VBELN = ‘0090000001’

AND BNAME = ‘Jones’.

· VBELN is the only key field for the table VBAK (except for the client). By including the check for the name of the customer (BNAME) in the WHERE condition, costly data transfer across the network (from the database server to the application server) will be prevented, should the condition for the name of the customer not be met.

· A SELECT statement without a WHERE condition usually indicates an error in the design of the program.

· Use SELECT … FOR ALL ENTRIES instead of nested SELECT’s

Eg.

SELECT VBELN AUART ... INTO TABLE IVBAK FROM VBAK

WHERE VBELN IN VBELN_SO.

SELECT VBELN POSNR MATNR … INTO TABLE IVBAP FROM VBAP

FOR ALL ENTRIES IN IVBAK

WHERE VBELN = IVBAK-VBELN.

LOOP AT IVBAK.

READ TABLE IVBAP WITH KEY VBELN = IVBAK-VBELN.

LOOP AT IVBAP FROM SY-TABIX.

…

ENDLOOP

ENDLOOP.

· Make use of the calculation capabilities of the database (ie. SUM, AVG, MIN, MAX).

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/774980/viewspace-716863/ ，如需转载，请注明出处，否则将追究法律责任。

上一篇： standard text in scriptform

下一篇：（转）FI/CO配置步骤

请登录后发表评论登录

全部评论

marui0613

注册时间：2008-12-09

博文量
3
访问量

4647

ITPub博客

performance of abap

最新文章