SELECT performance with LIKE Statement

[prajeshdesai]

Recently i faced performance issue with CHVW table. It takes more than 25 min, but i made some below changes, and now it is taking 2-3 min.

So i need to share this awerness with you all. This is not specifically about CHVW table, you can use below solution whenever applicable.

ISSUE QUERY

DATA: R_RANGE TYPE RANGE OF MATNR.

"i need to get data from CHVW using R_RANGE

SELECT *

     FROM CHVW

     INTO TABLE IST_TAB1

     FOR ALL ENTRIES IN IST_TAB2

     WHERE MATNR IN R_RANGE

     AND  CHARG =  IST_TAB2-CHARG

     AND  WERKS =  IST_TAB2-WERKS.

Here, R_RANGE contains value with *. (ex. abc*, xyz* etc.)

So if we pass R_RANGE with IN oprator in WHERE calues of SELECT statement, it will generate SQL statement with LIKE oprator and here because of FOR ALL ENTRIES IN addition, it will generate n no. of query and execute them one by one on database.

CHANGES I MADE

SELECT MATNR

     FROM MARA

     INTO TABLE IST_MATNR

     WHERE MATNR IN R_RANGE.

  CLEAR R_RANGE.

  LOOP AT IST_MATNR INTO WA_MATNR.              "Convert internal table to range table

     WA_R_MATNR-SIGN    = 'I'.

     WA_R_MATNR-OPTION  = 'EQ'.

     WA_R_MATNR-LOW = WA_MATNR-MATNR.

     APPEND WA_R_MATNR TO R_RANGE.

   ENDLOOP.

i added above code before my query.

What i done is, i fatched all MATNR from MARA table. so now i'm having MATNR without * (ex. abcd, abce, xyza, xyzb etc.). Then i created range for the same and performance improved.

Hope ths helps.