Quickly Retrieving FI document Data from BSEG

Posted by Rob Burbank Nov 12, 2007

In other BLOGs, I showed some general techniques that you can use to improve the performance of your ABAP code. In this one, I will show some specific techniques that you can use to retrieve accounting document data.

BSEG is a cluster table and in the forums it is often said that you should avoid selecting directly against this table. This is simply incorrect. While BSEG is large and the non-key fields are not known to the underlying database, it is simply a table and if you take proper care in designing a SELECT statement, you will have no performance problems accessing BSEG.

So the trick is in designing the SELECT statement. Because BSEG is a cluster table, the only key fields allowed are those of the primary key:

BUKRS - Company Code

BELNR - Accounting Document Number
GJAHR - Fiscal Year
BUZEI - Line Item Number

But do you need to know values for all of these fields in order to maximize performance? The short answer is "yes" if performance is truly to be "maximized". But in normal circumstances, you may not have values for all of these fields immediately at hand. There are two factors that you have to take into account:

The most important fields to fill are the leading primary key fields. If you know the document number, fiscal year and line item number but not the company code, a SELECT using only these fields will give very poor performance. Imagine trying to find an entry in a large dictionary when you know only the second through fourth letters of a word. You would have to scan the entire dictionary. It's the same sort of situation here.

The SELECT should return as little data as possible. If you know only the company code but not the other key fields, performance will likely still be very bad. I say "likely" because in your system you may have some company codes that have very few documents posted to them while others have many.

In my experience, I have found that when retrieving data from BSEG, it is necessary and sufficient to have the company code and document number. Anything more is icing that you can use if you have it.

But also in my experience, users don't usually ask for reports based on document numbers. They need to see data based on customers or cost centers or some other similar criteria. If you try to retrieve data from BSEG using the customer number only, performance will be about as bad is possible.

But many standard SAP programs retrieve accounting data based on these sorts of fields. What magic tricks do SAP programmers have at their disposal that we don't?

No magic, but there are some tricks that you can use. I've written a program that shows different ways that you can use to find a document number. In some cases, you need to know beforehand the company code (or controlling area or funds management area). But, reports usually have this as one of the selection criteria. If not, you can use the technique I have shown in of my other BLOGs.

Before I present the program, there are some caveats:

Most importantly, this is not complete. I'm showing some techniques that I have learned, but there are many more. This is really just to get you started.
I've used FOR ALL ENTRIES rather than JOINs. There is probably a small performance hit as I've shown in another blog, but I find that hit to be generally small and FOR ALL ENTRIES easier to read. Feel free to use JOINs instead.
Configuration may be an issue. I don't think it should be, but it's possible some of these techniques will not work in all systems.
On the other hand, configuration may work to your advantage. It's possible (at least in earlier versions) to configure FI documents to have the same number as for example, the corresponding billing document.
Archiving may also be an issue. If you have archived financial data you may find that you have to read the archive rather than BKPF or BSEG.
I have not done any actual SELECTS against BSEG in this program. I have simply handed control to transaction FB03 which does this for me (based on the primary key).

Anyway, here it is:

