Can I use FOR ALL ENTRIES with GROUP BY?

Question

Currently the code looks something like this:

LOOP AT lt_orders ASSIGNING <fs_order>.
  SELECT COUNT(*) AS cnt
      FROM order_items
      INTO <fs_order>-cnt
      WHERE order_id = <fs_order>-order_id.
ENDLOOP.

It is the slowest part of the report. I want to speed it up.

How can I use FOR ALL ENTRIES with GROUP BY?

Answer 1

Not directly, only through a CDS view

While all of the answers provide a faster solution than the one in the question, the fastest way is not mentioned.

If you have at least Netweaver 7.4, EHP 5 (and you should, it was released in 2014), you can use CDS views, even if you are not on HANA.

It still cannot be done directly, as OpenSQL does not allow FOR ALL ENTRIES with GROUP BY, and CDS views cannot handle FOR ALL ENTRIES. However, you can create one of each.

CDS:

    @AbapCatalog.sqlViewName: 'zorder_i_fae'
    DEFINE VIEW zorder_items_fae AS SELECT FROM order_items {
      order_id,
      count( * ) AS cnt, 
    }
    GROUP BY order_id

OpenSQL:

    SELECT * 
        FROM zorder_items_fae
        INTO TABLE @DATA(lt_order_cnt)
        FOR ALL ENTRIES IN @lt_orders
        WHERE order_id = @lt_orders-order_id.

Speed

If lt_orders contains more than about 30% of all possible order_id values from table ORDER_ITEMS, the answer from iPirat is faster. (While using more memory, obviously)

However, if you need only a couple hunderd order_id values out of millions, this solution is about 10 times faster than any other answer, and 100 times faster than the original.