The Best way to find SUM of records

In this post I would like to show which is the fastest way to find sum for a number of records.
The three possible ways to calculate a sum of records are:
  1. Using COLLECT Statement.
  2. Using SUM statement within Control-break statement AT-ENDAT.
  3. Using a variable within a parallel cursor.
The conclusion is that the second option always takes less time compared to other three.The following graph explains it:



The following code is used to calculate it.Please test it yourself and give me a feedback.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
PROGRAM ztest_sum.
 
TYPES:
BEGIN OF x_sbook,
  carrid     TYPE s_carr_id,
  connid     TYPE s_conn_id,
  fldate     TYPE s_date,
  luggweight TYPE s_lugweigh,
  wunit      TYPE s_weiunit,
END OF x_sbook.
 
DATA: i_sbook TYPE STANDARD TABLE OF x_sbook INITIAL SIZE 0,
      i_final1 TYPE STANDARD TABLE OF x_sbook INITIAL SIZE 0,
      i_final2 TYPE STANDARD TABLE OF x_sbook INITIAL SIZE 0,
      i_final3 TYPE STANDARD TABLE OF x_sbook INITIAL SIZE 0,
      wa_sbook TYPE x_sbook,
      rtime1 TYPE i,
      rtime2 TYPE i,
      rtime3 TYPE i.
 
PARAMETERS: p_rec TYPE i. " No. of records
 
SELECT carrid
       connid
       fldate
       luggweight
       wunit
  FROM sbook
  UP TO p_rec ROWS
  INTO TABLE i_sbook.
 
CHECK sy-subrc = 0.
APPEND LINES OF i_sbook TO i_sbook.
 
SORT i_sbook BY carrid connid fldate.
 
PERFORM collect.
 
PERFORM at_end_of_sum.
 
PERFORM sum_by_variable.
 
END-OF-SELECTION.
 
  PERFORM display.
 
 
*&---------------------------------------------------------------------*
*&      Form  collect
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM collect .
  DATA: time1 TYPE i,
        time2 TYPE i.
 
  GET RUN TIME FIELD time1.
 
  LOOP AT i_sbook INTO wa_sbook.
    COLLECT wa_sbook INTO i_final1.
  ENDLOOP.
 
  GET RUN TIME FIELD time2.
 
  rtime1  = time2 - time1.
ENDFORM.                    " collect
*&---------------------------------------------------------------------*
*&      Form  at_end_of_sum
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM at_end_of_sum .
  DATA: time1 TYPE i,
        time2 TYPE i.
 
  GET RUN TIME FIELD time1.
  LOOP AT i_sbook INTO wa_sbook.
    AT END OF fldate.
      SUM.
      APPEND wa_sbook TO i_final2.
    ENDAT.
  ENDLOOP.
 
  GET RUN TIME FIELD time2.
 
  rtime2 = time2 - time1.
ENDFORM.                    " at_end_of_sum
*&---------------------------------------------------------------------*
*&      Form  sum_by_variable
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM sum_by_variable .
  DATA: time1    TYPE i,
        time2    TYPE i,
        i_temp   TYPE STANDARD TABLE OF x_sbook INITIAL SIZE 0,
        l_index  TYPE i,
        wa_temp  TYPE x_sbook,
        l_weight TYPE s_lugweigh,
        wa_final TYPE x_sbook.
 
  GET RUN TIME FIELD time1.
 
  i_temp = i_sbook.
 
  DELETE ADJACENT DUPLICATES FROM i_temp
   COMPARING carrid connid fldate.
 
  LOOP AT i_temp INTO wa_temp.
    READ TABLE i_sbook
    TRANSPORTING NO FIELDS
    WITH KEY carrid = wa_temp-carrid
             connid = wa_temp-connid
             fldate = wa_temp-fldate
             BINARY SEARCH.
    IF sy-subrc = 0.
      l_index = sy-tabix.
      LOOP AT i_sbook INTO wa_sbook FROM l_index.
        IF wa_sbook-carrid <> wa_temp-carrid OR
           wa_sbook-connid <> wa_temp-connid OR
           wa_sbook-fldate <> wa_temp-fldate.
          EXIT.
        ENDIF.
        l_weight = wa_sbook-luggweight + l_weight.
      ENDLOOP.
 
      wa_final = wa_sbook.
      wa_final-luggweight = l_weight.
      APPEND wa_sbook TO i_final3.
      CLEAR l_weight.
    ENDIF.
  ENDLOOP.
 
  GET RUN TIME FIELD time2.
  rtime3 = time2 - time1.
ENDFORM.                    " sum_by_variable
*&---------------------------------------------------------------------*
*&      Form  display
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM display .
  DATA: wa_final TYPE x_sbook.
 
  DATA: l_lines TYPE i.
 
  l_lines = LINES( i_final1 ).
  ULINE.
  FORMAT COLOR 2 ON.
  WRITE: /5 'Using COLLECT Statement',':', l_lines.
  WRITE: /5 'Run Time:',rtime1,'microseconds'.
  FORMAT COLOR 2 OFF.
  ULINE.
  l_lines = LINES( i_final2 ).
  FORMAT COLOR 1 ON.
  WRITE: /5 'Using Control Break statement AT-END-OF, SUM', ':',
          l_lines.
  WRITE: /5 'Run Time:',rtime2,'microseconds'.
  FORMAT COLOR 1 OFF.
  ULINE.
  l_lines = LINES( i_final3 ).
  FORMAT COLOR 4 ON.
  WRITE: /5 'Using Parallel Cursor',':', l_lines.
  WRITE: /5 'Run Time:',rtime3,'microseconds'.
  FORMAT COLOR 4 OFF.
  ULINE.
ENDFORM.                    " display

1 comment:

Subscribe to: Post Comments (Atom)