REPORT ztest_select.  * Tables *************************************************************** TABLES: bkpf, bseg,         covp, csks,         glpca,         bsis, bsas, bsid, bsad, bsik, bsak,         ekbe, aufk,         vbfa, vbak,         vapma,         fmifiit,         payr.  * Global Data **********************************************************  TYPES: BEGIN OF doc,         bukrs TYPE bseg-bukrs,         belnr TYPE bseg-belnr,         gjahr TYPE bseg-gjahr,         buzei TYPE bseg-buzei,       END   OF doc.  DATA: doc_int  TYPE TABLE OF doc,       doc_wa   TYPE          doc,       w_repid  TYPE sy-repid VALUE sy-repid,       no_lines TYPE sy-tabix.  * Selection Screen ***************************************************** PARAMETERS: p_gjahr TYPE covp-refgj OBLIGATORY. SELECTION-SCREEN SKIP. PARAMETERS: p_kokrs TYPE csks-kokrs OBLIGATORY,             p_kostl TYPE csks-kostl,             p_prctr TYPE glpca-rprctr,             p_aufnr TYPE aufk-aufnr. SELECTION-SCREEN SKIP. PARAMETERS: p_bukrs TYPE bsis-bukrs OBLIGATORY,             p_budat TYPE bkpf-budat,             p_ebeln TYPE ekko-ebeln,             p_hkont TYPE bsis-hkont,             p_lifnr TYPE bsik-lifnr,             p_kunnr TYPE bsid-kunnr. SELECTION-SCREEN SKIP. PARAMETERS: p_vbeln TYPE vbak-vbeln. SELECTION-SCREEN SKIP. PARAMETERS: p_matnr TYPE vapma-matnr. SELECTION-SCREEN SKIP. PARAMETERS: p_fikrs TYPE fmifiit-fikrs,             p_fistl TYPE fmifiit-fistl,             p_fonds TYPE fmifiit-fonds. SELECTION-SCREEN ULINE. PARAMETERS: p_hbkid TYPE payr-hbkid,             p_hktid TYPE payr-hktid,             p_rzawe TYPE payr-rzawe,             p_chect TYPE payr-chect.  START-OF-SELECTION.  * Retrieve document numbers based on different requirements  * Posting Date   PERFORM posting_date_actuals     USING p_bukrs           p_budat.  * Cost Center   PERFORM cost_center_actuals     USING p_kokrs           p_kostl           p_gjahr.  * GL Account   PERFORM gl_actuals     USING p_bukrs           p_hkont           p_gjahr.  * Vendor   PERFORM vendor_actuals     USING p_bukrs           p_lifnr           p_gjahr.  * Customer   PERFORM customer_actuals     USING p_bukrs           p_kunnr           p_gjahr.  * Purchase Order   PERFORM po_actuals     USING p_ebeln.  * Sales Order   PERFORM so_actuals     USING p_vbeln.  * Order   PERFORM order_actuals     USING p_aufnr           p_gjahr.  * Fund/Fund Center   PERFORM fm_actuals     USING p_fikrs           p_gjahr           p_fistl           p_fonds.  * Profit Center   PERFORM profit_center_actuals     USING p_kokrs           p_prctr           p_gjahr.  * Material   PERFORM material_actuals     USING p_matnr           p_gjahr.  * Cheque number   PERFORM cheque_actuals     USING p_hbkid           p_hktid           p_chect.  *&---------------------------------------------------------------------* *&      Form  posting_date_actuals *&---------------------------------------------------------------------* *       Use one of the secondary indices of BKPF to retrieve the *       document number *----------------------------------------------------------------------* FORM posting_date_actuals   USING    bukrs            budat.    DATA: disp_date(10).    CHECK NOT budat IS INITIAL.  * Uses index BKPF~2 (4.7)   SELECT bukrs belnr gjahr     INTO TABLE doc_int     UP TO 100 ROWS     FROM bkpf     WHERE bukrs = bukrs  AND * Normally, you would probably only want normal documents, that is * documents with BSTAT = ' '. So you would change the next line. * On the other hand, you might want documents for all values of BSTAT, * but not want to hardcode the values. In that case, you can retrieve * values from the domain of BSTAT and put them in a range table and * use the range table in the next line.           bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z') AND           budat = budat.    CHECK sy-subrc = 0.   WRITE budat TO disp_date.    PERFORM display_documents     TABLES doc_int     USING 'Posting date'           disp_date           space           space.  ENDFORM.                    " posting_date_actuals  *&---------------------------------------------------------------------* *&      Form  cost_center_actuals *&---------------------------------------------------------------------* *       Retrieve documents for a cost center *----------------------------------------------------------------------* FORM cost_center_actuals   USING    kokrs            kostl            gjahr.    DATA: covp_int TYPE TABLE OF covp,         disp_cc(10).    CHECK NOT kostl IS INITIAL.  * Uses primary index (4.7)   SELECT SINGLE objnr     FROM csks     INTO csks-objnr     WHERE kokrs = kokrs       AND kostl = kostl.    CHECK sy-subrc = 0.  * COVP is a view. This uses index COEP~1 (4.7)   SELECT refbk refbn refgj refbz     FROM covp     INTO TABLE doc_int     UP TO 100 ROWS     WHERE lednr = '00'       AND objnr = csks-objnr       AND gjahr = gjahr       AND wrttp IN ('04', '11')       AND versn = '000'.    CHECK sy-subrc = 0.    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'     EXPORTING       input  = kostl     IMPORTING       output = disp_cc.    PERFORM display_documents     TABLES doc_int     USING 'Cost Center'           disp_cc           space           space.  ENDFORM.                    " cost_center_actuals  *&---------------------------------------------------------------------* *&      Form  gl_actuals *&---------------------------------------------------------------------* *       BKPF and BSEG have a number of secondary index tables. These are *       tables that are indexed by GL customer or vendor number and have *       data that is in both BKPF and BSEG. These secondary index tables *       that have an 'i' in the third character of the name contain open *       items. Those with an 'a' contain cleared items. In practice, you *       may only one or the other. In this program I am retrieving both. * *       Here we get documents related to a GL. *----------------------------------------------------------------------* FORM gl_actuals   USING    bukrs            hkont            gjahr.    DATA: disp_gl(10).    CHECK NOT hkont IS INITIAL.  * Uses primary index (4.7)   SELECT bukrs belnr gjahr buzei     FROM bsis     INTO TABLE doc_int     UP TO 100 ROWS     WHERE bukrs = bukrs       AND hkont = hkont       AND gjahr = gjahr.  * Uses primary index (4.7)   SELECT bukrs belnr gjahr buzei     FROM bsas     APPENDING TABLE doc_int     UP TO 100 ROWS     WHERE bukrs = bukrs       AND hkont = hkont       AND gjahr = gjahr.    DESCRIBE TABLE doc_int LINES no_lines.   CHECK no_lines > 0.    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'     EXPORTING       input  = hkont     IMPORTING       output = disp_gl.    PERFORM display_documents     TABLES doc_int     USING 'GL Account'           disp_gl           space           space.  ENDFORM.                    " gl_actuals  *&---------------------------------------------------------------------* *&      Form  vendor_actuals *&---------------------------------------------------------------------* *       Here we get documents related to a vendor. *----------------------------------------------------------------------* FORM vendor_actuals   USING    bukrs            lifnr            gjahr.    DATA: disp_vendor(10).    CHECK NOT lifnr IS INITIAL.  * Uses primary index (4.7)   SELECT bukrs belnr gjahr buzei     FROM bsik     INTO TABLE doc_int     UP TO 100 ROWS     WHERE bukrs = bukrs       AND lifnr = lifnr       AND gjahr = gjahr.  * Uses primary index (4.7)   SELECT bukrs belnr gjahr buzei     FROM bsak     APPENDING TABLE doc_int     UP TO 100 ROWS     WHERE bukrs = bukrs       AND lifnr = lifnr       AND gjahr = gjahr.    DESCRIBE TABLE doc_int LINES no_lines.   CHECK no_lines > 0.    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'     EXPORTING       input  = lifnr     IMPORTING       output = disp_vendor.    PERFORM display_documents     TABLES doc_int     USING 'Vendor'           disp_vendor           space           space.   ENDFORM.                    " vendor_actuals  *&---------------------------------------------------------------------* *&      Form  customer_actuals *&---------------------------------------------------------------------* *       Here we get documents related to a customer. *----------------------------------------------------------------------* FORM customer_actuals   USING  bukrs          kunnr          gjahr.    DATA: disp_customer(10).    CHECK NOT kunnr IS INITIAL.  * Uses primary index (4.7)   SELECT bukrs belnr gjahr buzei     FROM bsid     INTO TABLE doc_int     UP TO 100 ROWS     WHERE bukrs = bukrs       AND kunnr = kunnr       AND gjahr = gjahr.  * Uses primary index (4.7)   SELECT bukrs belnr gjahr buzei     FROM bsad     APPENDING TABLE doc_int     UP TO 100 ROWS     WHERE bukrs = bukrs       AND kunnr = kunnr       AND gjahr = gjahr.    DESCRIBE TABLE doc_int LINES no_lines.   CHECK no_lines > 0.    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'     EXPORTING       input  = kunnr     IMPORTING       output = disp_customer.    PERFORM display_documents     TABLES doc_int     USING 'Customer'           disp_customer           space           space.  ENDFORM.                    " customer_actuals  *&---------------------------------------------------------------------* *&      Form  po_actuals *&---------------------------------------------------------------------* *       Table BKPF has a useful index on AWTYP and AWKEY. Here, we use *       this to retrieve documents for purchase orders. *----------------------------------------------------------------------* FORM po_actuals   USING    ebeln.    TYPES: BEGIN OF ekbe_type,            belnr TYPE ekbe-belnr,            gjahr TYPE ekbe-gjahr,            vgabe TYPE ekbe-vgabe,          END   OF ekbe_type.    DATA: ekbe_int TYPE TABLE OF ekbe_type,         ekbe_wa  TYPE          ekbe_type.    DATA: v_reference    TYPE bkpf-awtyp,           "Reference procedure         v_objectkey    TYPE bkpf-awkey.           "Object key    DATA:disp_po(10).    CHECK NOT ebeln IS INITIAL.  * Uses primary index (4.7)   SELECT belnr gjahr     FROM ekbe     INTO TABLE ekbe_int     UP TO 100 ROWS     WHERE ebeln = ebeln     AND vgabe IN ('1', '2').                      "1 - GR, 2 - IR    CHECK sy-subrc = 0.    SORT ekbe_int.   DELETE ADJACENT DUPLICATES FROM ekbe_int.    LOOP AT ekbe_int INTO ekbe_wa.     v_objectkey+00(10) = ekbe_wa-belnr.     v_objectkey+10(10) = ekbe_wa-gjahr.           "BELNR+YEAR      IF ekbe_wa-vgabe = '1'.       v_reference = 'MKPF'.     ELSE.       v_reference = 'RMRP'.     ENDIF.  * Uses index BKPF~4 (4.7)     SELECT SINGLE bukrs belnr gjahr               "Accounting Doc Header       FROM bkpf       INTO doc_wa       WHERE awtyp =  v_reference         AND awkey =  v_objectkey.     IF sy-subrc = 0.       APPEND doc_wa TO doc_int.     ENDIF.   ENDLOOP.    CHECK no_lines > 0.    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'     EXPORTING       input  = ebeln     IMPORTING       output = disp_po.    PERFORM display_documents     TABLES doc_int     USING 'Purchase Order'           disp_po           space           space.  ENDFORM.                    " po_actuals  *&---------------------------------------------------------------------* *&      Form  so_actuals *&---------------------------------------------------------------------* *       Use AWTYP and AWKEY to retrieve documents related to sales *----------------------------------------------------------------------* FORM so_actuals  USING    vbeln.    TYPES: BEGIN OF vbfa_type,            vbeln TYPE vbfa-vbeln,          END   OF vbfa_type.    DATA: vbfa_int TYPE TABLE OF vbfa_type,         vbfa_wa  TYPE          vbfa_type.    DATA: v_reference    TYPE bkpf-awtyp,           "Reference procedure         v_objectkey    TYPE bkpf-awkey.           "Object key    DATA:disp_so(10).    CHECK NOT vbeln IS INITIAL.  * Uses primary index (4.7)   SELECT vbeln     FROM vbfa     INTO TABLE vbfa_int     UP TO 100 ROWS     WHERE vbelv   = vbeln       AND vbtyp_n = 'P'.                          "Debit memo    CHECK sy-subrc = 0.    SORT vbfa_int.   DELETE ADJACENT DUPLICATES FROM vbfa_int.    LOOP AT vbfa_int INTO vbfa_wa.     v_objectkey+00(10) = vbfa_wa-vbeln.           "BELNR     v_reference        = 'VBRK'.  * Uses index BKPF~4 (4.7)     SELECT SINGLE bukrs belnr gjahr               "Accounting Doc Header       FROM bkpf       INTO doc_wa       WHERE awtyp =  v_reference         AND awkey =  v_objectkey.     IF sy-subrc = 0.       APPEND doc_wa TO doc_int.     ENDIF.   ENDLOOP.    DESCRIBE TABLE doc_int LINES no_lines.   CHECK no_lines > 0.    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'     EXPORTING       input  = vbeln     IMPORTING       output = disp_so.    PERFORM display_documents     TABLES doc_int     USING 'Sales Document'           disp_so           space           space.  ENDFORM.                    " so_actuals  *&---------------------------------------------------------------------* *&      Form  order_actuals *&---------------------------------------------------------------------* *       Retrieve documents related to an order *----------------------------------------------------------------------* FORM order_actuals   USING    aufnr            gjahr.    DATA: disp_order(10).    CHECK NOT aufnr IS INITIAL.  * Uses primary index (4.7)   SELECT SINGLE objnr     FROM aufk     INTO aufk-objnr     WHERE aufnr = aufnr.    CHECK sy-subrc = 0.  * COVP is a view. This uses index COEP~1 (4.7)   SELECT refbk refbn refgj refbz     FROM covp     INTO TABLE doc_int     UP TO 100 ROWS     WHERE lednr = '00'       AND objnr = aufk-objnr       AND gjahr = gjahr       AND wrttp IN ('04', '11')       AND versn = '000'.    CHECK sy-subrc = 0.    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'     EXPORTING       input  = aufnr     IMPORTING       output = disp_order.    PERFORM display_documents     TABLES doc_int     USING 'Order'           disp_order           space           space.  ENDFORM.                    " order_actuals  *&---------------------------------------------------------------------* *&      Form  FM_actuals *&---------------------------------------------------------------------* *       Not many institutions use Funds Management, but if you do, this *       is how to relate funds management documents to FI documents. *----------------------------------------------------------------------* FORM fm_actuals   USING    fikrs            gjahr            fistl            fonds.    DATA: disp_cfc(10),         disp_fund(10).    CHECK NOT fikrs IS INITIAL AND         NOT fistl IS INITIAL.  * Uses index FMIFIIT~3 (4.7)   SELECT bukrs knbelnr kngjahr knbuzei     FROM  fmifiit     INTO TABLE doc_int     UP TO 100 ROWS          WHERE  fistl  = fistl          AND    fonds  = fonds.    CHECK sy-subrc = 0.    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'     EXPORTING       input  = fistl     IMPORTING       output = disp_cfc.    IF NOT fonds IS INITIAL.     CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'       EXPORTING         input  = fonds       IMPORTING         output = disp_fund.   ENDIF.    PERFORM display_documents     TABLES doc_int     USING 'Fund Center'           disp_cfc           'Fund'           disp_fund.  ENDFORM.                    " FM_actuals  *&---------------------------------------------------------------------* *&      Form  profit_center_actuals *&---------------------------------------------------------------------* *       Retrieve documents related to a profit center *----------------------------------------------------------------------* FORM profit_center_actuals   USING    kokrs            prctr            gjahr.    DATA: disp_pc(10).    CHECK NOT prctr IS INITIAL.  * This uses index GLPCA~1 (4.7)   SELECT rbukrs refdocnr refryear refdocln     FROM glpca     INTO TABLE doc_int     UP TO 100 ROWS     WHERE kokrs  = kokrs       AND ryear  = gjahr       AND rprctr = prctr       AND awtyp  = 'BKPF'.    CHECK sy-subrc = 0.    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'     EXPORTING       input  = prctr     IMPORTING       output = disp_pc.    PERFORM display_documents     TABLES doc_int     USING 'Profit Center'           disp_pc           space           space.  ENDFORM.                    " profit_center_actuals  *&---------------------------------------------------------------------* *&      Form  material_actuals *&---------------------------------------------------------------------* *       Get FI documents for a material. *       For purchase orders get: *         goods receipts *         invoice receipts *       For sales orders get: *         debit memos *----------------------------------------------------------------------* FORM material_actuals   USING    matnr            gjahr.    TYPES: BEGIN OF ekpo_type,            ebeln TYPE ekpo-ebeln,            ebelp TYPE ekpo-ebelp,          END   OF ekpo_type.    TYPES: BEGIN OF ekbe_type,            belnr TYPE ekbe-belnr,            gjahr TYPE ekbe-gjahr,            vgabe TYPE ekbe-vgabe,          END   OF ekbe_type.    TYPES: BEGIN OF vapma_type,            vbeln TYPE vapma-vbeln,            posnr TYPE vapma-posnr,          END   OF vapma_type.    TYPES: BEGIN OF vbfa_type,            vbeln TYPE vbfa-vbeln,            posnv TYPE vbfa-posnv,          END   OF vbfa_type.    DATA: ekpo_int TYPE TABLE OF ekpo_type,         ekpo_wa  TYPE          ekpo_type.    DATA: ekbe_int TYPE TABLE OF ekbe_type,         ekbe_wa  TYPE          ekbe_type.    DATA: vapma_int TYPE TABLE OF vapma_type,         vapma_wa  TYPE          vapma_type.    DATA: v_reference    TYPE bkpf-awtyp,           "Reference procedure         v_objectkey    TYPE bkpf-awkey.           "Object key    DATA: vbfa_int TYPE TABLE OF vbfa_type,         vbfa_wa  TYPE          vbfa_type.    DATA: disp_mat(10).    CHECK NOT matnr IS INITIAL.  * First, find purchase orders for the material * This uses index EKPO~1 (4.7)   SELECT ebeln ebelp     FROM ekpo     INTO TABLE ekpo_int     UP TO 100 ROWS     WHERE matnr = matnr.  * Now get the FI document numbers as above * Uses primary index (4.7)   SELECT belnr gjahr vgabe     FROM ekbe     INTO TABLE ekbe_int     UP TO 100 ROWS     FOR ALL ENTRIES IN ekpo_int     WHERE ebeln = ekpo_int-ebeln       AND ebelp = ekpo_int-ebelp       AND vgabe IN ('1', '2').                      "1 - GR, 2 - IR    CHECK sy-subrc = 0.    SORT ekbe_int.   DELETE ADJACENT DUPLICATES FROM ekbe_int.    LOOP AT ekbe_int INTO ekbe_wa.     v_objectkey+00(10) = ekbe_wa-belnr.     v_objectkey+10(10) = ekbe_wa-gjahr.           "BELNR+YEAR      IF ekbe_wa-vgabe = '1'.       v_reference = 'MKPF'.     ELSE.       v_reference = 'RMRP'.     ENDIF.  * Uses index BKPF~4 (4.7)     SELECT SINGLE bukrs belnr gjahr               "Accounting Doc Header       FROM bkpf       INTO doc_wa       WHERE awtyp =  v_reference         AND awkey =  v_objectkey.     IF sy-subrc = 0.       APPEND doc_wa TO doc_int.     ENDIF.   ENDLOOP.  * Next get the sales orders for the material  * Note - I am using an artificial date range here because of archiving * in our system. Feel free to remove it.   SELECT vbeln posnr     FROM vapma     INTO TABLE vapma_int     UP TO 100 ROWS     WHERE matnr = matnr       AND audat BETWEEN '20070101' AND '20071231'.  * Uses primary index (4.7)   SELECT vbeln posnv     FROM vbfa     INTO TABLE vbfa_int     FOR ALL ENTRIES IN vapma_int     WHERE vbelv   = vapma_int-vbeln       AND posnv   = vapma_int-posnr       AND vbtyp_n = 'P'.                          "Debit memo    CHECK sy-subrc = 0.    SORT vbfa_int.   DELETE ADJACENT DUPLICATES FROM vbfa_int.    LOOP AT vbfa_int INTO vbfa_wa.     CLEAR: v_objectkey,            v_reference.     v_objectkey+00(10) = vbfa_wa-vbeln.           "BELNR     v_reference        = 'VBRK'.  * Uses index BKPF~4 (4.7)     SELECT SINGLE bukrs belnr gjahr               "Accounting Doc Header       FROM bkpf       INTO doc_wa       WHERE awtyp =  v_reference         AND awkey =  v_objectkey.     IF sy-subrc = 0.       APPEND doc_wa TO doc_int.     ENDIF.   ENDLOOP.    DESCRIBE TABLE doc_int LINES no_lines.   CHECK no_lines > 0.    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'     EXPORTING       input  = matnr     IMPORTING       output = disp_mat.    PERFORM display_documents     TABLES doc_int     USING 'Material'           disp_mat           space           space.  ENDFORM.                    " material_actuals  *&---------------------------------------------------------------------* *&      Form  cheque_actuals *&---------------------------------------------------------------------* *       Retrieve FI documents for a cheque. We are assuming Accounts *       Payable related. *----------------------------------------------------------------------* FORM cheque_actuals   USING    hbkid            hktid            chect.   DATA: belnr TYPE payr-vblnr,         gjahr TYPE payr-gjahr,         lifnr TYPE payr-lifnr,         laufd TYPE payr-laufd,         disp_cheque(13).    CHECK NOT p_chect IS INITIAL.  * Retrieve the cheque data using the primary key (4.7) * We are assuming a vendor payment here   SELECT SINGLE vblnr gjahr lifnr laufd     FROM  payr     INTO (belnr, gjahr, lifnr, laufd )     WHERE  zbukr  = p_bukrs       AND  hbkid  = p_hbkid       AND  hktid  = p_hktid       AND  rzawe  = p_rzawe       AND  chect  = p_chect.  * Now get the accounting documents   SELECT bukrs belnr gjahr     FROM  bsak     INTO TABLE doc_int          WHERE  bukrs  = p_bukrs          AND    lifnr  = lifnr          AND    umsks  = ' '          AND    umskz  = ' '          AND    augdt  = laufd          AND    augbl  = belnr          AND    gjahr  = gjahr.    DESCRIBE TABLE doc_int LINES no_lines.   CHECK no_lines > 0.    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'     EXPORTING       input  = chect     IMPORTING       output = disp_cheque.    PERFORM display_documents     TABLES doc_int     USING 'Cheque number'           disp_cheque           space           space.  ENDFORM.                    " cheque_actuals  *&---------------------------------------------------------------------* *&      Form  display_documents *&---------------------------------------------------------------------* *       At this point, you have the company code, document number and *       fiscal year in table DOC_INT. Here, I just use a standard SAP *       function module and transaction to display documents and *       details, but you could use the same information to retrieve *       document data from BKPF and/or BSEG. *----------------------------------------------------------------------* FORM display_documents   TABLES   doc_int STRUCTURE doc_wa   USING    doc_source_1            source_value_1            doc_source_2            source_value_2.    TYPE-POOLS: slis.    DATA: sel     TYPE  slis_selfield.   DATA: title   TYPE string.    CONCATENATE 'FI line items for' doc_source_1 source_value_1                INTO title SEPARATED BY space.    IF NOT source_value_2 IS INITIAL.     CONCATENATE title doc_source_2 source_value_2                 INTO title SEPARATED BY space.   ENDIF.    CALL FUNCTION 'REUSE_ALV_POPUP_TO_SELECT'     EXPORTING       i_title                 = title       i_selection             = 'X'       i_tabname               = 'BSEG'       i_structure_name        = 'BSEG'       i_callback_user_command = 'USER_COMMAND'       i_callback_program      = w_repid     IMPORTING       es_selfield             = sel     TABLES       t_outtab                = doc_int.    IF NOT sel IS INITIAL.     READ TABLE doc_int INDEX sel-tabindex.     SET PARAMETER ID 'BUK' FIELD doc_int-bukrs.     SET PARAMETER ID 'BLN' FIELD doc_int-belnr.     SET PARAMETER ID 'GJR' FIELD doc_int-gjahr.     CALL TRANSACTION 'FB03' AND SKIP FIRST SCREEN.   ENDIF.  ENDFORM.                    " display_documents

