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

No comments:

Post a Comment