Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Help in MSEG select performance.

rodrigo_paisante3
Active Contributor
0 Kudos
238

Hi all, please, help me with this performance.

The coment lines was the original code.

The previous code got time out and the new code is taking long long time. The range r_mblnr has about 1500 records.

MSEG table has more than 1.500.000 records.

Thanks in advance, regards

RP



**      SELECT  mblnr mjahr zeile bwart matnr werks lgort charg
**              insmk lifnr shkzg waers dmbtr bnbtr bualt menge
**              meins erfmg erfme bpmng bprme ebeln ebelp lfbnr
**              bukrs lsmng lsmeh lfbja lfpos xwsbr sjahr smbln
**              smblp
**
**              FROM mseg
**              INTO TABLE t_mseg_cancel
**            FOR ALL ENTRIES IN t_mseg
**            WHERE ( smbln EQ t_mseg-mblnr
**              AND   sjahr EQ t_mseg-mjahr
**              AND   smblp EQ t_mseg-zeile )
**               OR ( lfbnr = t_mseg-mblnr
**              AND   lfbja = t_mseg-mjahr
**              AND   lfpos = t_mseg-zeile ).
**
**
**      SELECT  mblnr mjahr zeile bwart matnr werks lgort charg
**              insmk lifnr shkzg waers dmbtr bnbtr bualt menge
**              meins erfmg erfme bpmng bprme ebeln ebelp lfbnr
**              bukrs lsmng lsmeh lfbja lfpos xwsbr sjahr smbln
**              smblp
**
**              FROM mseg APPENDING TABLE t_mseg_cancel
**              FOR ALL ENTRIES IN t_mseg
**              WHERE   mblnr EQ t_mseg-mblnr
**                AND ( lfbnr NE space
**                 OR   smbln NE space ).

    LOOP AT t_mseg.

      add_range r_mblnr t_mseg-mblnr.
      add_range h_mjahr t_mseg-mjahr.
      add_range r_zeile t_mseg-zeile.

    ENDLOOP.

    SORT: r_mblnr BY low,
          h_mjahr BY low,
          r_zeile BY low.

    DELETE ADJACENT DUPLICATES FROM r_mblnr COMPARING low.
    DELETE ADJACENT DUPLICATES FROM h_mjahr COMPARING low.
    DELETE ADJACENT DUPLICATES FROM r_zeile COMPARING low.


    SELECT  mblnr mjahr zeile bwart matnr werks lgort charg
            insmk lifnr shkzg waers dmbtr bnbtr bualt menge
            meins erfmg erfme bpmng bprme ebeln ebelp lfbnr
            bukrs lsmng lsmeh lfbja lfpos xwsbr sjahr smbln
            smblp
            FROM mseg
            INTO TABLE t_mseg_cancel
            FOR ALL ENTRIES IN r_mblnr
          WHERE ( smbln = r_mblnr-low
            AND   sjahr IN r_mjahr
            AND   smblp IN r_zeile ).

    SELECT  mblnr mjahr zeile bwart matnr werks lgort charg
            insmk lifnr shkzg waers dmbtr bnbtr bualt menge
            meins erfmg erfme bpmng bprme ebeln ebelp lfbnr
            bukrs lsmng lsmeh lfbja lfpos xwsbr sjahr smbln
            smblp
            FROM mseg
            APPENDING TABLE t_mseg_cancel
            FOR ALL ENTRIES IN r_mblnr
          WHERE ( lfbnr = r_mblnr-low
            AND   lfbja IN r_mjahr
            AND   lfpos IN r_zeile ).


    SELECT  mblnr mjahr zeile bwart matnr werks lgort charg
            insmk lifnr shkzg waers dmbtr bnbtr bualt menge
            meins erfmg erfme bpmng bprme ebeln ebelp lfbnr
            bukrs lsmng lsmeh lfbja lfpos xwsbr sjahr smbln
            smblp

            FROM mseg APPENDING TABLE t_mseg_cancel
            FOR ALL ENTRIES IN r_mblnr
            WHERE   mblnr = r_mblnr-low
              AND ( lfbnr NE space
               OR   smbln NE space ).

1 ACCEPTED SOLUTION

Former Member
0 Kudos
77

Hi Rodrigo,

if this statement is really important, I would go back to a single Statement, I would create an additional index and I would change the last statement (may be):


