mska and mssa query slow

[Former Member]

Hi experts,

How to optimize and correct this query.

SELECT mska~matnr mska~werks lgort charg mska~sobkz
           mska~vbeln mska~posnr
           kalab kains kaspe kaein kzbws
           INTO (collector-matnr, collector-werks, collector-lgort,
                 collector-charg, collector-sobkz,
                 collector-vbeln, collector-posnr,
                 collector-labst, collector-insme, collector-speme,
                 collector-einme, collector-kzbws)
           FROM mska INNER JOIN mssa
           ON   mska~matnr = mssa~matnr
            AND mska~werks = mssa~werks
            AND mska~sobkz = mssa~sobkz
            AND mska~vbeln = mssa~vbeln
            AND mska~posnr = mssa~posnr
           FOR ALL entries IN t_mat
           WHERE mska~matnr = t_mat-matnr
             AND mska~werks = t_mat-werks
             AND mska~sobkz NE 'W'
             AND mska~lgort IN lgort
             AND mska~charg IN charg.

      PERFORM                f2000_collect_collector.
    ENDSELECT.
*----------------------------------------------------------------------*
FORM f2000_collect_collector.
*----------------------------------------------------------------------*
* process the functions "No zero stocks",
* "Negative stocks only", "Without batches" here
*----------------------------------------------------------------------*
  IF negativ = 'X'.
*   ignore entry if all stocks are zero or greater
    IF collector-labst >= 0 AND collector-einme >= 0 AND
       collector-insme >= 0 AND collector-retme >= 0 AND
       collector-speme >= 0 AND collector-umlme >= 0.
      EXIT.                  "--> go to exit
    ENDIF.
  ENDIF.

  IF nozero = 'X'.
*   ignore all entries without stock
    IF collector-labst = 0 AND collector-einme = 0 AND
       collector-insme = 0 AND collector-retme = 0 AND
       collector-speme = 0 AND collector-umlme = 0.
      EXIT.                  "--> go to exit
    ENDIF.
  ENDIF.
  IF xmchb IS INITIAL.
    CLEAR collector-charg.
  ENDIF.
  COLLECT                    collector.
ENDFORM.                     "f2000_COLLECT_collector.

[Sandra_Rossi]

Is there a problem with it, or do you ask for a training? In this latter case, check the SDN performance home page, or ask for a training, because this is rather complex.

Note that maybe it's correct, and maybe it's not correct. It depends on the volume of data, on indexes, etc.

[Former Member]

why dont you fetch these into a internal table. you can put most of the conditions on

PERFORM                f2000_collect_collector.

at the where statement.

Thanks

Nafran

[venkat_o]

NSTomar, Try this way. <li>Instead of SELECT-ENDSELECT, use SELECT ..INTO . <li>Loop the collector table and check the validation.

 SELECT mska~matnr mska~werks lgort charg mska~sobkz
           mska~vbeln mska~posnr
           kalab kains kaspe kaein kzbws
           INTO CORRESPONDING FIELDS OF TABLE collector "CORRESPONDING FIELDS OF TABLE collector
           FROM mska INNER JOIN mssa
           ON   mska~matnr = mssa~matnr
            AND mska~werks = mssa~werks
            AND mska~sobkz = mssa~sobkz
            AND mska~vbeln = mssa~vbeln
            AND mska~posnr = mssa~posnr
           FOR ALL entries IN t_mat
           WHERE mska~matnr = t_mat-matnr
             AND mska~werks = t_mat-werks
             AND mska~sobkz NE 'W'
             AND mska~lgort IN lgort
             AND mska~charg IN charg.

 LOOP AT collector. "Loop the collector table
* process the functions "No zero stocks",
* "Negative stocks only", "Without batches" here
*----------------------------------------------------------------------*
   IF negativ = 'X'.
*   ignore entry if all stocks are zero or greater
     IF collector-labst >= 0 AND collector-einme >= 0 AND
        collector-insme >= 0 AND collector-retme >= 0 AND
        collector-speme >= 0 AND collector-umlme >= 0.
       EXIT.                  "--> go to exit
     ENDIF.
   ENDIF.

   IF nozero = 'X'.
*   ignore all entries without stock
     IF collector-labst = 0 AND collector-einme = 0 AND
        collector-insme = 0 AND collector-retme = 0 AND
        collector-speme = 0 AND collector-umlme = 0.
       EXIT.                  "--> go to exit
     ENDIF.
   ENDIF.
   IF xmchb IS INITIAL.
     CLEAR collector-charg.
   ENDIF.
   COLLECT                    collector.
 ENDLOOP.

Thanks Venkat.O

[_IvanFemia_]

Hi,

as suggested fetch data with statement INTO CORRESPONDING FIELD OF TABLE, btw I prefer INTO TABLE (create an ad hoc structure) it is more efficient.

Try to monitor you query with ST05 and check witch is the worst join.

Regards,

Ivan

[Former Member]

long time no answer

[former_member182114]

Hi NSTomar,

Questions:

a) How many entries internal table t_mat have ?

b) How long this statment spent on ST05 for Open and for first Fetchs ?

c) How many entries have on tables mska and mssa ?

d) The values for LGORT and CHARG are filled or not ? How many conditions each field have ?

Here you can find excelent tips to really solve your problem

Even w/o the answers you can split the database fetch from application processing, also use dinamyic filtering to filter direct on database:

DATA: lv_negative TYPE string, lv_nozero TYPE string.
IF negativ = 'X'.
  CONCATENATE 'AND ( NOT ('
  'collector-labst >= 0 AND '
  'collector-einme >= 0 AND '
  'collector-insme >= 0 AND '
  'collector-retme >= 0 AND '
  'collector-speme >= 0 AND '
  'collector-umlme >= 0) )' INTO lv_negative.
ENDIF.

IF nozero = 'X'.
  CONCATENATE 'AND ( NOT ('
  'collector-labst = 0 AND '
  'collector-einme = 0 AND''
  'collector-insme = 0 AND '
  'collector-retme = 0 AND '
  'collector-speme = 0 AND '
  'collector-umlme = 0 ) )' INTO lv_nozero.
ENDIF.

SELECT mska~matnr mska~werks lgort charg mska~sobkz
       mska~vbeln mska~posnr
       kalab kains kaspe kaein kzbws
INTO TABLE lt_collec
FROM mska INNER JOIN mssa
  ON mska~matnr = mssa~matnr
 AND mska~werks = mssa~werks
 AND mska~sobkz = mssa~sobkz
 AND mska~vbeln = mssa~vbeln
 AND mska~posnr = mssa~posnr
FOR ALL entries IN t_mat
WHERE mska~matnr = t_mat-matnr
  AND mska~werks = t_mat-werks
  AND mska~sobkz NE 'W'
  AND mska~lgort IN lgort
  AND mska~charg IN charg
  (lv_negative)
  (lv_nozero).

LOOP AT lt_collec ASSIGNING <fs_collec>.
  IF xmchb IS INITIAL.
    CLEAR <fs_collec>-charg.
  ENDIF.
  COLLECT <fs_collec> INTO collector.
ENDLOOP.

PS: I didn't tested the syntax of this code, but the idea is passed.

Regards,

Fernando Da Ró