You may have noticed that the program is incomplete. There is only a stub where the logic to retrieve FI documents for a cheque. This is a challenge to you to figure out how to do this. (Hint - table PAYR may be useful). If you try this, please post your results in the comments section of this BLOG. In a week or so, I will post the code that I created to do this. I am assuming that we are using an accounts payable cheque as an example.

JOINS vs. FOR ALL ENTRIES - Which Performs Better?

Posted by Rob Burbank Mar 19, 2007

In earlier blogs, I looked at various performance tuning techniques and tried to identify some that are more important than others. In this blog, I want to look at different ways to construct a simple SELECT statement.

A very common question that is asked in the ABAP forum is “Which is better: a JOIN or FOR ALL ENTRIES ?” I’ve written a program that compares six different ways of constructing a SELECT statement: a simple, fully qualified SELECT ; a nested SELECT ; a SELECT using FOR ALL ENTRIES ; a SELECT using an INNER JOIN ; a SELECT using an OUTER JOIN ; and a SELECT using a sub-query. All of these SELECT s are fully qualified in the sense that they use all fields of the primary key. For comparison, I’ve also added a SELECT that doesn’t fully use the primary key.

This task was made more difficult by the fact that it’s not really easy to compare a JOIN with a sub-query. A JOIN assumes that you want the data from more than one table. A sub-query assumes that you need data only from the main table. So the SELECT statements that I have constructed are quite simple and in some cases, not practical. They are just for comparison purposes.

I’ve used the GET RUN TIME statement for comparison rather than the EXPLAIN function of transaction ST05 because it’s difficult to compare multiple SELECT s with single SELECT s using this function. GET RUN TIME is not perfect either, but if you do multiple comparisons, particularly in a system with little activity, the results should be OK. I’ve put all of the SELECTs used in comparisons within loops. You can adjust the number of loop passes on the selection screen.

In any event, here is the program:

REPORT ztest_selects LINE-SIZE 80 MESSAGE-ID 00.DATA: t001      TYPE t001,      bkpf      TYPE bkpf.SELECT-OPTIONS: s_bukrs FOR  bkpf-bukrs MEMORY ID buk      OBLIGATORY.SELECT-OPTIONS: s_belnr FOR  bkpf-belnr MEMORY ID bln      OBLIGATORY.PARAMETERS:     p_gjahr LIKE bkpf-gjahr MEMORY ID gjr      OBLIGATORY.SELECTION-SCREEN ULINE.PARAMETERS:     p_loop1 TYPE i                             OBLIGATORY                        DEFAULT 5,                p_loop2 TYPE i                             OBLIGATORY                        DEFAULT 10.TYPES: BEGIN OF t001_type,         bukrs  TYPE t001-bukrs,       END   OF t001_type,       BEGIN OF bkpf_type,         bukrs  TYPE bkpf-bukrs,         belnr  TYPE bkpf-belnr,         gjahr  TYPE bkpf-gjahr,       END   OF bkpf_type.DATA: t001_int  TYPE TABLE OF t001_type,      t001_wa   TYPE          t001_type,      bkpf_int  TYPE TABLE OF bkpf_type,      bkpf_wa   TYPE          bkpf_type.DATA: start     TYPE i,      end       TYPE i,      dif       TYPE i.START-OF-SELECTION.  DO p_loop1 TIMES.    PERFORM simple_select.    PERFORM nested_select.    PERFORM for_all_entries.    PERFORM inner_join.    PERFORM outer_join.    PERFORM sub_query.    PERFORM unqualified_select.    SKIP 1.  ENDDO.*&---------------------------------------------------------------------**&      Form  simple_select*&---------------------------------------------------------------------** First we get documents using a select statement that is* fully qualified on the primary key. Because buffering may be an issue,* the first select will be disregarded in this test. However, in real* life, this would be the important time.*----------------------------------------------------------------------*FORM simple_select.* Do an initial select of the documents we intend to get. Due to* buffering, the first select may take much longer then the next one.  SELECT  bukrs belnr gjahr    FROM  bkpf    INTO  TABLE bkpf_int    WHERE bukrs IN s_bukrs      AND belnr IN s_belnr      AND gjahr EQ p_gjahr.  IF sy-subrc <> 0.    MESSAGE ID '00' TYPE 'E' NUMBER '001' WITH               'No Data meets selection criteria'.  ENDIF.* Next we get the same document using the same fully qualified select* statement. We will use this in comparisons.  GET RUN TIME FIELD start.  DO p_loop2 TIMES.    SELECT  bukrs belnr gjahr      FROM  bkpf      INTO  TABLE bkpf_int      WHERE bukrs IN s_bukrs        AND belnr IN s_belnr        AND gjahr EQ p_gjahr.  ENDDO.  GET RUN TIME FIELD end.  dif = end - start.  WRITE: /001 'Time for first SELECT   (fully qualified)',          055 ':', dif, 'microseconds'.ENDFORM.                    " simple_select*&---------------------------------------------------------------------**&      Form  nested_select*&---------------------------------------------------------------------**       text*----------------------------------------------------------------------*FORM nested_select.* Use the same fully qualified SELECT, but this time nested. As usual,* Ignore the first SELECT and use the subsequent ones for comparison.  REFRESH: bkpf_int.  SELECT  bukrs FROM t001    INTO  t001_wa    WHERE bukrs IN s_bukrs.    SELECT  bukrs belnr gjahr      FROM  bkpf      INTO  bkpf_wa      WHERE bukrs EQ t001_wa-bukrs        AND belnr IN s_belnr        AND gjahr EQ p_gjahr.      APPEND bkpf_wa TO bkpf_int.    ENDSELECT.  ENDSELECT.* Next we get the same document using the same fully qualified select* statement. We will use this in comparisons.  GET RUN TIME FIELD start.  DO p_loop2 TIMES.    REFRESH: bkpf_int.    SELECT  bukrs FROM t001      INTO  t001_wa      WHERE bukrs IN s_bukrs.      SELECT  bukrs belnr gjahr        FROM  bkpf        INTO  bkpf_wa        WHERE bukrs EQ t001_wa-bukrs          AND belnr IN s_belnr          AND gjahr EQ p_gjahr.        APPEND bkpf_wa TO bkpf_int.      ENDSELECT.    ENDSELECT.  ENDDO.  GET RUN TIME FIELD end.  dif = end - start.  WRITE: /001 'Time for second SELECT  (nested)',          055 ':', dif, 'microseconds'.ENDFORM.                    " nested_select*&---------------------------------------------------------------------**&      Form  for_all_entries*&---------------------------------------------------------------------**       text*----------------------------------------------------------------------*FORM for_all_entries .* Use the same fully qualified SELECT, but this time with* FOR ALL ENTRIES.* Ignore the first set of SELECTs  SELECT  bukrs    FROM  t001    INTO  TABLE t001_int    WHERE bukrs IN s_bukrs.  SELECT  bukrs belnr gjahr    FROM  bkpf    INTO TABLE bkpf_int    FOR ALL ENTRIES IN t001_int    WHERE bukrs EQ t001_int-bukrs      AND belnr IN s_belnr      AND gjahr EQ p_gjahr.* Use these SELECTs in comparisons.  GET RUN TIME FIELD start.  DO p_loop2 TIMES.    SELECT  bukrs      FROM  t001      INTO  TABLE t001_int      WHERE bukrs IN s_bukrs.    SELECT  bukrs belnr gjahr      FROM  bkpf      INTO TABLE bkpf_int      FOR ALL ENTRIES IN t001_int      WHERE bukrs EQ t001_int-bukrs        AND belnr IN s_belnr        AND gjahr EQ p_gjahr.  ENDDO.  GET RUN TIME FIELD end.  dif = end - start.  WRITE: /001 'Time for third SELECT   (using FOR ALL ENTRIES)',          055 ':', dif, 'microseconds'.ENDFORM.                    " for_all_entries*&---------------------------------------------------------------------**&      Form  inner_join*&---------------------------------------------------------------------**       text*----------------------------------------------------------------------*FORM inner_join .* Use the same fully qualified SELECT, but this time with an INNER JOIN.  SELECT  t001~bukrs bkpf~belnr bkpf~gjahr    FROM  bkpf      INNER JOIN t001 ON        t001~bukrs EQ bkpf~bukrs    INTO TABLE bkpf_int    WHERE t001~bukrs IN s_bukrs      AND bkpf~belnr IN s_belnr      AND bkpf~gjahr EQ p_gjahr.* Use this select in comparisons.  GET RUN TIME FIELD start.  DO p_loop2 TIMES.    SELECT  t001~bukrs bkpf~belnr bkpf~gjahr      FROM  bkpf        INNER JOIN t001 ON          t001~bukrs EQ bkpf~bukrs      INTO TABLE bkpf_int      WHERE t001~bukrs IN s_bukrs        AND bkpf~belnr IN s_belnr        AND bkpf~gjahr EQ p_gjahr.  ENDDO.  GET RUN TIME FIELD end.  dif = end - start.  WRITE: /001 'Time for fourth SELECT  (using an INNER JOIN)',          055 ':', dif, 'microseconds'.ENDFORM.                    " inner_join*&---------------------------------------------------------------------**&      Form  outer_join*&---------------------------------------------------------------------**       text*----------------------------------------------------------------------*FORM outer_join .* Use the same fully qualified SELECT, but this time with an OUTER JOIN.  SELECT  t001~bukrs bkpf~belnr bkpf~gjahr    FROM  bkpf      LEFT OUTER JOIN t001 ON        t001~bukrs EQ bkpf~bukrs    INTO TABLE bkpf_int    WHERE bkpf~bukrs IN s_bukrs      AND bkpf~belnr IN s_belnr      AND bkpf~gjahr EQ p_gjahr.* Use this select in comparisons.  GET RUN TIME FIELD start.  DO p_loop2 TIMES.    SELECT  t001~bukrs bkpf~belnr bkpf~gjahr      FROM  bkpf        LEFT OUTER JOIN t001 ON          t001~bukrs EQ bkpf~bukrs      INTO TABLE bkpf_int      WHERE bkpf~bukrs IN s_bukrs        AND bkpf~belnr IN s_belnr        AND bkpf~gjahr EQ p_gjahr.  ENDDO.  GET RUN TIME FIELD end.  dif = end - start.  WRITE: /001 'Time for fifth SELECT   (using an OUTER JOIN)',          055 ':', dif, 'microseconds'.ENDFORM.                    " outer_join*&---------------------------------------------------------------------**&      Form  sub_query*&---------------------------------------------------------------------**       text*----------------------------------------------------------------------*FORM sub_query .* And a sub-query  SELECT bukrs belnr gjahr    FROM bkpf    INTO TABLE bkpf_int    WHERE bukrs IN      ( SELECT bukrs          FROM  t001          WHERE bukrs IN s_bukrs )      AND belnr IN s_belnr      AND gjahr EQ p_gjahr.* Use this select in comparisons.  GET RUN TIME FIELD start.  DO p_loop2 TIMES.    SELECT  bukrs belnr gjahr      FROM  bkpf      INTO TABLE bkpf_int      WHERE bukrs IN        ( SELECT  bukrs            FROM  t001            WHERE bukrs IN s_bukrs )        AND belnr IN s_belnr        AND gjahr EQ p_gjahr.  ENDDO.  GET RUN TIME FIELD end.  dif = end - start.  WRITE: /001 'Time for sixth SELECT   (using a sub-query)',          055 ':', dif, 'microseconds'.ENDFORM.                    " sub_query*&---------------------------------------------------------------------**&      Form  unqualified_select*&---------------------------------------------------------------------** Compare the above results with a SELECT that is only partially* qualified.*----------------------------------------------------------------------*FORM unqualified_select.* Ignore the first SELECT  SELECT  bukrs belnr gjahr    FROM  bkpf    INTO  TABLE bkpf_int    WHERE belnr IN s_belnr      AND gjahr EQ  p_gjahr.* Use this select in comparisons.  GET RUN TIME FIELD start.  DO p_loop2 TIMES.    SELECT  bukrs belnr gjahr      FROM  bkpf      INTO  TABLE bkpf_int      WHERE belnr IN s_belnr        AND gjahr EQ  p_gjahr.  ENDDO.  GET RUN TIME FIELD end.  dif = end - start.  WRITE: /001 'Time for seventh SELECT (partially qualified)',          055 ':', dif, 'microseconds'.ENDFORM.                    " unqualified_select

