Projects:RTVS Performance Refactor

About this document

This document describes the performance improvements to be executed over the Return To Vendor Shipment Pick&Execute in order to properly perform in high volume environments

Introduction

The Return to Vendor Shipment P&E is built on top of the m_rm_shipment_pick_edit database view. For small/medium size environments, the view seems to perform properly, however in environment with high volume, specially for the business partners with lots of orders and shipments, the view doesn't perform well. In a real customer environment, for a concrete business partner, it takes around 25 seconds to show the results.

Several bugs were reported on this topic in the past, that were fixed by improving the HQL where clause defined into the tab. However, there is no more room for improvements there, and we are forced to modify the database view.

• 34612: Performance problem in Return To Vendor Shipment
• Test Plan

Explanation of the problem

The plan calculated by the database for this view is the following:

1. It takes all the order lines belonging to this business partner (in other planner executions it filters by the return orders first)
2. For each of these lines, it calculates the pending quantity to be returned. This is done by a sub-select over the m_inout and m_inoutline (iol3 in the view definition).
3. It joins each of the order lines with a record in m_storage_detail (high volume table) to know the qty on hand for its product.
4. Then it joins each of the lines with the shipments not completed (iol in the view), to get the movement quantity and to set the ob_selected flag.
5. For each of the lines, it joins with the m_product to get the isstocked flag
6. The join described in point 2 is executed again to filter out the rows with something pending to be returned.
7. Finally it sorts the result

There are 2 main problems in this plan:

1. We need to calculate on the fly the quantity pending to be returned for each of the order lines. That includes orders already fully returned, and according to the previous plan, even “normal” orders.
2. If the number of order lines and shipment lines is too high for a concrete business partner (vendor), from one side the calculation of the quantity is too slow, and from the other side we need to join more records to the m_storage_detail, which is a heavy table.

Proposed solution

The first thing we need to try is to avoid calculating on the fly the quantity pending to be returned. Instead we should be using a physical column in the database (C_OrderLine table) with the current value. When the return to vendor document is processed, this value will be the same as the ordered quantity.

This column will be updated each time a return to vendor shipment document is completed or reactivated.

Right now the C_OrderLine table has a column called QtyDelivered that has implemented this logic for sales flows only. We should be doing the same thing but for the returns to vendor flow.

Let’s use the current QtyDelivered instead of creating a new column.

It should be necessary to create a module script to populate this column in current instances for just one time. That will impact in the high volume instances update process.

Besides, we should mark the return order as fully returned when all the quantities in the lines have been returned. It should be done at the same time we update the quantity pending to return in the lines.

This flag will allow the db planner to drastically reduces the number of return orders to check to the ones with fully returned flag = 'N'

The same module script described above should be in charge of populating this column for existing instances too.

In C_Order table there is a column called IsDelivered. As far as I can see, it is not fully implemented. Only really used in two places:

• In C_Order_POST. Set to Y only for documents: “On Credit Order”, “POS Order” and “Warehouse Order”. Main reason for this could be that these types of documents generate the shipment at the order creation, so it's very simple to control that flag.
• In procedure M_InOut_Create (Generate Shipments) to filter out orders already delivered

As part of this project, we will implement the logic to populate the isDelivered flag for all the sales flows and the return to vendor. This field will be now shown in sales order header too.

Other minor modifications:

• Avoid to join with M_Product table. This is only done to get the product's IsStocked flag, which is neither displayed in the grid nor used in the P&E process
• There is a useless join with M_Locator table. The planner seems to detect it, but let's remove it too from the view definition.
• Implement an index in C_DocType.IsReturn to avoid sequential scans on this table