05-10-2006 2:55 AM
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
05-10-2006 3:19 AM
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.
05-10-2006 3:04 AM
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
05-10-2006 3:18 AM
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
05-10-2006 3:22 AM
Hi Srinkanth,
Only MBLNR,MJAHR,ZEILE only having indexing.Can we create programitically?? My objective is to improve performance of this select statement.
05-10-2006 3:25 AM
Hi Kaki,
You cannot create indexes programmatically.
Create a new index for your AUFNR field in the MSEG table.
Regards,
Wenceslaus.
05-10-2006 3:19 AM
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.
05-10-2006 3:32 AM
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!!
05-10-2006 4:20 AM
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
05-10-2006 4:26 AM
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'.
05-10-2006 4:37 AM
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
05-10-2006 4:27 AM
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