Improve Acces to SD data

[former_member246786]

Hi,

I have problems trying to improve database access in SD requests.

I'm not the one who wrote the source code and now, I'm trying to modify it cause it's so bad that there is a 'Timeout' each time users try to launch it from production server.

The are a lot of requests. I began with the followings ones (hereunder is the original version) :

SELECT * FROM mard WHERE matnr IN s_matnr
                       AND werks IN s_werks
*                      AND spart IN s_spart
                       AND lgort IN s_lgort.
*   Initialisation de la quantité
    CLEAR wmenge.
    wmenge = mard-insme + mard-einme + mard-speme + mard-retme.

    SELECT * FROM mseg WHERE bukrs = p_bukrs
                         AND matnr = mard-matnr
                         AND werks = mard-werks
                         AND lgort = mard-lgort.
Treatment
     SELECT * FROM mkpf WHERE mblnr = mseg-mblnr
                   AND mjahr = li_mseg-mjahr
                   AND budat >= s_dtper-low.
ENDSELECT.
ENDSELECT.
ENDSELECT.

I began with these modifications, it seems to be better but not enought at all.

SELECT * FROM mard
  INTO CORRESPONDING FIELDS OF TABLE li_mard
  WHERE matnr IN s_matnr
                       AND werks IN s_werks
                       AND lgort IN s_lgort.

    IF NOT li_mard[] IS INITIAL.

    SELECT *
    INTO CORRESPONDING FIELDS OF TABLE wt_mseg
    FROM mseg AS a
    INNER JOIN mkpf AS b
        ON  a~mandt = b~mandt
           AND a~mblnr = b~mblnr
          AND a~mjahr = b~mjahr
    FOR ALL ENTRIES IN li_mard
        WHERE   a~bukrs = p_bukrs
          AND a~matnr = li_mard-matnr
          AND a~werks = li_mard-werks
          AND a~lgort = li_mard-lgort
          AND b~budat >= s_dtper-low.

     ENDIF.

Why dioo you thing about these modifications ? Can I do better ?

Do you any tips I can apply to solve this problem ?

An other thing to tell is that there are lot of other requests that take a long time and the analysis give my the following informations :

- the most time is taken with table MSEG

- More than 80% of the wasting time is lost in FETCH instructions.

- there is a lot of small request with several "Select" .. "Endselect" like :

           select ....
               select 
                 select
                  ......
                 endselect
              endselect
           endselect

Thanks for help cause it's a real brainstroming.

regards

Morgan

[former_member246786]

Hi,

I have problems trying to improve database access in SD requests.

I'm not the one who wrote the source code and now, I'm trying to modify it cause it's so bad that there is a 'Timeout' each time users try to launch it from production server.

The are a lot of requests. I began with the followings ones (hereunder is the original version) :

SELECT * FROM mard WHERE matnr IN s_matnr
                       AND werks IN s_werks
*                      AND spart IN s_spart
                       AND lgort IN s_lgort.
*   Initialisation de la quantité
    CLEAR wmenge.
    wmenge = mard-insme + mard-einme + mard-speme + mard-retme.

    SELECT * FROM mseg WHERE bukrs = p_bukrs
                         AND matnr = mard-matnr
                         AND werks = mard-werks
                         AND lgort = mard-lgort.
Treatment
     SELECT * FROM mkpf WHERE mblnr = mseg-mblnr
                   AND mjahr = li_mseg-mjahr
                   AND budat >= s_dtper-low.
ENDSELECT.
ENDSELECT.
ENDSELECT.

I began with these modifications, it seems to be better but not enought at all.

SELECT * FROM mard
  INTO CORRESPONDING FIELDS OF TABLE li_mard
  WHERE matnr IN s_matnr
                       AND werks IN s_werks
                       AND lgort IN s_lgort.

    IF NOT li_mard[] IS INITIAL.

    SELECT *
    INTO CORRESPONDING FIELDS OF TABLE wt_mseg
    FROM mseg AS a
    INNER JOIN mkpf AS b
        ON  a~mandt = b~mandt
           AND a~mblnr = b~mblnr
          AND a~mjahr = b~mjahr
    FOR ALL ENTRIES IN li_mard
        WHERE   a~bukrs = p_bukrs
          AND a~matnr = li_mard-matnr
          AND a~werks = li_mard-werks
          AND a~lgort = li_mard-lgort
          AND b~budat >= s_dtper-low.

     ENDIF.