SELECT mblnr mjahr zeile bwart matnr werks lgort charg
       insmk lifnr shkzg waers dmbtr bnbtr bualt menge
       meins erfmg erfme bpmng bprme ebeln ebelp lfbnr
       bukrs lsmng lsmeh lfbja lfpos xwsbr sjahr smbln
       smblp
  FROM mseg
  INTO TABLE t_mseg_cancel
   FOR ALL ENTRIES IN t_mseg
 WHERE ( smbln EQ t_mseg-mblnr
     AND sjahr EQ t_mseg-mjahr
     AND smblp EQ t_mseg-zeile )
    OR ( lfbnr = t_mseg-mblnr
     AND lfbja = t_mseg-mjahr
     AND lfpos = t_mseg-zeile )
    OR ( mblnr EQ t_mseg-mblnr
     AND mjahr EQ t_mseg-mjahr		" if correct
     AND zeile EQ t_mseg-zeile		" if correct
     AND ( lfbnr NE space OR smbln NE space ) ).

The new secondary Index should contain the fields: LFBNR, LFBJA and LFPOS.

Don't forget to create new statistics after creating the index.

Many people probably disagree with a new secondary index, but to improve your report, it is probably the only solution.

You should not add a new index without good reasons, but a single index does not cost you too much (insert and update performance). You should not tend to create additional indexes for any statement, but on big tables it is sometimes unavoidable.

Best regards

Ralph Ganszky

6 REPLIES 6

kesavadas_thekkillath
Active Contributor
0 Kudos
77

heavy select...

try using package size concept...or execute it in background..

or create new secondary indexex based on your where clause...

Former Member
0 Kudos
78

Hi Rodrigo,

if this statement is really important, I would go back to a single Statement, I would create an additional index and I would change the last statement (may be):


SELECT mblnr mjahr zeile bwart matnr werks lgort charg
       insmk lifnr shkzg waers dmbtr bnbtr bualt menge
       meins erfmg erfme bpmng bprme ebeln ebelp lfbnr
       bukrs lsmng lsmeh lfbja lfpos xwsbr sjahr smbln
       smblp
  FROM mseg
  INTO TABLE t_mseg_cancel
   FOR ALL ENTRIES IN t_mseg
 WHERE ( smbln EQ t_mseg-mblnr
     AND sjahr EQ t_mseg-mjahr
     AND smblp EQ t_mseg-zeile )
    OR ( lfbnr = t_mseg-mblnr
     AND lfbja = t_mseg-mjahr
     AND lfpos = t_mseg-zeile )
    OR ( mblnr EQ t_mseg-mblnr
     AND mjahr EQ t_mseg-mjahr		" if correct
     AND zeile EQ t_mseg-zeile		" if correct
     AND ( lfbnr NE space OR smbln NE space ) ).

The new secondary Index should contain the fields: LFBNR, LFBJA and LFPOS.

Don't forget to create new statistics after creating the index.

Many people probably disagree with a new secondary index, but to improve your report, it is probably the only solution.

You should not add a new index without good reasons, but a single index does not cost you too much (insert and update performance). You should not tend to create additional indexes for any statement, but on big tables it is sometimes unavoidable.

Best regards

Ralph Ganszky

0 Kudos
77

Hello,

Sometimes, It is less costly to make a loop with select in it.

Instead using a select for all entries or an heavy table join.

Sincerely

Christophe Blineau

rodrigo_paisante3
Active Contributor
0 Kudos
77

Thanks for your answers, but it not help in this case.

Already exists one index for smbln sjahr smblp and other to

lfbnr lfbja lfpos.

I tried to change the last (lfbnr lfbja lfpos) including the mandt field, but other parts of the program that do select in MSEG become slow. Nice...

I am listening for other opinion...

continue testing

Regards

0 Kudos
77

Once again we see the folly of creating an index to speed up a single SELECT in a single program.

Since part of the WHERE is:

lfbnr NE space

There is no way this SELECT could use the new index.

What I would do is see if there are any secondary index tables that will help or see if the sales document flow table (VBFA) can be used.

If not, run it in the background.

Rob

rodrigo_paisante3
Active Contributor
0 Kudos
77

INCREDIBLE

I dont know why, how, what happened.

But when I get back the index without the field MANDT, and transport it to QA, each select run less than 2 seconds...

The index wake up and decide to work!!!

Lol!!!

😛