SAPPractical.com

1/7/2016 11:08:32 AM

All consultants (Technical and Functional) thinking to deliver the object but never think about future performance. Performance is not only slow with SD related transactions actually the system itself slowdown then customer will create high priority incidents.

To avoid Performance issue in MM and WM, below examples will give you heads up to tune our custom developments like Customer exit and BADI...

1. Accesses to transport requests

a) via the storage unit number

Incorrect:   SELECT FROM LTAP WHERE VLENR = ....  oder

  SELECT FROM LTAP WHERE NLENR = ...

Correct:

  SELECT FROM LEIN WHERE LENUM = ...

    SELECT FROM LTAP WHERE LGNUM = LEIN-LGNUM and

                      AND TANUM = LEIN-BTANR

                      AND TAPOS = LEIN-BTAPS.

b) via the SD delivery note

Incorrect:   SELECT FROM LTAP WHERE NLPLA = LIPS-VBELN

                     AND POSNR = LIPS-POSNR.Correct:

  SELECT FROM VBFA WHERE VBELV = LIPS-VBELN

                    AND POSNV = LIPS- POSNR

                    AND VBTYP_N = 'Q'.

    SELECT FROM LTAP WHERE LGNUM = LIPS-LGNUM

                      AND TANUM = VBFA-VBELN

                      AND TAPOS = VBFA-POSNN.


Remark:

This access mode only exists if the transport orders were created on account of deliveries in Sales and Distribution. In this case the respective transport orders can be determined via the document flow table VBFA.

The field LTAP-NLPLA is filled with the delivery note number via the dynamic storage bin assignment. This automatic assignment can be deactivated in WM Customizing (see movement types in WMS). This side effect must be considered when field NLPLA is used for data selection.

2. Access to material documents

a) via the purchase order number

Incorrect:    SELECT FROM MSEG WHERE EBELN = ...

                    and EBELP = ...

Correct:  SELECT FROM EKBE WHERE EBELN = ..

                 AND EBELP = ...

                AND VGABE IN (1,6,7,8,9).

     SELECT FROM MSEG WHERE MBLNR = EKBE-BELNR

                        AND MJAHR = EKBE-GJAHR

                        AND ZEILE = EKBE-BUZEI.


Remark:

The fiscal year must be specified so that the system has effective access possibilities via the primary index.If the fiscal year is missing, the database can no longer effectively use the item number for the search (this is a problem, especially for material documents with many items).If the operation type VGABE is specified, the values can be additionally restricted to the corresponding goods movements that are relevant.

b) via the transport order number

Incorrect:   SELECT FROM MSEG WHERE TANUM = ...

Correct:

  SELECT FROM LTAP WHERE TANUM = ...

    SELECT FROM MSEG WHERE MBELN = LTAP-WENUM

                      AND MJAHR = <requested year>

                      AND ZEILE = LTAP-WEPOS.

c) via transfer requirement

Incorrect:   SELECT MSEG WHERE TBNUM = ...

Correct:

  SELECT LTBK WHERE TBNUM = ....

    SELECT MSEG WHERE MBLNR = LTBK-MBLNR

                  AND MJAHR = LTBK-MJAHR.


Remark:

All items of the material document are returned. It is possible to have a restriction to one individual item of the material document by specifying the material number.

d) Via the vendor number

Incorrect:   SELECT FROM MSEG WHERE LIFNR = ...

Correct:

  SELECT EKKO WHERE LIFNR = ....

      SELECT EKBE WHERE EBELN = EKKO-EBELN

                    AND VGABE = '1'.

         SELECT MSEG WHERE  MBLNR = EKBE-BELNR

                      AND MJAHR = EKBE-GJAHR

                      AND ZEILE = EKBE-BUZEI.


Remark:

Accesses to EKKO and EKBE return several datasets under certain circumstances. This must be taken into account in the program logic.With the operation type VGABE = 1, only goods movements for purchase orders are selected.

As an alternative you can use matchcode object M_MEKKL in place of table EKKO (for example SELECT FROM M_EKKL WHERE LIFNR = ...).Access can be improved by specifying additional restrictions.The fields purchasing organization EKORG, purchasing group EKGRP, document date BEDAT, purchasing document category BSTYP, order type BSART can make the access more selective.

3. Accesses to purchase requisitions

a) via the reservation number

Incorrect:   SELECT FROM EBAN WHERE EBELN = ....

                     AND EBELP = ....

Correct:

  SELECT FROM EKET WHERE EBELN = ....

                         EBELP = ....

     SELET FROM EBAN WHERE BANFN  = EKET-BANFN

                       AND BANFPO = EKET-BANFPO.

4. Access to incoming invoices

a) via the purchase order number

Incorrect: SELECT FROM RSEG WHERE EBELN = ...

                    and EBELP = ...

Correct: SELECT FROM EKBE WHERE EBELN = ...

                    AND EBELP = ...

                    AND VGABE IN (2,3,P).

    SELECT FROM RSEG WHERE BELNR = EKBE-BELNR

                       AND GJAHR = EKBE-GJAHR

                       AND BUZEI = EKBE-BUZEI.

If you like this blog, please share (Facebook/LinkedIn/Google+) to click below links so it will reach to others.


COMMENTS

  • John Walsh -1/11/2016 3:18:42 PM

    Good post and some very good educative pointers. I suspect many fully technical readers would already be aware of the reasons for your proposed code. I guess the real issue here is what I would call LAZY SEARCHES.  I am semi-retired nowadays, but I recall that it is possible to "govern" the impact of lazy searches by setting DBMS access parameters by User (or User Group/Type) that causes any search submitted to be first analysed by the potential resource that code would consume based on the Tables and Table sizes being searched. Especially where these searches contain wildcard components. So if, as is often the case, a KEY departmental User submits a query, the system would only process the code if it does not breach the parameters governing that Users access. In the early days of "User-centric" reporting tools for Reporting and BI, these parameters made it possible for the Sys Admin team to manage resource-hungry access - usually such searches being submitted through naivity on the part of the Key-User who might usually be the departmental champion of the solution. Also, I would suggest the issue spans all functional areas not just the 2 in focus here. Maybe a little time spent with Project Team members during implementation would help to reel-in some of the need to manage by setting params on resource usage at the DBMS level, and maybe occasional refreshers around this subject matter would be time and money well spent to maintain efficient operations. The good news is that Users "can" get at their data using generic tools to create the lower level code submitted in Reporting functions. The bad news is that "technically ignorant"  access can and will drag down the overall system performance. But it can be managed, IF my recollections are correct.

  • Rajesh -1/8/2016 11:56:21 AM

    Nice post...