topic Re: Fetching from MSEG in Application Development and Automation Discussions

Fetching from MSEG

aditya_niyogi — Thu, 29 Oct 2009 13:52:33 GMT

Hi ABAP gurus,

I have a unique problem - I am fetching from MSEG without using the keys and always getting a dump in the select statment.

The table MSEG contains 22 Million entries and I am fetching using the statement:

SELECT matnr mjahr bwart menge oib_timestamp

INTO TABLE it_mseg

FROM mseg FOR ALL ENTRIES IN it_mard

WHERE matnr = it_mard-matnr

AND werks = it_mard-werks.

I have tried the following, but no result as of yet:

(1) Made an index and tried fetching

(2) All combinations of JOINS with MSEG table

(3) Writing Native SQL statements.

Please help.

Re: Fetching from MSEG

Former Member — Thu, 29 Oct 2009 13:57:29 GMT

Hi Aditya,

just a guess. Try:

INTO CORRESPONDING FIELDS OF TABLE it_mseg

Kind regards,

Roy

Re: Fetching from MSEG

RaymondGiuseppi — Thu, 29 Oct 2009 14:09:36 GMT

There should already be an index MSEG~M on your system whose keys are MANDT, MATNR, WERKS and LGORT, BWART, SOBKZ so you request should have used this index, check if it is activated database side.

- how many records do you gave in it_mard

- which error exact error do you get (ST22 or SM21) - related to SQL, duration or memory ?

Regards,

Raymond

Re: Fetching from MSEG

aditya_niyogi — Thu, 29 Oct 2009 16:19:25 GMT

Hi,

Currently 1900 records are being given to IT_MARD. I have tried various scenarios and have obtained the following dumps:

(1) Timeout.

(2) Memory failure - this was because the total no. of records fetched was beyond 2 million.

(3) Database failure - this was because I tried accessing the index with the MSEG~M index. There were too many criterias I set for BWART, SOBKZ and LGORT fields.

Let me know if you need any more information. I have got any and every error I think that is related to memory .

Thanks in advance,

Aditya.

Edited by: Aditya Niyogi on Oct 29, 2009 9:49 PM

Re: Fetching from MSEG

ThomasZloch — Thu, 29 Oct 2009 16:34:03 GMT

IT_MARD should be sorted by matnr and werks and not contain duplicates, but I seriously think you should go back to the scenario that gave you just the time out and no memory problems and run this query in background. Or try to use block processing if possible (SELECT ... PACKAGE SIZE ... ).

Thomas

Re: Fetching from MSEG

Rui_Dantas — Thu, 29 Oct 2009 16:34:37 GMT

Hi Aditya,

So for those 1900 materials there are 2 million material documents?

If so, what exactly do you want to do with those 2 million documents? Surely no user will want to check 2 million lines of information, or?

Rui Dantas

Re: Fetching from MSEG

kesavadas_thekkillath — Fri, 30 Oct 2009 05:35:08 GMT

First of all do you need documents for all the movement types ?

Can you tell the logic behind MARD and MSEG.

Generally i use

MARC-MATNR and MARC-werks to MSEG.

Try to pass posting date to mkpf , get the document no, then get the details from mseg.

check transaction MB51

Re: Fetching from MSEG

RaymondGiuseppi — Fri, 30 Oct 2009 07:14:21 GMT

Try to read the data from packet of 100 or so keys records (are you sure you really need every records, without date or type criteria) then condense/collect the data before fetching the next packet, you may also considere true parallelization, via function modules (CALL FUNCTION ... STARTING NEW TASK ... PERFORMING ... ON END OF TASK)

If you actually need the 2000000 records, use EXTRACTs, but they will be stored in database if

larger than 500KB, so performance problem may arise in the late processing.

Regards,

Raymond

Re: Fetching from MSEG

Former Member — Fri, 30 Oct 2009 15:48:05 GMT

Hi Aditya,

Why does your where condition contain only 2 fields?

Can you try narrowing the database fetch?

Use more fields in the where condition to narrow the fetch..

Regards,

SuryaD.

Re: Fetching from MSEG

aditya_niyogi — Mon, 02 Nov 2009 09:41:35 GMT

Hi Guys,

Regarding the requirement - as my functional is only aware of MSEG table, hence all entries have to be used. I had one suggestion and that was to find out how the data was fetched into the MSEG table (this is a cluster table, so all the data should be coming from somewhere else).

Regarding approaces to be taken, I am considering the following approaches:

(1) Parallelization

(2) Using PACKAGE

(3) Using Extracts.

Would like some links or hints regarding these approaches, since this goes beyond normal ABAP coding.

Re: Fetching from MSEG

RaymondGiuseppi — Mon, 02 Nov 2009 10:18:34 GMT

