Skip to Content

problem with select query

SELECT MATNR
              MEINS
              FERTH
         FROM MARA
          INTO TABLE GT_CMATNR
          FOR ALL ENTRIES IN GT_FERTH
          WHERE ( MTART EQ 'HALB' OR MTART EQ 'FERT' ) AND
                FERTH EQ GT_FERTH-FERTH AND
                SPART = '27'.

The above select query fetches more than 100,000 records and is giving a short dump.... how can i improve the performance of this one.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

9 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jul 22, 2008 at 02:48 PM

    Have you checked GT_FERTH is not initial before the SELECT? How many rows are in GT_FERTH and how many entries do you have on the MARA table?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 22, 2008 at 02:49 PM

    Populate the internal table by batches to avoid the memmory dump

    regards,

    sarath p

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 22, 2008 at 02:52 PM

    If the cause of the dump is due to memory, you can try using the PACKAGE SIZE option in the select statement. Also add a check to make sure the table GT_FERTH is not initial.

    Add a comment
    10|10000 characters needed characters exceeded

    • The possible options to rectify the problem, is to find if you can use and existing index, or add more conditions in the where clause --> checked this it is hitting the right index.

      You can also see if you can fetch some of the keys from a different smaller table and the hit mara will the right keys --> explored this option.

      Basis can increase the timeout setting which is normally set to 10 mins, but i personally dont think that is a good solution. -


      > haven't tried this, keeping it as last option.

      IF nothing work, make it work in background. -


      > working on this.

      Thanks.

      Karthik.

  • Posted on Jul 22, 2008 at 02:53 PM
    data: GT_FERTH_tmp type table of GT_FERTH.
    
    GT_FERTH_tmp[] = GT_FERTH[].
    
    sort GT_FERTH_tmp.
    delete adjacent duplicates from GT_FERTH_tmp comparing all fields.
    IF NOT  GT_FERTH[]  is initial.
    
    SELECT MATNR
                  MEINS
                  FERTH
             FROM MARA
              INTO TABLE GT_CMATNR
              FOR ALL ENTRIES IN GT_FERTH_TMP
              WHERE ( MTART EQ 'HALB' OR MTART EQ 'FERT' ) AND
                    FERTH EQ GT_FERTH_TMP-FERTH AND
                    SPART = '27'.
    ENDIF.

    Use Initial check before calling the For all entries. and delete duplicates aslo.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 22, 2008 at 03:24 PM

    The maximum runtime of a dialog process is set by a profile parameter - rdisp/max_wprun_time. This is set by the BASIS team and can be dynamically adjusted depending on your SAP version. They will almost certainly tell you to run the job in the background unless this is a one-time job. You shouldn't be running anything in dialog mode that needs that much time. That's what background mode is for.

    Add a comment
    10|10000 characters needed characters exceeded

    • I modified the select query this way... still getting the run time error....

      the reason is size has exceeded 2GB... how i can i fix this error.

      SELECT MATNR
                    MEINS
                    FERTH
               FROM MARA
                INTO TABLE GT_CMATNR
                 WHERE ( MTART EQ 'HALB' OR MTART EQ 'FERT' ) AND
                      FERTH EQ GT_FERTH-FERTH AND
      

  • author's profile photo Former Member
    Former Member
    Posted on Jul 22, 2008 at 06:08 PM

    2GB for a three field table? Either your MARA is absolutely huge or we're looking in the wrong place. If you look at MARA using SE16 and provide the same selection criteria how many entries do you get?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 22, 2008 at 06:30 PM

    If you're not going to loop through GT_FERTH to get your MARA results, I think you're stuck with SELECT DISTINCT... otherwise...

    I might try something like this.

    LOOP AT GT_FERTH.

    SELECT DISTINCT MATNR MEINS FERTH FROM MARA

    INTO CORRESPONDING FIELDS OF TABLE GT_CMATNR

    WHERE ( MTART EQ 'HALB' OR MTART EQ 'FERT' )

    AND FERTH EQ GT_FERTH-FERTH AND SPART = '27'.

    • Other processing goes here

    ENDLOOP.

    other than that I would sort GT_FERTH & delete adjacent duplicates before select from MARA

    Doug -

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 22, 2008 at 06:33 PM

    and by the way... check your indexing with ST05. Make sure you're using one. Create one if necessary.

    Doug -

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 22, 2008 at 06:37 PM

    I think Doug has it. Duplicate entries in GT_FERTH would explain the long runtimes and huge memory usage. SORT and REMOVE ADJACENT DUPLICATES should fix this. Don't forget to give Doug his points.

    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.