Performance issue on SELECT COUNT

[Former Member]

Hi,

I have a SELECT statement inside a loop which is not ideal, I want to improve performance for it, any suggestion?

LOOP AT I_RFQ_HEADER INTO W_RFQ_HEADER.
     SELECT COUNT ( * ) 
          FROM EKPO
          INTO W_RFQ_HEADER-QITEM.
          WHERE EBELN = W_RFQ_LINEITEM-EBELN.
      .... mode codes here
ENDLOOP.

Thanks.

Regards,

Marlson

[Former Member]

Hi Marlson,

Pls try below logic.

   SELECT ebeln ebelp
  FROM ekpo
  INTO TABLE i_ekpo
  FOR ALL ENTRIES IN i_rfq_header
  WHERE ebeln = i_rfq_header-ebeln.
IF sy-subrc = 0.
  SORT i_ekpo BY ebeln ebelp.
ENDIF.

LOOP AT i_rfq_header INTO w_rfq_header.
  READ TABLE i_ekpo TRANSPORTING NO FIELDS WITH KEY ebeln = w_rfq_header-ebeln BINARY SEARCH.
  IF sy-subrc = 0.
    v_tabix = sy-tabix.
    CLEAR v_count.
    LOOP AT i_ekpo INTO wa_ekpo FROM v_tabix..
      IF wa_ekpo-ebeln = w_rfq_header-ebeln.
        v_count = v-count + 1.
      ELSE.
        w_rfq_header-qitem = v_count.
        EXIT.
      ENDIF.
    ENDLOOP.
  ENDIF.
ENDLOOP.

[Former Member]

Hi Nare,

I have try your method, however I found a problem in your code is that the counter for the last item in the internal table (i_ekpo) will never get transferred. The reason is  that when it reached the last record, it had already exit the loop so w_rfq_header-qitem will never get the value of v_count.

I have rectified by modifying a bit of your code:

DESCRIBE TABLE i_ekpo LINES lv_lines.

LOOP AT i_ekpo INTO wa_ekpo FROM lv_tabix.

         IF wa_ekpo-ebeln = wa_rfq_header-ebeln.

           ADD 1 TO lv_count.

           IF lv_lines = sy-tabix. "Check whether it as reach the last index.

             MOVE lv_count TO wa_rfq_header-qitem.

           ENDIF.

         ELSE.

           MOVE lv_count TO gs_rfq_header-qitem.

           EXIT.

         ENDIF.

ENDLOOP.

Great thanks, points had been rewarded 😃

[Former Member]

your solution is the best solution, if you are really interested in the count number and not in an existence check.

The recommendation 'Avoid SELECT in LOOP' is not in general correct, there are exceptions.

It is definitely not a good a Idea to transfer 1000+ records to the application server, if you are only interested in the COUNT(*). So the above recommendation is worse than your solution.

However, I guess that you are not interested in the COUNT(*), but only in the existence check, i.e. COUNT(*) > 0 or not. Then you should change the COUNT(*) into a SELECT ... UP TO 1 ROWS, which is the recommended existence check. In this case there are probably only a few records transferred to the app-server, that is why the recommended solution does help. However, I would still not support it, in this case it is probably possible to streamline the logic even more. It is not necessary to store the result of the existence check, but you can use it directly.

.... better test recommendations before you give points!

[Sandra_Rossi]

I know it's oooold. Just saw your remark "The recommendation 'Avoid SELECT in LOOP' is not in general correct". I guess the "not" should not be there.