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

Performance of SELECT SINGLE vs READ

A common practice that may have a serious negative impact on performance is the use of the SELECT SINGLE statement...

within a loop (SELECT...ENDSELECT or LOOP AT intab...ENDLOOP). When using SELECT SINGLE to read a record once within the loop, this method is fine. But in the case where the same record is read many times, the multiple reads from the database can become expensive (hard disk I/O).

This situation could exist, for example, in a report of sales orders that needs to display the customer name. Only the customer number field is included in the sales order header table (VBAK-KUNNR).

Therefore, the program also needs to get the customer name from the customer master data table (KNA1-NAME1). If the selection criteria were to cause a retrieval of thousands of sales orders, there would most likely be many customers that appear on more than one of the selected documents. See code example 1.

Performance may be significantly improved by loading the customer master records into an internal table and using the READ TABLE intab statement, which uses memory storage instead of accessing the database. In this case, the SELECT SINGLE would still need to be executed, but would access the database only once per customer number. When the record is selected, it is then loaded into an internal table where the READ statement can be used to determine whether or not the record already exists in the internal table. If it does exist, then the name is available when the READ TABLE statement is executed. See code example 2.

Be advised that the code examples have been simplified to illustrate the concept. (i.e. SELECT * is used instead of a explicit field list). Please also note that there are many additions and variants to the READ TABLE statement. It is suggested that you review the ABAP F1 Help documentation regarding the READ statement for more information.


************************************************
Code Example 1 - Use of the SELECT SINGLE method
TABLES: vbak, kna1.
...

SELECT * FROM vbak WHERE ...
  SELECT SINGLE * FROM kna1 WHERE kunnr = vbak-kunnr.
  ...

ENDSELECT.


***************************************************
Code Example 2 - Use of the READ TABLE intab method
TABLES: vbak.
...

DATA: BEGIN OF itab OCCURS 0,
        kunnr    TYPE kna1-kunnr,
        name1    TYPE kna1-name1,
      END OF itab.
...

SELECT * FROM vbak WHERE ...
  READ TABLE itab WITH KEY kunnr = vbak-kunnr.
  IF sy-subrc = 0.  ?record found in itab
    ...

  ELSE.             "record NOT found in itab
    SELECT SINGLE name1 INTO itab-name1 FROM kna1 
         WHERE kunnr = vbak-kunnr.
    MOVE vbak-kunnr TO itab-kunnr.
    APPEND itab.
  ENDIF.
  ...

ENDSELECT.
This was last published in April 2001

Dig Deeper on SAP ABAP

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