URGENT------MB5B : PERFORMANCE PROBLEM

former_member377111 — Wed, 09 Jan 2008 06:08:39 GMT

Hi,

We are getting the time out error while running the transaction MB5B. We have posted the same to SAP global support for further analysis, and SAP revrted with note 1005901 to review.

The note consists of creating the Z table and some Z programs to execute the MB5B without time out error, and SAP has not provided what type logic has to be written and how we can be addressed this.

Could any one suggest us how can we proceed further.

Note as been attached for reference.

Note 1005901 - MB5B: Performance problems

Note Language: English Version: 3 Validity: Valid from 05.12.2006

Summary

Symptom

o The user starts transaction MB5B, or the respective report

RM07MLBD, for a very large number of materials or for all materials

in a plant.

o The transaction terminates with the ABAP runtime error

DBIF_RSQL_INVALID_RSQL.

o The transaction runtime is very long and it terminates with the

ABAP runtime error TIME_OUT.

o During the runtime of transaction MB5B, goods movements are posted

in parallel:

- The results of transaction MB5B are incorrect.

- Each run of transaction MB5B returns different results for the

same combination of "material + plant".

More Terms

MB5B, RM07MLBD, runtime, performance, short dump

Cause and Prerequisites

The DBIF_RSQL_INVALID_RSQL runtime error may occur if you enter too many

individual material numbers in the selection screen for the database

selection.

The runtime is long because of the way report RM07MLBD works. It reads the

stocks and values from the material masters first, then the MM documents

and, in "Valuated Stock" mode, it then reads the respective FI documents.

If there are many MM and FI documents in the system, the runtimes can be

very long.

If goods movements are posted during the runtime of transaction MB5B for

materials that should also be processed by transaction MB5B, transaction

MB5B may return incorrect results.

Example: Transaction MB5B should process 100 materials with 10,000 MM

documents each. The system takes approximately 1 second to read the

material master data and it takes approximately 1 hour to read the MM and

FI documents. A goods movement for a material to be processed is posted

approximately 10 minutes after you start transaction MB5B. The stock for

this material before this posting has already been determined. The new MM

document is also read, however. The stock read before the posting is used

as the basis for calculating the stocks for the start and end date.

If you execute transaction MB5B during a time when no goods movements are

posted, these incorrect results do not occur.

Solution

The SAP standard release does not include a solution that allows you to

process mass data using transaction MB5B. The requirements for transaction

MB5B are very customer-specific. To allow for these customer-specific

requirements, we provide the following proposed implementation:

Implementation proposal:

o You should call transaction MB5B for only one "material + plant"

combination at a time.

o The list outputs for each of these runs are collected and at the

end of the processing they are prepared for a large list output.

You need three reports and one database table for this function. You can

store the lists in the INDX cluster table.

o Define work database table ZZ_MB5B with the following fields:

- Material number

- Plant

- Valuation area

- Key field for INDX cluster table

o The size category of the table should be based on the number of

entries in material valuation table MBEW.

Report ZZ_MB5B_PREPARE

In the first step, this report deletes all existing entries from the

ZZ_MB5B work table and the INDX cluster table from the last mass data

processing run of transaction MB5B.

o The ZZ_MB5B work table is filled in accordance with the selected

mode of transaction MB5B:

- Stock type mode = Valuated stock

- Include one entry in work table ZZ_MB5B for every "material +

valuation area" combination from table MBEW.

o Other modes:

- Include one entry in work table ZZ_MB5B for every "material +

plant" combination from table MARC

Furthermore, the new entries in work table ZZ_MB5B are assigned a unique

22-character string that later serves as a key term for cluster table INDX.

Report ZZ_MB5B_MONITOR

This report reads the entries sequentially in work table ZZ_MB5B. Depending

on the mode of transaction MB5B, a lock is executed as follows:

o Stock type mode = Valuated stock

For every "material + valuation area" combination, the system

determines all "material + plant" combinations. All determined

"material + plant" combinations are locked.

o Other modes:

- Every "material + plant" combination is locked.

- The entries from the ZZ_MB5B work table can be processed as

follows only if they have been locked successfully.

- Start report RM07MLBD for the current "Material + plant"

combination, or "material + valuation area" combination,

depending on the required mode.

- The list created is stored with the generated key term in the

INDX cluster table.

- The current entry is deleted from the ZZ_MB5B work table.

- Database updates are executed with COMMIT WORK AND WAIT.

- The lock is released.

- The system reads the next entry in the ZZ_MB5B work table.

Application

- The lock ensures that no goods movements can be posted during

the runtime of the RM07MLBD report for the "material + Plant"

combination to be processed.

- You can start several instances of this report at the same

time. This method ensures that all "material + plant"

combinations can be processed at the same time.

- The system takes just a few seconds to process a "material +

Plant" combination so there is just minimum disruption to

production operation.

- This report is started until there are no more entries in the

ZZ_MB5B work table.

- If the report terminates or is interrupted, it can be started

again at any time.

Report ZZ_MB5B_PRINT

You can use this report when all combinations of "material + plant", or

"material + valuation area" from the ZZ_MB5B work table have been

processed. The report reads the saved lists from the INDX cluster table and

adds these individual lists to a complete list output.

Estimated implementation effort

An experienced ABAP programmer requires an estimated three to five days to

create the ZZ_MB5B work table and these three reports. You can find a

similar program as an example in Note 32236: MBMSSQUA.

If you need support during the implementation, contact your SAP consultant.

Header Data

Release Status: Released for Customer

Released on: 05.12.2006 16:14:11

Priority: Recommendations/additional info

Category: Consulting

Main Component MM-IM-GF-REP IM Reporting (no LIS)

The note is not release-dependent.

Thanks in advance.

Edited by: Neliea on Jan 9, 2008 10:38 AM

Edited by: Neliea on Jan 9, 2008 10:39 AM

Re: URGENT------MB5B : PERFORMANCE PROBLEM

Former Member — Wed, 09 Jan 2008 15:46:14 GMT

before you try any of this try working with database-hints as described in note 921165, 902157, 918992

topic URGENT------MB5B : PERFORMANCE PROBLEM in Application Development and Automation Discussions

URGENT------MB5B : PERFORMANCE PROBLEM

Re: URGENT------MB5B : PERFORMANCE PROBLEM