Skip to Content

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_TAB2CHARG

     AND  WERKS IST_TAB2WERKS.

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_MATNRSIGN    = ‘I’.

     WA_R_MATNROPTION  = ‘EQ.

     WA_R_MATNRLOW = WA_MATNRMATNR.

     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.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Shai Sinai

    Hi Prajesh,

    Thanks for sharing your experience.

    However, your solution doesn’t make too much sense.

    Have you analyzed your DB statement explain?

    Is index CHVW-ARC active in your system?

    (0) 
    1. Prajesh Desai Post author

      Yes it is already active, i am passing value without pattern (ex. abcd, xyza) in my SELECT statement. I think if we pass values with patterns (ex abc*, xyz*) with FAE it becomes slow.

      (0) 
  2. Thomas Zloch

    Option should be EQ in this case and not CP.

    You can run into problems once your range contains several thousand single entries, then the generated SQL statement becomes too large, ending in a short dump.

    Better enhance FAE driver table IST_TAB2 to also contain MATNR in addition to CHARG and WERKS values, or still better go back and see if you can replace FAE by JOIN select (depends on how IST_TAB2 is being filled, we don’t know this from your post).

    Thomas

    (0) 
  3. Suhas Saha

    Have you analyzed your DB statement explain?

    Without ST05 trace analysis, the approach seems like an arrow shot in the dark! 😐

    (0) 

Leave a Reply