Projects:Selector Performance Improvements/Specs

Functional Requirements

The main functional requirement of this project is to improve the time spent by the selectors in general to show their information. Also, it will be an effort to try to improve the Product (by Price and Warehouse) selector performance in particular.

This project will not add new features, the changes involved within this project should be transparent for selectors: they must continue working as usual.

The objective of this project is to improve selector performance in midsized environments. For example, the test plan executed for the product selector along this project has been performed in an environment with 100.000 products. More information about the volumes used for testing this project can be found in the QA-Test Plan section.

Technical Specs

General Performance Improvements

There are some general improvements that will be applied for the selectors infrastructure itself. They all help to reduce the time spent to calculate the information returned by the selector datasource in different scenarios.

Apply Filter by ID for Default Selector Filters

It is possible to configure selectors to have a default filtering when their pop-up is opened. This helps to reduce the number of records that are retrieved and in general, the execution time of the related query. This default filtering can be removed later by clicking on the funnel icon.

The default filtering criteria is sent from the client to an action handler, SelectorDefaultFilterActionHandler, which evaluates the filter expressions and returns them into a JSON which is interpreted by the selectors to set the filters on their grid shown in the pop-up.

Product (by Price and Warehouse) Selector Default Filter

With this improvement, when filtering by a foreign key, this handler will built the expression to force filter by ID on the selector grid, i.e., the criteria that will be finally sent to the datasource will use the ID of the foreign key and the equals operator.

The infrastructure that allows filtering by ID was introduced with the Foreign Key Filtering Performance Improvement project, so we are finally taking advantage of its implementation.

Use Selected Properties for the Drop-Down

Currently all the fields defined for the selector are requested when using the selector pick list (drop-down). But most of them are not used in this case.

Product (by Price and Warehouse) Selector Drop-Down

The following fields are the ones that should be requested always by the drop-down:

• Value Field
• Display Field
• Pick-List Fields
• Out Fields

With this improvement the drop-down will just request those fields, preventing possible unneeded joins in the datasource. So, this type of requests will include two new parameters:

• SELECTED_PROPERTIES: include the names of the fields that are marked to be displayed on the pick-list.
• EXTRA_PROPERTIES: include the value field, display fields and out fields.

Using the SELECTED_PROPERTIES we will use a feature already present in the datasources infrastructure, which allows to select just the properties specified in that parameter.

The reason to make use of the EXTRA_PROPERTIES parameters also is because it is always defined in the window view, according to the selector definition. So, it must be overridden to retrieve just the mentioned fields.

Unneeded Query To Compute New Start Row

The DefaultJsonDataService is the datasource used by the most part of the selectors in order to retrieve their information. When a request is done to this class having a selected record, it implements a mechanism to retrieve the information in a page leaving the selected record in the middle of that page.

The selected record ID is sent within the request to the datasource, using the TARGETRECORDID_PARAMETER. The DefaultJsonDataService is performing the query to calculate the start row position of the page every time that the request contains this parameter.

This calculation is not needed if the value of the parameter is empty or null. So adding this check we will be computing the value for the new start row just when it is strictly necessary.

Use Inner Join for Sorting

For those entities joined for sorting, the query performed by the datasource should use an inner join instead of a left join, when possible. This is because a left join sometimes avoids the usage of indexes when sorting. This does not happen with inner joins.

A new check will be included before joining a table which contains the column used for sorting. If it is safe to replace left join with inner join, i.e., the column in the left table is mandatory, then the inner join will be taken instead of the left join.

Prevent Usage of Concatenated IDs for Predictable sorting

In order to ensure a predictable sorting, the DefaultJsonDataService appends into the sort statement of the query, the primary key column of the datasource main entity.

This have a performance impact in some views. If the primary key of that view is the result of a concatenation of several columns which belongs to different tables, like the M_PRODUCT_PRICE_WAREHOUSE_V view, that column will not be indexable, so the query will be significantly slower when using it for sorting.

To avoid this problem in predictable sorting for this kind of views, it will not be used the primary key of the view but the column related to the value field defined for the selector, if exists.

Performance Improvements For Product Selector

Together with the general improvements, the Product (by Price and Warehouse) selector requires some particular changes. This selector has performance problems in environments with a lot of records in the following tables: M_PRODUCT, M_PRODUCTPRICE, M_WAREHOUSE, M_LOCATOR, M_STORAGE_DETAIL and M_STORAGE_PENDING.

The changes will be focused on improving the query executed by the selector, which makes use of the M_PRODUCT_PRICE_WAREHOUSE_V view.

This changes will be decided after a first research stage in order to find out the most suitable solution.

Researching Stage

The main disadvantage of the current implementation of the M_PRODUCT_PRICE_WAREHOUSE_V is a left join that it has with another view, M_PRODUCT_WAREHOUSE_QTYS_V used to retrieve different quantities (available, on hand, reserved and ordered). The main idea is try to replace this join and calculate this quantities in a different way.

The following solutions will be considered during this step:

• Solution 1: Use a computed column for every quantity.
• Solution 2: Use a sub-query to compute all quantities inside the M_PRODUCT_PRICE_WAREHOUSE_V view itself.

In both cases, the quantities will be calculated just for every record present in the page requested by the selector.

View Refactoring Stage

Both approaches mentioned in previous section were improving the selector performance. The main problem with Solution 1 is that hibernate is appending a WHERE clause with the IDs of the records whose computed column are going to be calculated. As we have mentioned above, the ID of the view is not indexable, and for this reason the query is slower than the one finally generated with Solution 2.

In a different scenario, where the ID of the view/table used by the selector can be indexed, Solution 1 would be a good solution.