The program has two SELECT-OPTIONS and one PARAMETER for selecting data: Company code, document number and fiscal year. I ran it four different ways: with a single company code and document number, with a single company code and a range of document numbers, with a range of company codes and a single document number and with ranges of both company codes and document numbers.

I ran the program a number of times in a 4.7 environment with DB2 databases. I was a bit surprised at some of the results:

For the simple case (single company code and document number) all of the methods worked almost equally well. The single fully qualified SELECT worked best, while the OUTER JOIN was worst. But the worst case only added about 25% execution time. The nested SELECT was really no worse than the others
With a single company code and range of document numbers, the execution times increased, but the overall results were quite similar to the simple case with the exception that the nested SELECT added about 75% to the execution time.
With a range of company codes either with a single or range of document numbers, the results were different: the execution times for both the OUTER JOIN and fully qualified SELECT were dramatically higher (500 to 1000 times) than the other methods. This (to me at least) was the really surprising result. The following statement:

SELECT bukrs belnr gjahr  FROM bkpf  INTO TABLE bkpf_int    WHERE bukrs IN s_bukrs     AND belnr IN s_belnr      AND gjahr EQ p_gjahr.

Is far less efficient than:

SELECT t001~bukrs bkpf~belnr bkpf~gjahrFROM bkpf  INNER JOIN t001 ON  t001~bukrs EQ bkpf~bukrs  INTO TABLE bkpf_int  WHERE t001~bukrs IN s_bukrs    AND bkpf~belnr IN s_belnr    AND bkpf~gjahr EQ p_gjahr.

when a range of company codes is used. The increase in execution time for the OUTER JOIN is probably due to the fact that I could not use T001~BUKRS in the WHERE clause because of that limitation on OUTER JOINs

In the final analysis, there is no “one size fits all” answer to the question is “Which is better: a JOIN or FOR ALL ENTRIES ?” In many, if not most cases, my money is on the JOIN , but the difference is not large enough to spend much time jumping through hoops to pare off the last microsecond. In the end, if you are interested in the differences for your particular case, then you must code different SELECTs to find which is best. But then you also have to bear in mind that the same SELECT may behave differently based on the makeup of the WHERE clause.

There are other considerations that come into play as well:

INNER JOINs only look at the intersection of the results that meet the WHERE clause.
FOR ALL ENTRIES eliminates duplicates from the results.
I find JOINs to be more time consuming to code. (I can never find the “~” key.)
When using FOR ALL ENTRIES you generally end up with at least two internal tables. This may or may not be a good thing.
The example I have shown uses the full primary key. Some preliminary testing I have done comparing JOINs with FOR ALL ENTRIES show that FOR ALL ENTRIES can give better performance in that case.

One final thing to note: in the above program, the one SELECT that consistently underperformed was the one that did not use the index effectively. And that is the real point here. All of the techniques that I have shown here work reasonably effectively. The most important thing to remember is to use an index.

Performance - what will kill you and what will leave you with only a flesh wound

Posted by Rob Burbank Nov 16, 2006

In the Performance of Nested Loops, I showed that nested loops can give far worse performance than a poorly constructed SELECT statement. In Using an Index When You Don't Have all of the Fields, I showed some tricks that will allow you to use an index. In this blog I will look at some other performance tuning tips to see how they stack up against these two.

I wrote four programs to examine this. Each one provides statistics at the end to show how long portions of it took. Each one also includes a small report (that is commented out) that you can produce to ensure that it produces the same report as the other programs. Before you look at them, I should point out some important caveats:

I'm not concerned with making sure that range tables are not empty before doing a SELECT. I consider this to be a logic error and out of the scope of this blog. The same goes for internal tables used in SELECT ... FOR ALL ENTRIES.
I didn't consider badly constructed JOINS (joins on non key fields). I think this is just a subset of not using an index.
I didn't look at aggregate functions.
I didn't consider nested calls to RFC enabled function modules. This can actually be a serious problem, but it's one that is not mentioned in the forums, so although it can cause bad performance, it doesn't seem to occur very frequently.
Although there has been discussion in the forums about which is better - joins or FOR ALL ENTRIES, I'm not trying to pick a winner here either. My own testing (which may form a later blog) shows inconsistent results.
I also didn't consider READ statements that don't use a binary search. They are similar to LOOP/EXIT/ENDLOOP.

If you decide to run these programs, buffering will definitely be an issue. I ran all four programs overnight (one program per night) when there would be little other activity and without the effects of hardware buffering.

The first two programs may run for a very long time.

The first program disobeys a number of performance rules and is presented below:

report ztest1 line-size 120 message-id 00 line-count 44.

data: bkpf type bkpf, bseg type bseg, lfa1 type lfa1, ekko type ekko, ekpo type ekpo.

select-options: s_lifnr for bseg-lifnr memory id lif obligatory.

types: begin of fi_tab, bukrs type bseg-bukrs, belnr type bseg-belnr, gjahr type bseg-gjahr, buzei type bseg-buzei, lifnr type bseg-lifnr, ebeln type bseg-ebeln, ebelp type bseg-ebelp, budat type bkpf-budat, waers type bkpf-waers, blart type bkpf-blart, usnam type bkpf-usnam, cpudt type bkpf-cpudt, cputm type bkpf-cputm, name1 type kna1-name1, end of fi_tab.

types: begin of po_tab, ebeln type ekpo-ebeln, ebelp type ekpo-ebelp, loekz type ekko-loekz, lifnr type ekko-lifnr, aedat type ekko-aedat, ernam type ekko-ernam, loekz1 type ekpo-loekz, menge type ekpo-menge, netwr type ekpo-netwr, end of po_tab.

types: begin of merge_tab, bukrs type bseg-bukrs, belnr type bseg-belnr, gjahr type bseg-gjahr, buzei type bseg-buzei, name1 type kna1-name1, ebeln type ekko-ebeln, lifnr type ekko-lifnr, ernam type ekko-ernam, waers type bkpf-waers, curr(20), end of merge_tab.

data: fi_int type table of fi_tab, fi_wa type fi_tab, po_int type table of po_tab, po_wa type po_tab, merge_int type table of merge_tab, merge_wa type merge_tab, copy_int type table of merge_tab, copy_wa type merge_tab.

data: pgm_start type sy-uzeit, pgm_end type sy-uzeit, sel1_start type sy-uzeit, sel1_end type sy-uzeit, sel2_start type sy-uzeit, sel2_end type sy-uzeit, merge_start type sy-uzeit, merge_end type sy-uzeit, dif1 type i, dif2 type i, dif3 type i, dif4 type i, no_lines type i.

initialization. perform init_parm.

start-of-selection.

get time field pgm_start.

perform get_fi. perform get_po. perform merge_data. perform copy_itab.

