Skip to Content
author's profile photo Former Member
Former Member

Performance issue for select statement

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

6 Answers

  • Posted on Aug 06, 2012 at 08:39 AM

    First test your report with SQL Trace (ST05)

    Regards,

    Raymond

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2012 at 02:17 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2012 at 09:59 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 04, 2012 at 08:02 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 04, 2012 at 07:04 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 21, 2012 at 01:34 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.