Why dioo you thing about these modifications ? Can I do better ?

Do you any tips I can apply to solve this problem ?

An other thing to tell is that there are lot of other requests that take a long time and the analysis give my the following informations :

- the most time is taken with table MSEG

- More than 80% of the wasting time is lost in FETCH instructions.

- there is a lot of small request with several "Select" .. "Endselect" like :

           select ....
               select 
                 select
                  ......
                 endselect
              endselect
           endselect

Thanks for help cause it's a real brainstroming.

regards

Morgan

[Former Member]

Try this..

table i_mard is of type MARD....

SELECT * FROM mard

INTO TABLE i_mard

WHERE matnr IN s_matnr

AND werks IN s_werks

AND lgort IN s_lgort.

IF NOT li_mard[] IS INITIAL.

SELECT *

INTO TABLE wt_mkpf

FROM MKPF

FOR ALL ENTRIES IN li_mard

WHERE a~bukrs = p_bukrs

AND a~matnr = li_mard-matnr

AND a~werks = li_mard-werks

AND a~lgort = li_mard-lgort

AND b~budat >= s_dtper-low.

ENDIF.

Then try to get data from MSEG...

In any case don't use INTO CORRESPONDING.. it will take more DB time. Insteda u can use ABAP statements...

[former_member246786]

It is not possible to to what you are saying cause fields matnr, werks, lgort does not exist in mkpf.

[Former Member]

create secondary index on MSEG table with fields of where clause.

Regards

Prabhu

[Former Member]

Hi,

SELECT matnr werks lgort "select only the required fields

FROM mard

INTO table i_mard

WHERE matnr IN s_matnr

AND werks IN s_werks

AND lgort IN s_lgort.

IF sy-subrc = 0.

SELECT MBLNR MJAHR

FROM mseg

INTO table i_msge

FOR ALL ENTRIES IN I_MARD

WHERE bukrs = p_bukrs

AND matnr = i_mard-matnr

AND werks = i_mard-werks

AND lgort = i_mard-lgort.

IF sy-subrc = 0.

SORT I_MSEG BY MBLNR MJAHR.

DELETE ADJACENT DUPLICATES COMPARING MBLNR MJAHR.

SELECT * "select only req fields

FROM mkpf

INTO i_mkpf

FOR ALL ENTRIES IN i_mseg

WHERE mblnr = i_mseg-mblnr

AND mjahr = i_mseg-mjahr

AND budat >= s_dtper-low.

ENDIF.

ENDIF.

**reward if helpful

regards,

madhu

[former_member246786]

Thnks but the most part of the required fields for the report is the ones from MSEG, the purpose isn't to display fields from mkpf (or only a small part of it).

So, I can not select only the fields from MSEG that join MSEG with MKPF.

According to me, it's the original request that is confusing because it make the selection from MKPF in second part but it must be first, just after mard.

[Former Member]

Hi,

You have to check your requirement.

As you say it should be MARD --> MKPF --> MSEG.

give in more details as ur requirement is not clear.

regards,

madhu

[Former Member]

Hello,

If your requirement is to get the data from MFPK and MSEG, it would always be better to get the data first from MKPF. Then get the details from MSEG, based on the document number in MKPF. Then you may process the data fetched from MSEG based on the details of 'MATNR', 'WERKS', 'LGORT', fecthed from the table 'MARD'. This would decrease the time used for selecting the data from Database. Also i guess the separate processing of the data rather than the direct statements on database might take less time.

Regards,

pavan k

[Former Member]

Hi,

Try this :

1. do not use * anywhere in sqls

2. do not use corrosponding field clause

3. Important: User inner joins to join db tables as follows.

select <fld names> into table g_itab

from mkpf as a inner join mseg as b on a_{mblnr = b}mblnr and a_{mjahr = b}mjahr

inner join mard as c where b_{matnr = c}matnr

where <all your where conditions>

.

This is only way to improve this query. To improve it you should use more and more where criteria on KEY fields.

Do not use select and endselect.

Hope this will help you.

Jogdand M B