A real case to use REDUCE to finish a task in daily work
Have you ever used REDUCE keyword to resolve a real task in your daily work? For me not till today.
There is a table CRM_JSTO which stores the object guid together with the status object name it uses.
I am asked by my colleagues to generate a statistics about how many records exist for the combination of each object type and its status profile. The result should be sorted by occurrence number in the DESCENDING order.
In my system the table CRM_JSTO has 462325 entries.
Here is a peek of my result, which shows for example object type COH ( CRM Order Header ) with status profile CRMACTIV is used 30489 times.
As first attempt I get the result via the following report using REDUCE approach:
REPORT zreduce1.
DATA: lt_status TYPE TABLE OF crm_jsto.
SELECT * INTO TABLE lt_status FROM crm_jsto.
DATA(lo_tool) = NEW zcl_status_calc_tool( ).
lo_tool = REDUCE #( INIT o = lo_tool
local_item = VALUE zcl_status_calc_tool=>ty_status_result( )
FOR GROUPS <group_key> OF <wa> IN lt_status
GROUP BY ( obtyp = <wa>-obtyp stsma = <wa>-stsma )
ASCENDING NEXT local_item = VALUE #( obtyp = <group_key>-obtyp
stsma = <group_key>-stsma
count = REDUCE i( INIT sum = 0 FOR m IN GROUP <group_key>
NEXT sum = sum + 1 ) )
o = o->add_result( local_item ) ).
DATA(ls_result) = lo_tool->get_result( ).Source code of ZCL_STATUS_CALC_TOOL could be found from my github.
After this implementation has finished I realized that it is actually not necessary to calculate the total number of each group.
As a result I have another solution: the count of each group is directly calculated by kernel via key word GROUP SIZE.
Finally I use the following report to compare the performance of both solutions:
report z.
DATA: lt_status TYPE zcl_status_calc_tool=>tt_raw_input.
SELECT * INTO TABLE lt_status FROM crm_jsto.
DATA(lo_tool) = NEW zcl_status_calc_tool( ).
zcl_abap_benchmark_tool=>start_timer( ).
DATA(lt_result1) = lo_tool->get_result_traditional_way( lt_status ).
zcl_abap_benchmark_tool=>stop_timer( ).
zcl_abap_benchmark_tool=>start_timer( ).
lo_tool = REDUCE #( INIT o = lo_tool
local_item = VALUE zcl_status_calc_tool=>ty_status_result( )
FOR GROUPS <group_key> OF <wa> IN lt_status
GROUP BY ( obtyp = <wa>-obtyp stsma = <wa>-stsma )
ASCENDING NEXT local_item = VALUE #( obtyp = <group_key>-obtyp
stsma = <group_key>-stsma
count = REDUCE i( INIT sum = 0 FOR m IN GROUP <group_key>
NEXT sum = sum + 1 ) )
o = o->add_result( local_item ) ).
DATA(lt_result2) = lo_tool->get_result( ).
zcl_abap_benchmark_tool=>stop_timer( ).
ASSERT lt_result1 = lt_result2.The comparison result shows the second solution ( LOOP AT GROUP ) is much more efficient than the REDUCE version, which makes senses since it avoid the group size calculation in ABAP layer.
So... in the end of the day,you still have not used REDUCE to resolve a real task on your daily work 🙂
Still, nice post, as always!
Hello Custodio,
yes since this is an internal task so I have freedom to use any approach which can finish the work. My colleague only cares about the accuracy of statistics result but is completely not interested what code I am written to get the result and the performance 🙂 I am glad I can have chance to practice REDUCE key word in my real work 🙂
Best regards,
Jerry
Hi Jerry,
I don’t work with cutting edge technologies like you, but atleast i beat you in using REDUCE operator
In FI Module it is common to calculate the net total of the Debit/Credit items in an FI document. A nice use case to use REDUCE
Tbh, i use the (internal)table operators FOR & REDUCE in productive coding. But for grouping internal table records i do not prefer the FOR…GROUP BY operator.
Btw, congrats for the “Mentor” lemon
Well deserved
BR, Suhas
Hello Suhas,
Very nice to see a real case resolved by REDUCE,  thanks a lot for your sharing 🙂
Best regards,
Jerry
And how much slower is this SELECT everything + LOOP than a proper SELECT ... GROUP BY?
Why would you not just use
SELECT OBTYP, STSMA, COUNT(*) as OCCURANCES
FROM CRM_JSTO
GROUP BY OBTYP, STSMA
ORDER BY COUNT(*) DESC
INTO TABLE DATA(LT_RESULTS).
Above code may not be quite the correct syntax, but you get my drift hopefully. This seems a heck of a lot simpler and far easier to read.
Now taking your example further, can you show how you would identify the single value result of the total number of result rows (OBJTY, STSMA combinations), with a count > 10,000, which from your sample results set above, should be a result of 10 rows?