Hence the chosen solution consists of replacing the left join with M_PRODUCT_WAREHOUSE_QTYS_V view by sub-queries that computes the quantities. Thus, the current implementation of the M_PRODUCT_PRICE_WAREHOUSE_V view:

 
CREATE OR REPLACE VIEW m_product_price_warehouse_v AS 
 SELECT COALESCE(w.m_warehouse_id, '-'::character varying)::text || pp.m_productprice_id::text AS m_product_price_warehouse_v_id,
    p.ad_client_id,
    p.ad_org_id,
    p.isactive,
    p.updated,
    p.updatedby,
    p.created,
    p.createdby,
    p.m_product_id,
    w.m_warehouse_id,
    pp.m_productprice_id,
        CASE
            WHEN p.producttype::text <> 'I'::text OR p.isstocked = 'N'::bpchar THEN 99999::numeric
            ELSE COALESCE(pwq.qtyonhand - pwq.qtyreserved, 0::numeric)
        END AS qty_available,
        CASE
            WHEN p.producttype::text <> 'I'::text OR p.isstocked = 'N'::bpchar THEN 99999::numeric
            ELSE COALESCE(pwq.qtyonhand, 0::numeric)
        END AS qty_onhand,
        CASE
            WHEN p.producttype::text <> 'I'::text OR p.isstocked = 'N'::bpchar THEN 0::numeric
            ELSE COALESCE(pwq.qtyreserved, 0::numeric)
        END AS qty_reserved,
        CASE
            WHEN p.producttype::text <> 'I'::text OR p.isstocked = 'N'::bpchar THEN 0::numeric
            ELSE COALESCE(pwq.qtyordered, 0::numeric)
        END AS qty_ordered,
    pp.pricelist,
    pp.pricestd,
    pp.pricelimit,
    COALESCE(w.ad_org_id, '0'::character varying) AS orgwarehouse
  FROM m_productprice pp
     LEFT JOIN m_warehouse w ON pp.ad_client_id::text = w.ad_client_id::text
     LEFT JOIN m_product p ON p.m_product_id::text = pp.m_product_id::text
     LEFT JOIN m_product_warehouse_qtys_v pwq ON pwq.m_product_id::text = pp.m_product_id::text AND pwq.m_warehouse_id::text = w.m_warehouse_id::text
  WHERE p.isgeneric = 'N'::bpchar AND pp.isactive = 'Y'::bpchar AND w.isactive = 'Y'::bpchar AND p.isactive = 'Y'::bpchar;

Will be replaced with this one:

 
CREATE OR REPLACE VIEW m_product_price_warehouse_v AS 
 SELECT COALESCE(w.m_warehouse_id, '-'::character varying)::text || pp.m_productprice_id::text AS m_product_price_warehouse_v_id,
    p.ad_client_id,
    p.ad_org_id,
    p.isactive,
    p.updated,
    p.updatedby,
    p.created,
    p.createdby,
    p.m_product_id,
    w.m_warehouse_id,
    pp.m_productprice_id,
        CASE
            WHEN p.producttype::text <> 'I'::text OR p.isstocked = 'N'::bpchar THEN 99999::numeric
            ELSE COALESCE(( SELECT sum(sdd.qtyonhand) AS sum
               FROM m_storage_detail sdd
                 JOIN m_locator l ON sdd.m_locator_id::text = l.m_locator_id::text
               WHERE sdd.m_product_id::text = p.m_product_id::text AND l.m_warehouse_id::text = w.m_warehouse_id::text), 
               0::numeric) - 
                 COALESCE(( SELECT sum(sp.qtyreserved) AS sum
               FROM m_storage_pending sp
               WHERE sp.m_product_id::text = p.m_product_id::text AND sp.m_warehouse_id::text = w.m_warehouse_id::text), 
               0::numeric)
        END AS qty_available,
        CASE
            WHEN p.producttype::text <> 'I'::text OR p.isstocked = 'N'::bpchar THEN 99999::numeric
            ELSE COALESCE(( SELECT sum(sdd.qtyonhand) AS sum
               FROM m_storage_detail sdd
                 JOIN m_locator l ON sdd.m_locator_id::text = l.m_locator_id::text
               WHERE sdd.m_product_id::text = p.m_product_id::text AND l.m_warehouse_id::text = w.m_warehouse_id::text), 
               0::numeric)
        END AS qty_onhand,
        CASE
            WHEN p.producttype::text <> 'I'::text OR p.isstocked = 'N'::bpchar THEN 0::numeric
            ELSE COALESCE(( SELECT sum(sp.qtyreserved) AS sum
               FROM m_storage_pending sp
               WHERE sp.m_product_id::text = p.m_product_id::text AND sp.m_warehouse_id::text = w.m_warehouse_id::text), 
               0::numeric)
        END AS qty_reserved,
        CASE
            WHEN p.producttype::text <> 'I'::text OR p.isstocked = 'N'::bpchar THEN 0::numeric
            ELSE COALESCE(( SELECT sum(sp.qtyordered) AS sum
               FROM m_storage_pending sp
               WHERE sp.m_product_id::text = p.m_product_id::text AND sp.m_warehouse_id::text = w.m_warehouse_id::text), 
               0::numeric)
        END AS qty_ordered,
    pp.pricelist,
    pp.pricestd,
    pp.pricelimit,
    COALESCE(w.ad_org_id, '0'::character varying) AS orgwarehouse
  FROM m_productprice pp
     LEFT JOIN m_warehouse w ON pp.ad_client_id::text = w.ad_client_id::text
     LEFT JOIN m_product p ON p.m_product_id::text = pp.m_product_id::text
  WHERE p.isgeneric = 'N'::bpchar AND pp.isactive = 'Y'::bpchar AND w.isactive = 'Y'::bpchar AND p.isactive = 'Y'::bpchar;