topic Re: Performance issue on SELECT COUNT in Application Development and Automation Discussions

Performance issue on SELECT COUNT

Former Member — Sun, 08 Apr 2012 07:49:00 GMT

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

Re: Performance issue on SELECT COUNT

Former Member — Sun, 08 Apr 2012 08:46:54 GMT

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.

Re: Performance issue on SELECT COUNT

Former Member — Sun, 08 Apr 2012 11:52:20 GMT

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 😃

Re: Performance issue on SELECT COUNT

Former Member — Wed, 11 Apr 2012 08:50:14 GMT

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!

Re: Performance issue on SELECT COUNT

Sandra_Rossi — Sun, 26 Sep 2021 04:15:24 GMT

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.