PERFORM write_data.

get time field pgm_end. dif4 = pgm_end - pgm_start.

perform write_statistics.

top-of-page. perform write_heading.

&----

*& Form init_parm &----

text

----

form init_parm .

endform. " init_parm

&----

*& Form get_fi &----

Possible sources of poor performance in this FORM:
- Nested SELECTS
- SELECT *
- MOVE-CORRESPONDING

----

form get_fi .

get time field sel1_start.

select * from bseg where gjahr > '2001' and lifnr in s_lifnr and koart = 'K'. move-corresponding bseg to fi_wa.

select single * from bkpf where bukrs = bseg-bukrs and belnr = bseg-belnr and gjahr = bseg-gjahr. if sy-subrc = 0. move-corresponding bkpf to fi_wa. endif.

select single * from lfa1 where lifnr = bseg-lifnr. if sy-subrc = 0. move-corresponding lfa1 to fi_wa. endif.

append fi_wa to fi_int. endselect.

if sy-subrc <> 0. message e001 with 'No FI data selected'. endif.

sort fi_int by bukrs belnr gjahr.

get time field sel1_end. dif1 = sel1_end - sel1_start.

endform. " get_fi

&----

*& Form get_po &----

Possible sources of poor performance in this FORM:
- Nested SELECT

----

form get_po .

get time field sel2_start.

select * from ekko where lifnr in s_lifnr. move-corresponding ekko to po_wa.

select * from ekpo where ebeln = ekko-ebeln. move-corresponding ekpo to po_wa. po_wa-loekz1 = ekpo-loekz. po_wa-aedat = ekko-aedat.

append po_wa to po_int.

endselect. endselect.

if sy-subrc <> 0. message e001 with 'No PO data selected'. endif.

get time field sel2_end. dif2 = sel2_end - sel2_start.

endform. " get_po

&----

*& Form merge_data &----

Possible sources of poor performance in this FORM:
- Using LOOP AT <itab> without ASSIGNING
- IF/ELSEIF
- Nested LOOPs

----

form merge_data .

sort: po_int by lifnr ernam aedat, fi_int by lifnr usnam cpudt.

get time field merge_start.

loop at po_int into po_wa.

loop at fi_int into fi_wa where lifnr = po_wa-lifnr and usnam = po_wa-ernam and cpudt = po_wa-aedat. move-corresponding fi_wa to merge_wa. move-corresponding po_wa to merge_wa. append merge_wa to merge_int. endloop. endloop.

loop at merge_int into merge_wa. if merge_wa-waers = 'CAD'. merge_wa-curr = 'Canadian dollars'. elseif merge_wa-waers = 'USD'. merge_wa-curr = 'U. S. dollars'. elseif merge_wa-waers = 'GBP'. merge_wa-curr = 'British pounds'. elseif merge_wa-waers = 'EUR'. merge_wa-curr = 'Euros'. else. merge_wa-curr = 'Other'. endif.

modify merge_int from merge_wa.

endloop.

get time field merge_end. dif3 = merge_end - merge_start.

endform. " merge_data

&----

*& Form copy_itab &----

Possible sources of poor performance in this FORM:
- LOOP AT <itab> APPEND ENDLOOP

----

form copy_itab .

loop at merge_int into merge_wa. move-corresponding merge_wa to copy_wa. append copy_wa to copy_int. endloop.

delete adjacent duplicates from copy_int comparing bukrs belnr gjahr ebeln.

endform. " copy_itab

&----

*& Form write_heading &----

text

----

form write_heading .

write: /037 'Test Report to Validate Data Selection'. write: /001 'CoCd', 006 'Doc No', 018 'FYr', 024 'PONo', 036 'Ven No', 052 'Vendor Name', 089 'UserID', 101 'Currency'. skip 1.

endform. " write_heading

&----

*& Form write_data &----

text

----

form write_data .

loop at copy_int into copy_wa. write: / copy_wa-bukrs under 'CoCd', copy_wa-belnr under 'Doc No', copy_wa-gjahr under 'FYr', copy_wa-ebeln under 'PONo', copy_wa-lifnr under 'Ven No', copy_wa-name1 under 'Vendor Name', copy_wa-ernam under 'UserID', copy_wa-curr under 'Currency'. endloop.

if sy-subrc = 0. skip 1. write: /001 'Number of rows selected: ', no_lines. else. write: /001 'No data selected'. endif.

endform. " write_data

&----

*& Form write_statistics &----

text

----

form write_statistics .

describe table fi_int lines no_lines. write: /001 'Number of FI records selected :', no_lines.

describe table po_int lines no_lines. write: /001 'Number of PO records selected :', no_lines.

describe table copy_int lines no_lines. write: /001 'Number of merged records :', no_lines.

skip 1. write: /001 'Time for unoptimized select on FI data :', dif1, 'seconds'. write: /001 'Time for unoptimized select on PO data :', dif2, 'seconds'. write: /001 'Time for unoptimized merge of data :', dif3, 'seconds'. write: /001 'Time for unoptimized program :', dif4, 'seconds'.

endform. " write_statistics type fi_tab.

data: pgm_start type sy-uzeit, pgm_end type sy-uzeit, sel1_start type sy-uzeit, sel1_end type sy-uzeit, sel2_start type sy-uzeit, sel2_end type sy-uzeit, merge_start type sy-uzeit, merge_end type sy-uzeit, dif1 type i, dif2 type i, dif3 type i, dif4 type i, no_lines type i.

initialization. perform init_parm.

start-of-selection.

get time field pgm_start.

perform get_fi. perform get_po. perform merge_data. perform copy_itab.

PERFORM write_data.

get time field pgm_end. dif4 = pgm_end - pgm_start.

perform write_statistics.

top-of-page. perform write_heading.

&----

*& Form init_parm &----

text

----

form init_parm .

endform. " init_parm

&----

*& Form get_fi &----

Optimize this FORM by:
- Replacing nested SELECTs with SELECT INTO TABLE
- Replacing SELECT * with SELECT <field list>
- Using LOOP AT <itab> ASSIGNING
- Use a BINARY SEARCH

----

form get_fi .

get time field sel1_start.

select bukrs belnr gjahr buzei lifnr ebeln ebelp from bseg into table bseg_int where gjahr > '2001' and lifnr in s_lifnr and koart = 'K'.

if sy-subrc -name1 to fi_wa-name1. endif.

append fi_wa to fi_int. endloop.

sort fi_int by bukrs belnr gjahr.

get time field sel1_end. dif1 = sel1_end - sel1_start.

endform. " get_fi

&----

*& Form get_po &----

Optimize this FORM by:
- Replacing nested SELECT with a JOIN

----

form get_po .

get time field sel2_start.

select ekko_{ebeln ekpo}ebelp ekko_{loekz ekko}lifnr ekko~aedat ekko_{ernam ekpo}loekz ekpo_{menge ekpo}netwr from ekko join ekpo on ekpo_{ebeln = ekko}ebeln into table po_int where lifnr in s_lifnr.

if sy-subrc <> 0. message e001 with 'No PO data selected'. endif.

get time field sel2_end. dif2 = sel2_end - sel2_start.

endform. " get_po

&----

*& Form merge_data &----

Optimize this FORM by:
- Using LOOP AT <itab> ASSIGNING (first loop)
- Using MODIFY TRANSPORTING (second loop)
- Replace IF/ELSEIF with CASE

----

form merge_data .

sort: po_int by lifnr ernam aedat, fi_int by lifnr usnam cpudt.

get time field merge_start.

loop at po_int assigning -waers to merge_wa-waers. append merge_wa to merge_int. endloop. endloop.

loop at merge_int into merge_wa. case merge_wa-waers. when 'CAD'. merge_wa-curr = 'Canadian dollars'. when 'USD'. merge_wa-curr = 'U. S. dollars'. when 'GBP'. merge_wa-curr = 'British pounds'. when 'EUR'. merge_wa-curr = 'Euros'. when others. merge_wa-curr = 'Other'. endcase.

modify merge_int from merge_wa transporting curr.

endloop.

get time field merge_end. dif3 = merge_end - merge_start.

endform. " merge_data

&----

*& Form copy_itab &----

Optimize this FORM by:
- Replace LOOP AT <itab> APPEND ENDLOOP
with <itab2> = <itab1>

----

form copy_itab .

copy_int[] = merge_int[].

delete adjacent duplicates from copy_int comparing bukrs belnr gjahr ebeln.

endform. " copy_itab

&----

*& Form write_heading &----

text

----

form write_heading .

write: /037 'Test Report to Validate Data Selection'. write: /001 'CoCd', 006 'Doc No', 018 'FYr', 024 'PONo', 036 'Ven No', 052 'Vendor Name', 089 'UserID', 101 'Currency'. skip 1.

endform. " write_heading

&----

*& Form write_data &----

text

----

form write_data .

describe table copy_int lines no_lines.

loop at copy_int into copy_wa. write: / copy_wa-bukrs under 'CoCd', copy_wa-belnr under 'Doc No', copy_wa-gjahr under 'FYr', copy_wa-ebeln under 'PONo', copy_wa-lifnr under 'Ven No', copy_wa-name1 under 'Vendor Name', copy_wa-ernam under 'UserID', copy_wa-curr under 'Currency'. endloop.

if sy-subrc = 0. skip 1. write: /001 'Number of rows selected: ', no_lines. else. write: /001 'No data selected'. endif.

endform. " write_data

&----

*& Form write_statistics &----

text

----

form write_statistics .

describe table fi_int lines no_lines. write: /001 'Number of FI records selected :', no_lines.

describe table po_int lines no_lines. write: /001 'Number of PO records selected :', no_lines.

describe table copy_int lines no_lines. write: /001 'Number of merged records :', no_lines.

skip 1. write: /001 'Time for poorly optimized select on FI data :', dif1, 'seconds'. write: /001 'Time for poorly optimized select on PO data :', dif2, 'seconds'. write: /001 'Time for poorly optimized merge of data :', dif3, 'seconds'. write: /001 'Time for poorly optimized program :', dif4, 'seconds'.

endform. " write_statistics

The third program uses just two techniques. I've jumped through a couple of hoops to make sure I use an index and I've replaced the nested loops with a single loop and binary reads. Here is the final program:

report ztest3 line-size 120 message-id 00 line-count 44.

data: bkpf type bkpf, bseg type bseg, lfa1 type lfa1, ekko type ekko, ekpo type ekpo.

select-options: s_lifnr for bseg-lifnr memory id lif obligatory.

types: begin of fi_tab, bukrs type bseg-bukrs, belnr type bseg-belnr, gjahr type bseg-gjahr, buzei type bseg-buzei, lifnr type bseg-lifnr, ebeln type bseg-ebeln, ebelp type bseg-ebelp, budat type bkpf-budat, waers type bkpf-waers, blart type bkpf-blart, usnam type bkpf-usnam, cpudt type bkpf-cpudt, cputm type bkpf-cputm, name1 type kna1-name1, end of fi_tab.

types: begin of po_tab, ebeln type ekpo-ebeln, ebelp type ekpo-ebelp, loekz type ekko-loekz, lifnr type ekko-lifnr, aedat type ekko-aedat, ernam type ekko-ernam, loekz1 type ekpo-loekz, menge type ekpo-menge, netwr type ekpo-netwr, end of po_tab.

types: begin of merge_tab, bukrs type bseg-bukrs, belnr type bseg-belnr, gjahr type bseg-gjahr, buzei type bseg-buzei, name1 type kna1-name1, ebeln type ekko-ebeln, lifnr type ekko-lifnr, ernam type ekko-ernam, waers type bkpf-waers, curr(20), end of merge_tab.

