Program using mseg .How to reduce DB time..

[Former Member]

Hi All ,

SELECT

mblnr

mjahr

bwart

matnr

werks

lgort

charg

sobkz

lifnr

shkzg

waers

dmbtr

menge

meins

ebeln

ebelp

kostl

aufnr

bukrs

INTO CORRESPONDING FIELDS OF TABLE imseg

FROM mseg CLIENT SPECIFIED

FOR ALL ENTRIES IN imkpf

WHERE mandt = sy-mandt

AND mblnr = imkpf-mblnr

AND mjahr = imkpf-mjahr.

DELETE imseg WHERE NOT ( sobkz EQ 'K' ) .

DELETE imseg WHERE NOT ( bwart = '411' OR bwart = '412' ) .

IF NOT ( s_werks IS INITIAL ) .

DELETE imseg WHERE NOT ( werks IN s_werks ) .

ENDIF.

IF NOT ( p_bukrs IS INITIAL ) .

DELETE imseg WHERE NOT ( bukrs EQ p_bukrs ) .

ENDIF.

Above given is the query.Here <b>werks</b> and <b>bukrs</b> are mandatory field.

MSEG contains three key fields,two of them are given in where clause.

" INTO CORRESPONDING FIELDS OF TABLE imseg" statement can't be removed.

So these are the conditions.Please give some suggestion specific to this so that i can reduce the DB fetching time.

Thanks in advance,

Shyam

[Former Member]

Hello Shyam,

I would suggest you create a database view using MKPF and MSEG. This will help as the databsae will handle the performance part and make the query optimised.

[Former Member]

Dear Shyam,

1)Declare a internal table for all fileds in filed list of SELECT statement (Avoid into corresponding fields clause).

TYPES: BEGIN OF tys_mseg,

mblnr TYPE mblnr,

mjahr TYPE mjahr,

bwart TYPE bwart,

matnr TYPE matnr,

werks TYPE werks_d,

lgort TYPE lgort_d,

charg TYPE charg_d,

sobkz TYPE sobkz,

lifnr TYPE elifn,

shkzg TYPE shkzg,

waers TYPE waers,

dmbtr TYPE dmbtr,

menge TYPE menge_d,

meins TYPE meins,

ebeln TYPE bstnr,

ebelp TYPE ebelp,

kostl TYPE kostl,

aufnr TYPE kostl,

bukrs TYPE bukrs,

END OF tys_mseg.

DATA: it_mseg TYPE TABLE OF tys_mseg.

2) Better to avoid usage of paritial key .

3) Don't use SY-MANDT field in where clause. System will take care (Since data is client dependent)

Now query will be look like this.

IF NOT imkpf[] IS INITIAL.

SELECT

mblnr

mjahr

bwart

matnr

werks

lgort

charg

sobkz

lifnr

shkzg

waers

dmbtr

menge

meins

ebeln

ebelp

kostl

aufnr

bukrs

INTO TABLE it_mseg

FROM mseg

FOR ALL ENTRIES IN imkpf

WHERE mblnr EQ imkpf-mblnr

AND mjahr EQ imkpf-mjahr.

ENDIF.

If its helpful reward points.

Regards

Bhupal Reddy

[christian_wohlfahrt]

Hi Shyam!

As already said, an into table will be faster. But if you can't re-sort your fields, then an additional table, looping and filling your final result table will take longer.

Otherwise, add your additional selection criteria to your select. It will not make your select faster (because the primary key (without posnr) is fastest possible access), but you will get less entries -> less data to be transferred via network between DB server and application server.

At least werks and bukrs make sense, but also sobkz and bwart can be added. This fields can't be used for an index access (NOT), but they don't belong to the primary key anyway.

...FROM mseg 
WHERE mblnr = imkpf-mblnr
AND mjahr = imkpf-mjahr
AND werks = p_werks
AND bukrs = p_bukrs
AND sobkz NE 'K'
AND NOT bwart in ('411', '412').

Regards,

Christian

[former_member186741]

as others have sadi, instead of deleting include the criteria directly in the where clause BUT they are in fact positive conditions, and therefore may utilize an index if one is available:

WHERE mblnr = imkpf-mblnr

AND mjahr = imkpf-mjahr

AND werks = p_werks

AND bukrs = p_bukrs

AND sobkz = 'K'

AND bwart in ('411', '412').

[Former Member]

Hi Shyam

If you want use move-corresponding and the same internal table; there is nothing much left to improve the performance other than including the sobkz, bwart, werks, bukrs and avoid fetching these records.

Why do you fetch them and delete? You can restrict by giving them in Where class.

Regards

Surya.