• Complex queries can be efficiently implemented

• When Subqueries are used the query is executed at the database level bypassing SAP’s internal database buffer   

Restrictions: Select single can't be used together with subqueries

Following are some good cases of subquery usage.

Perform existence checks on tables (transactional) that you don’t need to select fields from (use of EXISTS)
Example: Get list of open sales orders (header level).
 
SELECT VBAK~VBELN
    FROM VBAK
    INTO TABLE T_ORDERS
    WHERE EXISTS
      ( SELECT AUFNR FROM J_3ABDSI
               WHERE J_3ABDSI~AUFNR = VBAK~VBELN ).

We do not want to use INNER JOIN here since that would result in size level data resulting in greater network i/o. We will then have to do subsequent DELETE ADJACENT duplicates on the internal table to report on the header level. This subquery technique gives significant performance improvements over INNER JOIN in this case.

Perform existence checks on tables (configuration type) that you don’t need to select fields from (use of IN clause)
Example: Get invoice items that are truly direct ships
 
SELECT VBRP~VBELN
       VBRP~POSNR
       VBRP~VGBEL
       VBRP~VGPOS
FROM VBRP
INTO TABLE RT_DRS_INVOICES
WHERE VBRP~VBELN IN O_VBELN AND
      VBRP~PSTYV IN
      ( SELECT ZTSD_ITM_CAT_CFG~PSTYV FROM ZTSD_ITM_CAT_CFG
               WHERE ZTSD_ITM_CAT_CFG~VKORG = VBRP~VKORG_AUFT AND
                     ZTSD_ITM_CAT_CFG~DRS_ITM_FL = 'X' ).

Avoid using INNER JOINs between big transactional tables and with a small config table. Instead use subquery like in the example below –
Example:
 
SELECT MVKE~MATNR
FROM MVKE INNER JOIN MARC ON MARC~MATNR = MVKE~MATNR AND
WHERE
        MVKE.VKORG = '1000' AND
        MVKE.VTWEG = '10' AND
        MARC.WERKS = '1051' AND
EXISTS ( SELECT * FROM TVKWZ WHERE TVKWZ.VKORG = MVKE.VKORG AND
                                   TVKWZ.VTWEG = MVKE.VTWEG AND
                                   TVKWZ.WERKS = MARC.WERKS )

 

Related Items:

• ABAP Performance
• ABAP Performance DOs and DON'T
• ABAP/4 Development Code Effici
• ABAP/4 Query Hints and Tips
• Calling An External Program Fr