There was discussion going on here regarding use of aggregate functions in abap Using Select Aggregate Statements so i thought of writing a simple demo program, there are different aggregate function we have in abap

 Average : AVG

 Minimum : MIN

 Maximum : MAX

 Total : SUM

 Count : COUNT

Right now i will just discuss about SUM and later on i will update this blog with some other aggregate functions as well, below is the demo program I wrote

At first i ran the program with aggregate function code and then I ran the program without aggregate function code.

TYPEs : BEGIN OF ty_ekpo,

         ebeln TYPE ebeln,

         netwr TYPE bwert,

         END OF ty_ekpo.

DATA: lv_avgnetwr TYPE bwert,

       i_ekpo      TYPE TABLE OF ty_ekpo,

       lv_count    TYPE i.

FIELD-SYMBOLS : <fs> TYPE ty_ekpo.

PARAMETERS: p_ebeln TYPE ebeln.

“Using aggregate function

SELECT AVG( netwr ) FROM ekpo

INTO lv_avgnetwr

WHERE ebeln EQ p_ebeln.

IF sysubrc EQ 0.

   WRITE: /‘Average value of Net-value is :’, lv_avgnetwr.

ENDIF.

START-OF-SELECTION.

“without aggregate function

   SELECT ebeln netwr INTO TABLE i_ekpo FROM ekpo

   WHERE ebeln = p_ebeln.

   IF i_ekpo[] IS NOT INITIAL.

     SORT i_ekpo by ebeln.

     lv_count = 1.

     LOOP AT i_ekpo ASSIGNING <fs>.

       lv_avgnetwr = lv_avgnetwr + <fs>netwr.

       AT END OF ebeln.

         lv_avgnetwr = lv_avgnetwr / lv_count.

         WRITE: /‘Average value of Net-value is :’, lv_avgnetwr.

       ENDAT.

       lv_count = lv_count + 1.

     ENDLOOP.

   ENDIF.


and then i checked ST05 for the performance

Without aggregate

Without aggregate.png

   With aggregate there is difference in the duration at this stage i can say that aggregate function does effect performance.


With Aggregate.png

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Uwe Fetzer

    Although your result leads to the right direction, the numbers are in the statistically tolerance. You can’t proof it with just a couple of DB entries because the administrative overhead (OPEN) is by far higher than the fetch itself.

    Try to enlarge the SQL result set to 1000s or millions of values (table MSEG for example).

    (0) 

Leave a Reply