topic Performance issue for select statement in Application Development and Automation Discussions

Performance issue for select statement

Former Member — Wed, 01 Aug 2012 21:17:53 GMT

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.

Re: Performance issue for select statement

Former Member — Thu, 02 Aug 2012 02:17:04 GMT

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

Re: Performance issue for select statement

Former Member — Thu, 02 Aug 2012 09:59:59 GMT

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.

Re: Performance issue for select statement

Former Member — Sat, 04 Aug 2012 08:02:07 GMT

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

Re: Performance issue for select statement

Former Member — Sat, 04 Aug 2012 19:04:24 GMT

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.

Re: Performance issue for select statement

RaymondGiuseppi — Mon, 06 Aug 2012 08:39:01 GMT

First test your report with SQL Trace (ST05)

Regards,

Raymond

Re: Performance issue for select statement

Former Member — Tue, 21 Aug 2012 13:34:43 GMT

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.