data: fi_int type table of fi_tab, fi_wa type fi_tab, po_int type table of po_tab, po_wa type po_tab, merge_int type table of merge_tab, merge_wa type merge_tab, copy_int type table of merge_tab, copy_wa type merge_tab, bsik_wa type bsik, bsak_wa type bsak, bkpf_wa type bkpf, lfa1_wa type lfa1, ekko_wa type ekko, ekpo_wa type ekpo.

data: pgm_start type sy-uzeit, pgm_end type sy-uzeit, sel1_start type sy-uzeit, sel1_end type sy-uzeit, sel2_start type sy-uzeit, sel2_end type sy-uzeit, merge_start type sy-uzeit, merge_end type sy-uzeit, dif1 type i, dif2 type i, dif3 type i, dif4 type i, no_lines type i, fi_index type sy-tabix.

initialization. perform init_parm.

start-of-selection.

get time field pgm_start.

perform get_fi. perform get_po. perform merge_data. perform copy_itab.

PERFORM write_data.

get time field pgm_end. dif4 = pgm_end - pgm_start.

perform write_statistics.

top-of-page. perform write_heading.

&----

*& Form init_parm &----

text

----

form init_parm .

endform. " init_parm

&----

*& Form get_fi &----

Optimize this FORM by:
- Replacing SELECT against BSEG with two SELECTS against BSIK
and BSAK in order to use an index

----

form get_fi .

get time field sel1_start.

select * from bsik into bsik_wa where lifnr in s_lifnr and gjahr > '2001'. move-corresponding bsik_wa to fi_wa.

select single * from bkpf into bkpf_wa where bukrs = bsik_wa-bukrs and belnr = bsik_wa-belnr and gjahr = bsik_wa-gjahr. if sy-subrc = 0. move-corresponding bkpf_wa to fi_wa. endif.

select single * from lfa1 into lfa1_wa where lifnr = bsik_wa-lifnr. if sy-subrc = 0. move-corresponding lfa1_wa to fi_wa. endif.

append fi_wa to fi_int. endselect.

select * from bsak into bsak_wa where lifnr in s_lifnr and gjahr > '2001'. move-corresponding bsak_wa to fi_wa.

select single * from bkpf into bkpf_wa where bukrs = bsak_wa-bukrs and belnr = bsak_wa-belnr and gjahr = bsak_wa-gjahr. if sy-subrc = 0. move-corresponding bkpf_wa to fi_wa. endif.

select single * from lfa1 into lfa1_wa where lifnr = bsak_wa-lifnr. if sy-subrc = 0. move-corresponding lfa1_wa to fi_wa. endif.

append fi_wa to fi_int. endselect.

describe table fi_int lines no_lines.

if no_lines = 0. message e001 with 'No FI data selected'. endif.

sort fi_int by bukrs belnr gjahr.

get time field sel1_end. dif1 = sel1_end - sel1_start.

endform. " get_fi

&----

*& Form get_po &----

This form is doing a SELECT that is using a secondary index
- Leave it alone

----

form get_po .

get time field sel2_start.

select * from ekko into ekko_wa where lifnr in s_lifnr. move-corresponding ekko_wa to po_wa.

select * from ekpo into ekpo_wa where ebeln = ekko_wa-ebeln. move-corresponding ekpo_wa to po_wa. po_wa-loekz1 = ekpo_wa-loekz. po_wa-aedat = ekko_wa-aedat.

append po_wa to po_int.

endselect. endselect.

if sy-subrc <> 0. message e001 with 'No PO data selected'. endif.

get time field sel2_end. dif2 = sel2_end - sel2_start.

endform. " get_po

&----

*& Form merge_data &----

Optimize this FORM by:
- Using BINARY SEARCH and INDEXed READ

----

form merge_data .

sort: po_int by lifnr ernam aedat, fi_int by lifnr usnam cpudt.

get time field merge_start.

loop at po_int into po_wa. read table fi_int with key lifnr = po_wa-lifnr usnam = po_wa-ernam cpudt = po_wa-aedat binary search into fi_wa.

fi_index = sy-tabix.

while sy-subrc = 0. move-corresponding fi_wa to merge_wa. move-corresponding po_wa to merge_wa. append merge_wa to merge_int.

fi_index = fi_index + 1. read table fi_int index fi_index into fi_wa.

if fi_wa-lifnr <> po_wa-lifnr or fi_wa-usnam <> po_wa-ernam or fi_wa-cpudt <> po_wa-aedat. sy-subrc = 9. endif.

endwhile. endloop.

loop at merge_int into merge_wa. case merge_wa-waers. when 'CAD'. merge_wa-curr = 'Canadian dollars'. when 'USD'. merge_wa-curr = 'U. S. dollars'. when 'GBP'. merge_wa-curr = 'British pounds'. when 'EUR'. merge_wa-curr = 'Euros'. when others. merge_wa-curr = 'Other'. endcase.

modify merge_int from merge_wa transporting curr.

endloop.

get time field merge_end. dif3 = merge_end - merge_start.

endform. " merge_data

&----

*& Form copy_itab &----

Do not optimize this form

----

form copy_itab .

loop at merge_int into merge_wa. move-corresponding merge_wa to copy_wa. append copy_wa to copy_int. endloop.

delete adjacent duplicates from copy_int comparing bukrs belnr gjahr ebeln.

endform. " copy_itab

&----

*& Form write_heading &----

text

----

form write_heading .

write: /037 'Test Report to Validate Data Selection'. write: /001 'CoCd', 006 'Doc No', 018 'FYr', 024 'PONo', 036 'Ven No', 052 'Vendor Name', 089 'UserID', 101 'Currency'. skip 1.

endform. " write_heading

&----

*& Form write_data &----

text

----

form write_data .

describe table copy_int lines no_lines.

loop at copy_int into copy_wa. write: / copy_wa-bukrs under 'CoCd', copy_wa-belnr under 'Doc No', copy_wa-gjahr under 'FYr', copy_wa-ebeln under 'PONo', copy_wa-lifnr under 'Ven No', copy_wa-name1 under 'Vendor Name', copy_wa-ernam under 'UserID', copy_wa-curr under 'Currency'. endloop.

if sy-subrc = 0. skip 1. write: /001 'Number of rows selected: ', no_lines. else. write: /001 'No data selected'. endif.

endform. " write_data

&----

*& Form write_statistics &----

text

----

form write_statistics .

describe table fi_int lines no_lines. write: /001 'Number of FI records selected :', no_lines.

describe table po_int lines no_lines. write: /001 'Number of PO records selected :', no_lines.

describe table copy_int lines no_lines. write: /001 'Number of merged records :', no_lines.

skip 1. write: /001 'Time for better optimized select on FI data :', dif1, 'seconds'. write: /001 'Time for better optimized select on PO data :', dif2, 'seconds'. write: /001 'Time for better optimized merge of data :', dif3, 'seconds'. write: /001 'Time for better optimized program :', dif4, 'seconds'.

endform. " write_statistics TYPE fi_tab.

DATA: pgm_start TYPE sy-uzeit, pgm_end TYPE sy-uzeit, sel1_start TYPE sy-uzeit, sel1_end TYPE sy-uzeit, sel2_start TYPE sy-uzeit, sel2_end TYPE sy-uzeit, merge_start TYPE sy-uzeit, merge_end TYPE sy-uzeit, dif1 TYPE i, dif2 TYPE i, dif3 TYPE i, dif4 TYPE i, no_lines TYPE i, fi_index TYPE sy-tabix.

INITIALIZATION. PERFORM init_parm.

START-OF-SELECTION.

GET TIME FIELD pgm_start.

PERFORM get_fi. PERFORM get_po. PERFORM merge_data. PERFORM copy_itab.

PERFORM write_data.

GET TIME FIELD pgm_end. dif4 = pgm_end - pgm_start.

PERFORM write_statistics.

TOP-OF-PAGE. PERFORM write_heading.

&----

*& Form init_parm &----

text

----

FORM init_parm .

ENDFORM. " init_parm

&----

*& Form get_fi &----

Optimize this FORM by:
- Replacing SELECT against BSEG with two SELECTS against BSIK
and BSAK in order to use an index
- Replacing nested SELECTs with SELECT INTO TABLE
- Replacing SELECT * with SELECT <field list>
- Using LOOP AT <itab> ASSIGNING
- Use a BINARY SEARCH

----

FORM get_fi .

GET TIME FIELD sel1_start.

SELECT bukrs belnr gjahr buzei lifnr ebeln ebelp FROM bsik INTO TABLE bseg_int WHERE lifnr IN s_lifnr AND gjahr > '2001'.

IF sy-subrc -name1 TO fi_wa-name1. ENDIF.

APPEND fi_wa TO fi_int. ENDLOOP.

SORT fi_int BY bukrs belnr gjahr.

GET TIME FIELD sel1_end. dif1 = sel1_end - sel1_start.

ENDFORM. " get_fi

&----

*& Form get_po &----

Optimize this FORM by:
- Replacing nested SELECT with a JOIN

----

FORM get_po .

GET TIME FIELD sel2_start.

SELECT ekko_{ebeln ekpo}ebelp ekko_{loekz ekko}lifnr ekko~aedat ekko_{ernam ekpo}loekz ekpo_{menge ekpo}netwr FROM ekko JOIN ekpo ON ekpo_{ebeln = ekko}ebeln INTO TABLE po_int WHERE lifnr IN s_lifnr.

IF sy-subrc <> 0. MESSAGE e001 WITH 'No PO data selected'. ENDIF.

GET TIME FIELD sel2_end. dif2 = sel2_end - sel2_start.

ENDFORM. " get_po

&----

*& Form merge_data &----

Optimize this FORM by:
- Using BINARY SEARCH and INDEXed READ
- Using LOOP AT <itab> ASSIGNING (first loop)
- Using MODIFY TRANSPORTING (second loop)
- Replace IF/ELSEIF with CASE

----

FORM merge_data .

SORT: po_int BY lifnr ernam aedat, fi_int BY lifnr usnam cpudt.

GET TIME FIELD merge_start.

LOOP AT po_int ASSIGNING -aedat. sy-subrc = 9. ENDIF.

ENDWHILE.

ENDLOOP.

LOOP AT merge_int INTO merge_wa. CASE merge_wa-waers. WHEN 'CAD'. merge_wa-curr = 'Canadian dollars'. WHEN 'USD'. merge_wa-curr = 'U. S. dollars'. WHEN 'GBP'. merge_wa-curr = 'British pounds'. WHEN 'EUR'. merge_wa-curr = 'Euros'. WHEN OTHERS. merge_wa-curr = 'Other'. ENDCASE.

MODIFY merge_int FROM merge_wa TRANSPORTING curr.

ENDLOOP.

GET TIME FIELD merge_end. dif3 = merge_end - merge_start.

ENDFORM. " merge_data

&----

*& Form copy_itab &----

Optimize this FORM by:
- Replace LOOP AT <itab> APPEND ENDLOOP
with <itab2> = <itab1>

----

FORM copy_itab .

copy_int[] = merge_int[].

DELETE ADJACENT DUPLICATES FROM copy_int COMPARING bukrs belnr gjahr ebeln.

ENDFORM. " copy_itab

&----

*& Form write_heading &----

text

----

FORM write_heading .

WRITE: /037 'Test Report to Validate Data Selection'. WRITE: /001 'CoCd', 006 'Doc No', 018 'FYr', 024 'PONo', 036 'Ven No', 052 'Vendor Name', 089 'UserID', 101 'Currency'. SKIP 1.

ENDFORM. " write_heading

&----

*& Form write_data &----

text

----

FORM write_data .

DESCRIBE TABLE copy_int LINES no_lines.

