topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Wed, 10 May 2006 01:55:23 GMT

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

Re: select statement

Former Member — Wed, 10 May 2006 02:04:51 GMT

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

Re: select statement

Former Member — Wed, 10 May 2006 02:18:54 GMT

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

Re: select statement

Former Member — Wed, 10 May 2006 02:19:58 GMT

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.

Re: select statement

Former Member — Wed, 10 May 2006 02:22:24 GMT

Hi Srinkanth,

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

Re: select statement

Former Member — Wed, 10 May 2006 02:25:14 GMT

Hi Kaki,

You cannot create indexes programmatically.

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

Regards,

Wenceslaus.

Re: select statement

Former Member — Wed, 10 May 2006 02:32:25 GMT

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!!

Re: select statement

Former Member — Wed, 10 May 2006 03:20:02 GMT

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

Re: select statement

Former Member — Wed, 10 May 2006 03:26:15 GMT

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'.

Re: select statement

Former Member — Wed, 10 May 2006 03:27:04 GMT

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

Re: select statement

Former Member — Wed, 10 May 2006 03:37:01 GMT

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