(1) Parallelization - [Parallel Processing with Asynchronous RFC|http://help.sap.com/saphelp_nw04/helpdata/EN/22/0425c6488911d189490000e829fbbd/frameset.htm]

(2) Using PACKAGE - [PACKAGE SIZE n |http://help.sap.com/abapdocu_70/en/ABAPINTO_CLAUSE.htm#!ABAP_ONE_ADD@1@] /or/ [OPEN CURSOR|http://help.sap.com/abapdocu_70/en/ABAPOPEN_CURSOR.htm]

(3) Using Extracts. - [Extracts|http://help.sap.com/saphelp_nw70/helpdata/EN/9f/db9ed135c111d1829f0000e829fbfe/frameset.htm]

Regards,

Raymond

Re: Fetching from MSEG

Former Member — Mon, 02 Nov 2009 20:12:05 GMT

Hi Aditya,

One of 2 things could be your problem.

1) Since the execution time large, the execution of your code is probably just timing out. This is a BASIS setting.

2) The records fetched could be so large that it exceeds the maximum permissible size of an internal table in your system. This too is a BASIS setting. This sounds more likely to be your problem because your select should be using index M.

Note: As an ABAP programmer you cannot change these settings. You will have to work around them.

My 2 cents:

1) MSEG has a secondary index M on fields MATNR, WERKS, LGORT, BWART, SOBKZ. Your code is probably already using this index. Explore the possibility of using LGORT, BWART, SOBKZ in your where clause. This would reduce the size of the prospective records thus influencing both the probable causes.

2) Whenever you use FOR ALL ENTRIES makes sure that you include all the primary key fields in the field list or else you could loose some data.

3) Using PACKAGE SIZE would reduce the number of records you process in each pass. It does not do anything for the speed of execution of your program (it could make it a little slower) but you can easily eliminate the dumping because of large number of qualifying entries.

4) Please avoid using native SQL.

IF NOT it_mard[] IS INITIAL.

  SELECT mblnr
         mjahr
         zeile
         matnr
         mjahr
         bwart
         menge
         oib_timestamp
  INTO TABLE it_mseg
  PACKAGE SIZE 5000
  FROM mseg FOR ALL ENTRIES IN it_mard
  WHERE matnr EQ it_mard-matnr
  AND   werks EQ it_mard-werks
  AND   lgort EQ it_mard-lgort.

*    LOOP AT it_mseg INTO w_mseg.
*
**     Processing code
*
*    ENDLOOP.

  ENDSELECT.

ENDIF.

Re: Fetching from MSEG

HermannGahm — Tue, 03 Nov 2009 09:38:47 GMT

Hi Mark,


> 3) Using PACKAGE SIZE would reduce the number of records you process in each pass. It does not do anything for the speed of execution of your program (it could make it a little slower) but you can easily eliminate the dumping because of large number of qualifying entries.
> IF NOT it_mard[] IS INITIAL.
> 
>   SELECT mblnr
>          mjahr
>          zeile
>          matnr
>          mjahr
>          bwart
>          menge
>          oib_timestamp
>   INTO TABLE it_mseg
>   PACKAGE SIZE 5000
>   FROM mseg FOR ALL ENTRIES IN it_mard
>   WHERE matnr EQ it_mard-matnr
>   AND   werks EQ it_mard-werks
>   AND   lgort EQ it_mard-lgort.
> 
> *    LOOP AT it_mseg INTO w_mseg.
> *
> **     Processing code
> *
> *    ENDLOOP.
> 
>   ENDSELECT.
> 
> ENDIF.

are you sure about the PACKAGE SIZE?

This is from the ABAP documentation:

If the additions PACKAGE SIZE or UP TO n ROWS are specified together with FOR ALL ENTRIES, they are not passed to the database system but are applied instead to the resulting set once all selected rows on the application server have been imported.

Kind regards,

Hermann

Re: Fetching from MSEG

HermannGahm — Tue, 03 Nov 2009 09:39:53 GMT

Hi Thomas,

PACKAGE SIZE will not help for the FAE (see my response to Mark).

Kind regards,

Hermann

Re: Fetching from MSEG

HermannGahm — Tue, 03 Nov 2009 09:49:40 GMT

Hi Aditya,


> Hi,
> 
> Currently 1900 records are being given to IT_MARD. I have tried various scenarios and have obtained the following dumps:
> 
> (1) Timeout.
> (2) Memory failure - this was because the total no. of records fetched was beyond 2 million.
> (3) Database failure  - this was because I tried accessing the index with the MSEG~M index. There were too many criterias I set for BWART, SOBKZ and LGORT fields. 
> 
> Let me know if you need any more information. I have got any and every error I think that is related to memory  . 
> 
> Thanks in advance,
> Aditya.
> 
> Edited by: Aditya  Niyogi on Oct 29, 2009 9:49 PM

have you tried with smaller driver tables?

eg.

do the FAE with 500 entries in it_mard, process these, then do the next 500....

Kind regards,

Hermann