Performance issue about "Generate Pick Pack List" on view shipment page

Hi everyone,

I’m Shangwei Yin, the tech lead of the ThoughtWorks SIGLUS team.
We found a performance issue about “generate pick pack list” pdf.

Scenario

Login and go to “Fulfill Orders” list, select an order and start fulfillment. In “View Shipment” page, fill number and click “Save Draft”, after draft saved then click “Generate Pick Pack List”, wait for a while and 504 Gateway Time-out error will happen(Our Nginx timeout is 60s).


Root Cause

After check the source code in openlmis-report service, we found a large SQL will executed when click “Generate Pick Pack List”.
We tested and executed the SQL directly, and it cost 203s, as the cost time is more than 60s, so gateway time-out error will happen.


The SQL is:

SELECT DISTINCT
  order_orderable.code                  AS orderProductCode,
  order_orderable.fullproductname       AS orderProductName,
  orderable.code                        AS productCode,
  orderable.fullproductname             AS productName,
  orderable.netContent                  AS netContent,
  shipment_li.quantityshipped           AS filledQuantity,
  lot.lotcode                           AS lotCode,
  lot.expirationDate                    AS expirationDate,
  stock_card_li.extraData->>'vvmStatus' AS vvmStatus
FROM
  fulfillment.shipment_drafts shipment
  JOIN
  fulfillment.orders ord
    ON ord.id = shipment.orderid
  JOIN
  fulfillment.shipment_draft_line_items shipment_li
    ON shipment_li.shipmentdraftid = shipment.id
  JOIN
  referencedata.orderables orderable
    ON orderable.id = shipment_li.orderableid
    AND orderable.versionnumber = shipment_li.orderableversionnumber
  JOIN
  referencedata.dispensables dispensable
    ON dispensable.id = orderable.dispensableid
  LEFT JOIN referencedata.orderable_identifiers shipment_orderable_identifier ON orderable.id = shipment_orderable_identifier.orderableid
                                                       AND (shipment_orderable_identifier.key = 'tradeItem' OR shipment_orderable_identifier.key = 'commodityType')
  LEFT JOIN
  fulfillment.order_line_items order_li
    ON order_li.orderid = ord.id AND (shipment_li.orderableid = order_li.orderableid AND shipment_li.orderableversionnumber = order_li.orderableversionnumber OR order_li.orderableid IN
    (
      SELECT
        o.id
      FROM
        referencedata.orderables o
        LEFT JOIN
        referencedata.orderable_identifiers order_orderable_identifier
          ON o.id = order_orderable_identifier.orderableid
             AND (order_orderable_identifier.key = 'tradeItem' OR order_orderable_identifier.key = 'commodityType')
        LEFT JOIN
        referencedata.trade_item_classifications trade_item_classification
          ON trade_item_classification.tradeitemid = order_orderable_identifier.value::uuid
        LEFT JOIN
        referencedata.commodity_types commodity_type
          ON order_orderable_identifier.value::uuid = commodity_type.id
             OR (commodity_type.classificationid = trade_item_classification.classificationid AND commodity_type.classificationsystem = trade_item_classification.classificationsystem)
        LEFT JOIN
        referencedata.trade_items trade_item
          ON order_orderable_identifier.value::uuid = trade_item.id
             OR EXISTS
             (
                 SELECT
                   1
                 FROM
                   referencedata.trade_item_classifications trade_item_classification
                 WHERE
                   trade_item_classification.tradeitemid = trade_item.id
                   AND trade_item_classification.classificationid = commodity_type.classificationid
                   AND trade_item_classification.classificationsystem = commodity_type.classificationsystem
             )
      WHERE
        NOT EXISTS
        (
            SELECT
              1
            FROM
              referencedata.dispensable_attributes orderer_dispensable_attribute
              JOIN
              referencedata.dispensable_attributes shipment_dispensable_attribute
                ON shipment_dispensable_attribute.dispensableid = dispensable.id
            WHERE
              orderer_dispensable_attribute.dispensableid = o.dispensableid
              AND orderer_dispensable_attribute.key = shipment_dispensable_attribute.key
              AND orderer_dispensable_attribute.value <> shipment_dispensable_attribute.value
        )
        AND (shipment_orderable_identifier.value::uuid = trade_item.id OR shipment_orderable_identifier.value::uuid = commodity_type.id)
    ))
  LEFT JOIN
  referencedata.lots lot
    ON shipment_li.lotid = lot.id
  LEFT JOIN
  referencedata.orderables order_orderable
    ON order_orderable.id = order_li.orderableid
    AND order_orderable.versionnumber = order_li.orderableversionnumber
  LEFT JOIN
  stockmanagement.stock_cards stock_card
    ON (stock_card.facilityId = ord.supplyingFacilityId AND stock_card.programId = ord.programId AND shipment_li.orderableId = stock_card.orderableId AND ((shipment_li.lotId IS NULL AND stock_card.lotId IS NULL) OR shipment_li.lotId = stock_card.lotId))
  LEFT JOIN
  stockmanagement.stock_card_line_items stock_card_li
    ON stock_card_li.stockCardId = stock_card.id
WHERE
  shipment.id = '9fbb3fd5-b5d8-44a6-ba74-6ad4b7731d64'
  AND shipment_li.quantityshipped > 0
  AND stock_card_li.id IN
  (SELECT id
   FROM stockmanagement.stock_card_line_items
   WHERE stockCardId = stock_card.id
   ORDER BY occurredDate DESC NULLS LAST, processedDate DESC NULLS LAST
   LIMIT 1)
ORDER BY
  orderProductCode ASC,
  productCode ASC,
  expirationDate ASC NULLS FIRST,
  vvmStatus DESC NULLS LAST,
  lotCode DESC;

Question

I would like to ask, can the OpenLMIS community do some performance optimization for this long-time sql, or any good suggestions can be given to us?

Thanks,
Shangwei Yin

Thank you, @yinshangwei, for the detailed analysis of this situation! @Klaudia_Palkowska, can you have someone from the SolDevelo team look into this and see if a fix can be scheduled soon?

@yinshangwei We’ve checked this on the UAT server and it takes about 20s so I have a few questions which would help us to investigate the issue further.

  1. Are you using the latest versions of OpenLMIS services (the most important - stock management)?
  2. How many products do you have on View Shipment screen when generating the report? Based on the screenshot it looks like 3 but I wanted to be sure.

@ibewes We will try to include it in the next sprint if it has high priority and we’ll have enough time/resources.

@Klaudia_Palkowska Thank you.

  1. We are using OpenLMIS v3.9, the stockmanagement service version is 5.0.2.
  2. On view shipment screen, it has more than 3 products, but only 3 products have SOH.

And I did some performance analysis about this sql, it seems like the the sub-query on LEFT JOIN referencedata.trade_items cost most of time.
If I remove this sub-query, the cost-time reduced from 87.8 seconds to less than 1 second.
Please take a look at the capture below. By the way, this is another shipment, has more than a dozen products, and only 7 products have SOH.
And in my case, I get the same result even I remove this sub-query, so I wonder if is it possible to make some modifications about this part.

Before:

After:

Yes, @Klaudia_Palkowska, please do try to include a fix for this in this sprint, if possible.