Performance issue for select statement

[Former Member]

Hi Friends,

I created custom index 004 on GLPCA table for fields RYEAR, POPER, RBUKRS, RACCT, BUDAT, KOSTL, AUFNR and LIFNR.  But, when I run my report, my select statement which has GLPCA fields is running for ever.  My doubt is that I really appreciate if someone could point me in right direction.

Below is my select statement:

*Use dirty trick to mislead DB-optimizer
  CALL FUNCTION 'DB_DO_NOT_USE_CLIENT_INDEX'
    EXPORTING
      VALUE    = SY-MANDT
    TABLES
      MANDTTAB = R_RCLNT.

  SELECT RLDNR RRCTY RVERS RBUKRS RPRCTR
         KOKRS RACCT ACTIV HSL BLART FROM GLPCA
                                     CLIENT SPECIFIED
                                     INTO TABLE GT_FINAL
                                     WHERE RCLNT  IN R_RCLNT            
                                       AND RRCTY  IN S_RRCTY                                                        AND RVERS  EQ P_RVERS                                                        AND RYEAR  EQ P_RYEAR                                                        AND POPER  IN S_POPER           
                                       AND RBUKRS EQ P_RBUKRS          
                                       AND RPRCTR IN S_RPRCTR                                                       AND KOKRS  EQ P_KOKRS           
                                       AND RACCT  IN S_RACCT.          

IF SY-SUBRC = 0.

    SORT GT_FINAL BY RACCT RPRCTR BLART ACTIV HSL ASCENDING.

    LOOP AT GT_FINAL INTO WA_FINAL.

      COLLECT WA_FINAL INTO GT_FINAL1.

    ENDLOOP.

    DELETE GT_FINAL1 WHERE HSL EQ 0.
ENDIF.

Thanks,

Nani.

[Former Member]

Hi nani G,

I am not sure about the "dirty trick" function. Seems to be outdated and of little use (try it without?). May I suggest you run your report through SE30 and see if it is really database access that is causing the long runs. Also, look at your conditions (you have alot) of the select and make sure there are no redundancies or conditions that are not needed to get you your results. Finally, consider using a hash table if there are ALOT of entries. However you will be limited with what you can do with the hash table.

Regards,

sifter

[Former Member]

Hi ,

Try using field symbols instead of work area and also update the table using field symbols.

LOOP AT GT_FINAL ASSIGNING <FS_FINAL>

MOVE <FS_FINAL> INTO GT_FINAL2.

This increases the performance.

[Former Member]

Hi

     nani

     I think your select statement is not using the index you have created.

     Use only those fields in select statement which is present in your index. and make sure that the sequence must be same as that in index.

Regards,

          Aaradhana

[Former Member]

Hi Nani,

Have you checked the Index used by System..??

If it is not the desired one, could you please try with DBA Hint....!!!!!

Hope it helps.

Thanking You All.

[RaymondGiuseppi]

First test your report with  SQL Trace (ST05)

Regards,

Raymond

[Former Member]

hi Nani,

Firstly CHECK your program in TCODE : ST12

1. Click on CURRENT MODE

2. Give your ZTCODE

3. Execute

look at the output.

SECONDLY....

CHECK if your custom index is ACTIVE?

Thank you,

kata.