LOOP AT copy_int INTO copy_wa. WRITE: / copy_wa-bukrs UNDER 'CoCd', copy_wa-belnr UNDER 'Doc No', copy_wa-gjahr UNDER 'FYr', copy_wa-ebeln UNDER 'PONo', copy_wa-lifnr UNDER 'Ven No', copy_wa-name1 UNDER 'Vendor Name', copy_wa-ernam UNDER 'UserID', copy_wa-curr UNDER 'Currency'. ENDLOOP.

IF sy-subrc = 0. SKIP 1. WRITE: /001 'Number of rows selected: ', no_lines. ELSE. WRITE: /001 'No data selected'. ENDIF.

ENDFORM. " write_data

&----

*& Form write_statistics &----

text

----

FORM write_statistics .

DESCRIBE TABLE fi_int LINES no_lines. WRITE: /001 'Number of FI records selected :', no_lines.

DESCRIBE TABLE po_int LINES no_lines. WRITE: /001 'Number of PO records selected :', no_lines.

DESCRIBE TABLE copy_int LINES no_lines. WRITE: /001 'Number of merged records :', no_lines.

SKIP 1. WRITE: /001 'Time for highly optimized select on FI data :', dif1, 'seconds'. WRITE: /001 'Time for highly optimized select on PO data :', dif2, 'seconds'. WRITE: /001 'Time for highly optimized merge of data :', dif3, 'seconds'. WRITE: /001 'Time for highly optimized program :', dif4, 'seconds'.

ENDFORM. " write_statistics

I ran each program three times: once with a single vendor to pick up a small amount of data; once with a range of vendors to pick up a moderately large amount of data and once with a wide open range of vendors to pick up as much data as possible. And then I ran the programs multiple times.

So - What are the Results

When selecting small amounts of data, using an index made the select run about 45 times faster. Then, removing nested SELECTs made it run about an extra 15 times faster. So the most important performance consideration is the use of an index. Removing the nested SELECTs also seemed to help, but these results may be skewed by fact that I only ran the highly optimized program once for this situation.

When selecting larger amounts of data, the elimination of nested loops reduced the run time from over an hour and a half to under a second. The use of the index sped the select up by a factor of five or six. Clearly, in this situation, removing nested loops is the most important performance tool. The use of an index will definitely help, but becomes less important as more of the database is selected.

When selecting very large amounts of data, the elimination of nested loops is still the most important performance tool. (I was not able to run either of the first two programs in under a day because of this.) Replacing nested SELECTS with FOR ALL ENTRIES reduced the run time by about a factor of two and so, helped in this case as well. The use of an index really doesn't matter when you're trying to select the whole database.

Conclusions

Remember the initial assumption - you must fix an existing program that performs poorly. In all likelihood you will find that this is from one of three causes:

The failure to use an index in SELECT(s)
The use of nested LOOPs
Nested SELECT(s)

Depending on the amount of data the program is retrieving, fix the one(s) that are causing the problem(s). While you can always tune a program to run minutes, seconds or just milliseconds faster, at some point, you will run into the point of diminishing returns. It will take you longer to make the program changes than the users will save due to those program changes. You also have to bear in mind that making any changes to an existing program that works carries some dangers. There is always the risk that the changes you make may make the program run faster, but produce incorrect results. Even if the user signs off on these changes, problems may arise later that may be caused by those changes.

In the final analysis, when writing new programs, I try to use as many of the tuning techniques as I can. But if I have a problem with an existing one, I look no further than nested loops and indexes.

Using an Index When You Don't Have all of the Fields

Posted by Rob Burbank Sep 13, 2006

One of the most important considerations when writing a select statement against a large table is the effective use of an index. However this is sometimes more easily said than done. Have you ever found that your WHERE clause is missing just one field of an index and that field is not at the end of the index?

There are some situations where you can make more effective use of the entire index even if you are missing a field. Here is a simple trick to help you do just that. If the field you are missing cannot contain too many entries, then if you create a range table with all possible entries and add that range table to your WHERE clause, you can dramatically speed up a SELECT statement. Even when you take into account the extra time needed to retrieve the key fields, the results are worth it. This may seem a bit counter-intuitive, but the example code shows what I'm doing (but be careful – if you run this code in a QA environment, it may take a while):

REPORT ztest_indexed_selects.PARAMETERS: p_bukrs LIKE bkpf-bukrs MEMORY ID buk OBLIGATORY, p_belnr LIKE bkpf-belnr MEMORY ID bln OBLIGATORY, p_gjahr LIKE bkpf-gjahr MEMORY ID gjr OBLIGATORY.TYPES: BEGIN OF bkpf_fields, bukrs LIKE bkpf-bukrs, belnr LIKE bkpf-belnr, gjahr LIKE bkpf-gjahr, blart LIKE bkpf-blart, budat LIKE bkpf-budat, END OF bkpf_fields.DATA: bkpf TYPE bkpf, dd07l TYPE dd07l.DATA: bkpf_int TYPE TABLE OF bkpf_fields, bkpf_wa TYPE bkpf_fields.DATA: start TYPE i, end TYPE i, dif TYPE i.START-OF-SELECTION. PERFORM get_one_document. PERFORM unindexed_select_bkpf. PERFORM indexed_select_bkpf. PERFORM unindexed_select_bkpf_2. PERFORM indexed_select_bkpf_2.*&---------------------------------------------------------------------**& Form get_one_document*&---------------------------------------------------------------------** text*----------------------------------------------------------------------*FORM get_one_document.* First we get a single document using a select statement that is* fully qualified on the primary key. Because buffering may be an issue,* the first select will be disregarded in this test. However, in real* life, this would be the important time.* Initial select SELECT bukrs belnr gjahr blart budat FROM bkpf INTO TABLE bkpf_int WHERE bukrs = p_bukrs AND belnr = p_belnr AND gjahr = p_gjahr. IF sy-subrc <> 0. MESSAGE ID '00' TYPE 'E' NUMBER '001' WITH 'Document does not exist'. ENDIF.* Next we get the same document using the same fully qualified select* statement. We will use the time for this in comparisons. REFRESH bkpf_int. GET RUN TIME FIELD start. SELECT bukrs belnr gjahr blart budat FROM bkpf INTO TABLE bkpf_int WHERE bukrs = p_bukrs AND belnr = p_belnr AND gjahr = p_gjahr. GET RUN TIME FIELD end. dif = end - start. WRITE: /001 'Time for first (fully qualified) select', 067 ':', dif, 'microseconds'. SKIP 1.* So we can use these fields later on READ TABLE bkpf_int INTO bkpf_wa INDEX 1.ENDFORM. " get_one_document*&---------------------------------------------------------------------**& Form unindexed_select_bkpf*&---------------------------------------------------------------------** text*----------------------------------------------------------------------*FORM unindexed_select_bkpf.* Now we select a group of documents using a select statement that is* missing the company code from the primary key. This may return a* different set of documents from the first select, but we are just* interested in how long it takes.* Initial select REFRESH bkpf_int. SELECT bukrs belnr gjahr blart budat FROM bkpf INTO TABLE bkpf_int WHERE belnr = p_belnr AND gjahr = p_gjahr. REFRESH bkpf_int. GET RUN TIME FIELD start.* Use this select in comparisons SELECT bukrs belnr gjahr blart budat FROM bkpf INTO TABLE bkpf_int WHERE belnr = p_belnr AND gjahr = p_gjahr. GET RUN TIME FIELD end. dif = end - start. WRITE: /001 'Time for second (unindexed) select', 067 ':', dif, 'microseconds'.ENDFORM. " unindexed_select_bkpf*&---------------------------------------------------------------------**& Form indexed_select_bkpf*&---------------------------------------------------------------------** text*----------------------------------------------------------------------*FORM indexed_select_bkpf.* Now we're going to use the first trick. Go to table T001 (company* codes) and retrieve all the company codes and put them into a range* table. We'll put the range table into the select. So long as the* number of company codes is not too great, this will speed up the* select on BKPF. RANGES: r_bukrs FOR bkpf-bukrs.* Preliminary selects r_bukrs-option = 'EQ'. r_bukrs-sign = 'I'. SELECT bukrs FROM t001 INTO r_bukrs-low. APPEND r_bukrs. ENDSELECT. REFRESH bkpf_int. SELECT bukrs belnr gjahr blart budat FROM bkpf INTO TABLE bkpf_int WHERE bukrs IN r_bukrs AND belnr = p_belnr AND gjahr = p_gjahr. REFRESH: bkpf_int, r_bukrs. GET RUN TIME FIELD start.* Use these selects in comparison r_bukrs-option = 'EQ'. r_bukrs-sign = 'I'. SELECT bukrs FROM t001 INTO r_bukrs-low. APPEND r_bukrs. ENDSELECT. SELECT bukrs belnr gjahr blart budat FROM bkpf INTO TABLE bkpf_int WHERE bukrs IN r_bukrs AND belnr = p_belnr AND gjahr = p_gjahr. GET RUN TIME FIELD end. dif = end - start. WRITE: /001 'Time for third select', '(indexed by selecting from the check table)', 067 ':', dif, 'microseconds'. SKIP 1.ENDFORM. " indexed_select_bkpf*&---------------------------------------------------------------------**& Form unindexed_select_bkpf_2*&---------------------------------------------------------------------** text*----------------------------------------------------------------------*FORM unindexed_select_bkpf_2.* Now we'll get another group of records from BKPF. There is a* secondary index on BKPF with fields BUKRS, BSTAT and BUDAT.* We're going to leave BSTAT out of the select and use* BUKRS and BUDAT from the first document we selected.* Preliminary select - to be ignored. REFRESH bkpf_int. SELECT bukrs belnr gjahr blart budat FROM bkpf INTO TABLE bkpf_int WHERE bukrs = p_bukrs AND budat = bkpf_wa-budat. REFRESH bkpf_int. GET RUN TIME FIELD start. SELECT bukrs belnr gjahr blart budat FROM bkpf INTO TABLE bkpf_int WHERE bukrs = p_bukrs AND budat = bkpf_wa-budat. GET RUN TIME FIELD end. dif = end - start. WRITE: /001 'Time for fourth (partially indexed) select', 067 ':', dif, 'microseconds'.ENDFORM. " unindexed_select_bkpf_2*&---------------------------------------------------------------------**& Form indexed_select_bkpf_2*&---------------------------------------------------------------------** text*----------------------------------------------------------------------*FORM indexed_select_bkpf_2.* Finally, we will use the domain values of BSTAT in the select. If you* are sure that you know all of the values, you can hardcode them. I am* using all of the possible values of BSTAT so that all three selects* return the same data. In practice, we would probably narrow it down.* But since normal FI postings have BSTAT = SPACE, this won't help* unless we are looking for 'not' normal documents. RANGES: r_bstat FOR bkpf-bstat. DATA : d1 LIKE dd07l-domvalue_l, d2 LIKE dd07l-domvalue_h.* Hardcoded values* Preliminary select. REFRESH bkpf_int. SELECT bukrs belnr gjahr blart budat FROM bkpf INTO TABLE bkpf_int WHERE bukrs EQ p_bukrs AND bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z') AND budat = bkpf_wa-budat. REFRESH bkpf_int. GET RUN TIME FIELD start.* Use this select in comparisons SELECT bukrs belnr gjahr blart budat FROM bkpf INTO TABLE bkpf_int WHERE bukrs EQ p_bukrs AND bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z') AND budat = bkpf_wa-budat. GET RUN TIME FIELD end. dif = end - start. WRITE: /001 'Time for fifth select', '(indexed by hardcoding the domain values)', 067 ':', dif, 'microseconds'.* After an upgrade, the values in a domain may change. It's safer to* retrieve all of the values from the data dictionary. There is a very* slight increase in the time. r_bstat-sign = 'I'. SELECT domvalue_l domvalue_h FROM dd07l INTO (d1, d2) WHERE domname = 'BSTAT' AND as4local = 'A'. IF d2 IS INITIAL. r_bstat-option = 'EQ'. r_bstat-low = d1. CLEAR r_bstat-high. ELSE. r_bstat-option = 'BT'. r_bstat-low = d1. r_bstat-high = d2. ENDIF. APPEND r_bstat. ENDSELECT. REFRESH bkpf_int. SELECT bukrs belnr gjahr blart budat FROM bkpf INTO TABLE bkpf_int WHERE bukrs EQ p_bukrs AND bstat IN r_bstat AND budat = bkpf_wa-budat. REFRESH: bkpf_int, r_bstat.* Use this select in comparisons. GET RUN TIME FIELD start. r_bstat-sign = 'I'. SELECT domvalue_l domvalue_h FROM dd07l INTO (d1, d2) WHERE domname = 'BSTAT' AND as4local = 'A'. IF d2 IS INITIAL. r_bstat-option = 'EQ'. r_bstat-low = d1. CLEAR r_bstat-high. ELSE. r_bstat-option = 'BT'. r_bstat-low = d1. r_bstat-high = d2. ENDIF. APPEND r_bstat. ENDSELECT. REFRESH bkpf_int. SELECT bukrs belnr gjahr blart budat FROM bkpf INTO TABLE bkpf_int WHERE bukrs EQ p_bukrs AND bstat IN r_bstat AND budat = bkpf_wa-budat. GET RUN TIME FIELD end. dif = end - start. WRITE: /001 'Time for sixth select', '(indexed by selecting the domain values)', 067 ':', dif, 'microseconds'.ENDFORM. " indexed_select_bkpf_2

