select statement

[Former Member]

Hi experts,

Pls see the following select statement.

Iam getting AUFNR value from Z table.

  SELECT AUFNR FROM <b>ZCSPICKLISTGROUP</b> INTO TABLE
  T_HDR WHERE GRPNO = P_GRPNO.

IF NOT T_HDR[] IS INITIAL.
     SELECT MBLNR ZEILE MATNR MENGE AUFNR 
        RSNUM EQUNR ABLAD ZEILE
         INTO CORRESPONDING FIELDS OF TABLE T_DET
           FROM MSEG
           FOR ALL ENTRIES IN T_HDR
             WHERE <b>AUFNR = T_HDR-AUFNR</b> AND
                   <b>BWART = '981'</b>.
ENDIF.

Iam trying to get the values using AUFNR & BWART from MSEG.Since these fields are not key fields it is taking lot of time.Is it possible to put <b>indexing</b> for this table to increase the performance??

If it is possible Can any one tell me how to put indexing?

Thanks

Kaki

[Former Member]

You can create Index on MSEG table. But check if you can get the document number from MKPF and then based on document number get the data from MSEG.

Regards

Aman

[Former Member]

hi

can you please check this MSEG table already have any Index with these 2 or either 1 of these fields.

if so use it.

Regards

srikanth

[Former Member]

Hi Srinkanth,

Only MBLNR,MJAHR,ZEILE only having indexing.Can we create programitically?? My objective is to improve performance of this select statement.

[Former Member]

Hi Kaki,

You cannot create indexes programmatically.

Create a new index for your AUFNR field in the MSEG table.

Regards,

Wenceslaus.

[Former Member]

Hi Kaki,

You can create indexes for the table in SE11.

Open your table and then in the menu Goto->Indexes.

In the popup, click the create button to create a new index.

You can ask your BASIS people to do this for you.

And use INTO TABLE instead of INTO CORRESPONDING FIELDS OF

in your select query...

Regards,

Wenceslaus.

[Former Member]

Hi Kaki,

You can use indexing on the MSEG table based on AUFNR field. It improves the performance. I also faced the same issue in one of my requirements and indexing on AUFNR improved the query performance. Create index on AUFNR in table thru index tab in SE11.

Cheers,

Vikram

Pls reward helpful replies!!

[Former Member]

It's generally not a good idea to create an index on an SAP table simply to speed up one select statement. There is overhead to an index. Every time a record is updated, an additional index will have to be updated as well. So the update program is slowed down. The additional index will also take up some of the tablespace of the table. Talk to your DBAs about this.

There may be a much simpler programming solution to this as well.

Rob

[Former Member]

Hi Rob,

Can you suggest me the better way to improve this select statement other than indexing...

    SELECT MBLNR ZEILE MATNR MENGE AUFNR
         INTO CORRESPONDING FIELDS OF TABLE T_DET
           FROM MSEG
           FOR ALL ENTRIES IN T_HDR
             WHERE AUFNR = T_HDR-AUFNR AND
                   BWART = '981'.

[Former Member]

Table AUFM Has the material document number and is indexed by AUFNR. Try selecting from this first. You can go to MSEG using MBLNR if there are any missing fields.

Rob

[Former Member]

HI

GOOD

TRY THIS OUT

SELECT MBLNR ZEILE MATNR MENGE AUFNR

RSNUM EQUNR ABLAD ZEILE FROM MSEG

INTO CORRESPONDING FIELDS OF TABLE T_DET

FOR ALL ENTRIES IN T_HDR

WHERE AUFNR = T_HDR-AUFNR AND BWART = '981'.

THANKS

MRUTYUN