I ran the above code in QA instances with a DB2 environment in both 4.6C and 4.7. There are more indexes on BKPF in 4.7, but I tried to use one that is in both versions. I also ran a similar program in Oracle with comparable results. But I really don’t know if it will work with other databases – please let me know!

I ran this many times in both active and quiet systems. Here are some typical results:

Time for first (fully qualified) select : 148 microseconds Time for second (unindexed) select : 1,873,906 microseconds Time for third select (indexed by selecting from the check table) : 455 microseconds Time for fourth (partially indexed) select : 816,253 microseconds Time for fifth select (indexed by hardcoding the domain values) : 43,259 microseconds Time for sixth select (indexed by selecting the domain values) : 43,332 microseconds

Some things to note:

In the above times, the first select shows what happens in the ideal world. We are comparing select 2 against select 3 and select 4 against selects 5 and 6. But selects 2 and 3 should return the same results as should selects 4, 5 and 6.

But the point is that even though start out knowing nothing about (and presumably not caring about) the company code in selects 2 and 3 and the document status in selects 4, 5 and 6, if you put all possible values of these fields into the select statement, the results are dramatic.

If you try to combine both tricks, you will probably find that they don’t work very well together. Once seems to be enough.

Performance of Nested Loops

Posted by Rob Burbank Feb 7, 2006

This BLOG is an expanded version of a post that I made to the ABAP forum a while ago. What I wanted to show was that the effect that when performance tuning, the effect of nested loops can be far worse than poorly constructed SELECT statements.

I wrote a small program which illustrates this point. Ive posted the code below, but in a nutshell, what it does is select a number of FI document headers and line items from BKPF and BSEG. The select statement from BSEG is very inefficient. I did it that way to prove a point. It then reads all records from both tables using a nested loop. Then it reads all records from both tables using a much more efficient method. Finally, it reads all records from both tables using a different method which may be even more efficient, but may be somewhat more difficult to program. It keeps track of the number of records and time taken for each operation.

I ran the program twice in our QA environment  once selecting a small amount of data and again selecting a moderate amount. The outputs are:

First run -

Time for unindexed select : 00:06:09
 Number of BKPF entries: 5,863
 Number of BSEG entries: 17,683

Time for nested loop : 00:00:53
 Number of BKPF reads : 5,863
 Number of BSEG reads : 17,683

Time for indexed loop : 00:00:00
 Number of BKPF reads : 5,863
 Number of BSEG reads : 17,683

Time for parallel cursor : 00:00:00
 Number of BKPF reads : 5,863
 Number of BSEG reads : 17,683

Second run 

Time for unindexed select : 00:21:07
 Number of BKPF entries: 55,777
 Number of BSEG entries: 205,285

Time for nested loop : 02:16:21
 Number of BKPF reads : 55,777
 Number of BSEG reads : 205,285

Time for indexed loop : 00:00:01
 Number of BKPF reads : 55,777
 Number of BSEG reads : 205,285

Time for parallel cursor : 00:00:01
 Number of BKPF reads : 55,777
 Number of BSEG reads : 205,285

So what can we conclude? In the first case, a gain in time of almost a minute is not much, but in a dialogue program, it would be worthwhile. But in the second case the other method gains over two hours. This would allow a program that has to run in the background to run in the foreground. The most striking thing to me though, is the fact that the nested loop takes substantially longer than an extremely inefficient select statement.

In both cases, the loop using the parallel cursor method did not produce a substantial savings over the loop using a binary search followed by an indexed read; however, if you run this and extract a very large amount of data, it does run more quickly. In the program I have provided, the parallel cursor method does appear to be somewhat easier to program, but I have found that if the outer loop does not contain all of the records in the inner loop, then programming complexity is increased and I don't think it warrants the extra effort. The binary search method is easy and runs quickly.

The select screen for the program is quite standard. The amount of data returned is determined by whatever the user enters and the programmer really has no control over it. So Im suggesting that if you cant guarantee that the amount of data is small, then you really ought to use the indexed read method.

Also note that the first select statement returns 17,683 rows from BSEG and took 06:09 to run and the second one returns 205,285 rows and takes 21:07. The second one retrieves almost 11.5 times as much data but takes only about 3.5 times as long to execute. The two runs of the program were on separate evenings when there shouldnt be any load, so buffering and workload shouldnt be issues.

So, my conclusion is: when tuning a program that you know will return a small amount of data, tune the select statement and dont worry too much about loops; however, if the program may return a large amount of data, avoid nested loops.

And a final thought: once you get a program tuned to a certain point, it doesn't make a lot of sense to try to spend a lot of time trying to reduce execution time by a small amount. (More on this later)

Code follows:

report ztest_nested_loop. data: bkpf type bkpf, bseg type bseg. select-options: s_bukrs for bseg-bukrs memory id buk obligatory, s_gjahr for bseg-gjahr memory id gjr obligatory, s_lifnr for bseg-lifnr memory id lif obligatory. data: bkpf_tab type standard table of bkpf, bkpf_lin like line of bkpf_tab, bseg_tab type standard table of bseg, bseg_lin like line of bseg_tab. data: start_time type sy-uzeit, end_time type sy-uzeit, difference type sy-uzeit, bkpf_entries type sy-tabix, bseg_entries type sy-tabix, bkpf_reads type sy-tabix, bseg_reads type sy-tabix. start-of-selection. perform unindexed_select. perform nested_loop. perform indexed_loop. PERFORM parallel_cursor. *&---------------------------------------------------------------------* *& Form unindexed_select *&---------------------------------------------------------------------* form unindexed_select. get time field start_time. select * from bseg into table bseg_tab where bukrs in s_bukrs and gjahr in s_gjahr and lifnr in s_lifnr. if sy-subrc <> 0. message id '00' type 'E' number '001' with 'No entries selected'. endif. select * from bkpf into table bkpf_tab for all entries in bseg_tab where bukrs = bseg_tab-bukrs and belnr = bseg_tab-belnr and gjahr = bseg_tab-gjahr and bstat = space. clear bseg_tab. refresh bseg_tab. select * from bseg into table bseg_tab for all entries in bkpf_tab where bukrs = bkpf_tab-bukrs and belnr = bkpf_tab-belnr and gjahr = bkpf_tab-gjahr. get time field end_time. difference = end_time - start_time. describe table bkpf_tab lines bkpf_entries. describe table bseg_tab lines bseg_entries. write: /001 'Time for unindexed select:', difference, /005 'Number of BKPF entries:', bkpf_entries, /005 'Number of BSEG entries:', bseg_entries. skip 1. endform. " unindexed_select *&---------------------------------------------------------------------* *& Form nested_loop *&---------------------------------------------------------------------* form nested_loop. get time field start_time. loop at bkpf_tab into bkpf_lin. bkpf_reads = bkpf_reads + 1. loop at bseg_tab into bseg_lin where bukrs = bkpf_lin-bukrs and belnr = bkpf_lin-belnr and gjahr = bkpf_lin-gjahr. bseg_reads = bseg_reads + 1. endloop. endloop. get time field end_time. difference = end_time - start_time. write: /001 'Time for nested loop:', difference, /005 'Number of BKPF reads:', bkpf_reads, /005 'Number of BSEG reads:', bseg_reads. skip 1. endform. " nested_loop *&---------------------------------------------------------------------* *& Form indexed_loop *&---------------------------------------------------------------------* form indexed_loop. data: bkpf_index like sy-tabix, bseg_index like sy-tabix. clear: bkpf_reads, bseg_reads. get time field start_time. sort: bkpf_tab by bukrs belnr gjahr, bseg_tab by bukrs belnr gjahr. loop at bkpf_tab into bkpf_lin. read table bseg_tab into bseg_lin with key bukrs = bkpf_lin-bukrs belnr = bkpf_lin-belnr gjahr = bkpf_lin-gjahr binary search. bkpf_reads = bkpf_reads + 1. bseg_index = sy-tabix. while sy-subrc = 0. bseg_index = bseg_index + 1. bseg_reads = bseg_reads + 1. read table bseg_tab into bseg_lin index bseg_index. if bseg_lin-bukrs <> bkpf_lin-bukrs or bseg_lin-belnr <> bkpf_lin-belnr or bseg_lin-gjahr <> bkpf_lin-gjahr. sy-subrc = 99. else. endif. endwhile. endloop. get time field end_time. difference = end_time - start_time. write: /001 'Time for indexed loop:', difference, /005 'Number of BKPF reads:', bkpf_reads, /005 'Number of BSEG reads:', bseg_reads. skip 1. endform. " indexed_loop *&---------------------------------------------------------------------* *& Form parallel_cursor *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* FORM parallel_cursor. DATA: bkpf_index LIKE sy-tabix, bseg_index LIKE sy-tabix. CLEAR: bkpf_reads, bseg_reads. GET TIME FIELD start_time. SORT: bkpf_tab BY bukrs belnr gjahr, bseg_tab BY bukrs belnr gjahr. bseg_index = 1. LOOP AT bkpf_tab INTO bkpf_lin. bkpf_reads = bkpf_reads + 1. LOOP AT bseg_tab INTO bseg_lin FROM bseg_index. IF bseg_lin-bukrs <> bkpf_lin-bukrs OR bseg_lin-belnr <> bkpf_lin-belnr OR bseg_lin-gjahr <> bkpf_lin-gjahr. bseg_index = sy-tabix. EXIT. ELSE. bseg_reads = bseg_reads + 1. ENDIF. ENDLOOP. ENDLOOP. GET TIME FIELD end_time. difference = end_time - start_time. WRITE: /001 'Time for parallel cursor :', difference, /005 'Number of BKPF reads :', bkpf_reads, /005 'Number of BSEG reads :', bseg_reads. ENDFORM. " parallel_cursor

More information can be found in SE30 (Tips and Tricks)

ABAP Testing and Troubleshooting

Quickly Retrieving FI document Data from BSEG

JOINS vs. FOR ALL ENTRIES - Which Performs Better?

Performance - what will kill you and what will leave you with only a flesh wound

So - What are the Results

Conclusions

Using an Index When You Don't Have all of the Fields

Performance of Nested Loops

Filter Blog