OFBiz
  1. OFBiz
  2. OFBIZ-3856

quicker picklist option functionality

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: Release 4.0, Release 09.04, SVN trunk
    • Fix Version/s: None
    • Component/s: product
    • Labels:
      None

      Description

      I am noticing as our OFBiz database grows larger, it is taking the PicklistOptions longer to load. Even for a batch as small as 50 orders it can take up to a couple minutes to load. 300 orders can take upwards of 5-10 minutes. My development system with much lesser hardware can get through a hundred orders in seconds, so I have a feeling it has to do with the process/code inefficiency.

      For instance, the picklistoptions function does the following (abbreviated):

      select * from OrderHeader
      for each orderHeader {
      select * from OrderItemShipGroup where orderId = orderHeader.orderId
      for each orderItemShipGroup in orderItemShipGroupList {
      select * from OrderItemShipGrpInvRes where orderId = orderItemShipGroup.orderId;
      for each orderItemShipGrpInvRes in orderItemShipGrpInvResList

      { etc.... }

      }
      }

      These kind of loops can create many hits to the database and greatly slow down the process, especially if the database tables have many records. Our database is now almost 8 gigs. The PicklistOptions process took ~4 minutes for 40 pickable orders.

      I am using OFBiz 4.0, but the trunk version has a lot of the same code. I am opening this ticket to start a conversation of a quicker method to create picklists, like with the use of views or DynamicViewEntitys.

      1. entitymodel.xml
        3 kB
        Joe Eckard
      2. FindOrdersToPick.groovy
        4 kB
        Joe Eckard
      3. quicker picklist.diff
        12 kB
        Albert Mayo
      4. PickListServices.java
        31 kB
        Albert Mayo

        Activity

        Hide
        Albert Mayo added a comment - - edited

        Attached is PickListServices.java with 3 new functions that are used to execute the PicklistOptions functionality more quickly in my implementation of OFBiz 4.0:

        getPickableOrders() - gets a list of pickable orders and related information, used by other 2 functions
        createPicklistAndBins() - creates picklists
        findOrdersToPick() - returns a count of pickable orders and stock moves

        Also attached is a unified diff of how I integrated these functions to work with the OFBiz framework and UI. You will notice my implementation does not remove the original PicklistOptions view. This was to verify the new process worked like the original and to compare the execute times.

        Please note:
        This is does not have all the functionality of the current PicklistOptions view but has the core functionality we use for the picklist process. The attached picklist logic was designed to match OFBiz's original logic except for a minor change: an order can reappear on a picklist if the order is not completed and the picklist was marked as "picked".

        This code is being used currently in our production environment and is working. Picklist counts can take 2 seconds where the original code will take 4-5 minutes.

        Show
        Albert Mayo added a comment - - edited Attached is PickListServices.java with 3 new functions that are used to execute the PicklistOptions functionality more quickly in my implementation of OFBiz 4.0: getPickableOrders() - gets a list of pickable orders and related information, used by other 2 functions createPicklistAndBins() - creates picklists findOrdersToPick() - returns a count of pickable orders and stock moves Also attached is a unified diff of how I integrated these functions to work with the OFBiz framework and UI. You will notice my implementation does not remove the original PicklistOptions view. This was to verify the new process worked like the original and to compare the execute times. Please note: This is does not have all the functionality of the current PicklistOptions view but has the core functionality we use for the picklist process. The attached picklist logic was designed to match OFBiz's original logic except for a minor change: an order can reappear on a picklist if the order is not completed and the picklist was marked as "picked". This code is being used currently in our production environment and is working. Picklist counts can take 2 seconds where the original code will take 4-5 minutes.
        Hide
        Joe Eckard added a comment -

        I'm also going to attach a slightly different version of a 'findOrdersToPick' service. I was hesitant to create a patch without a good set of test cases, since this version has not been extensively tested but it may be useful and relevant to this issue. (This also returns different data than is expected from the existing service so it is not a drop-in replacement)

        Show
        Joe Eckard added a comment - I'm also going to attach a slightly different version of a 'findOrdersToPick' service. I was hesitant to create a patch without a good set of test cases, since this version has not been extensively tested but it may be useful and relevant to this issue. (This also returns different data than is expected from the existing service so it is not a drop-in replacement)
        Hide
        Albert Mayo added a comment - - edited

        I like the simplicity of your solution. I may have taken some unneeded steps in attempt to minimize the number of loops – for instance my solution will loop through orders with a previous history of canceled/finished picklists to see if they need to be added again, versus looping through all approved orders. My method may be unnecessary since both situations would be completed quickly, even with thousands of approved orders.

        Here are some logical points that I considered while I was programming and testing. These may help with test cases when someone attempts to integrate a solution:

        • Joining to the picklist tables for the views can add to the count results if an order has multiple picklists in its history. To test, create and cancel a picklist with the same order multiple times. When the order is available to be picked again, be sure the count does not increase.
        • If an order can be split and an item is partially backordered, should the partially available inventory be added to the picklist? My solution adds the item to the picklist with the available quantity. I believe OFBiz 4.0 does as well. Example: If someone orders a quantity of 3 of an item and a quantity of 1 is on backorder and maySplit equals Yes, then when a picklist is created for the order this particular item should have a pick for a quantity of 2.
        • Continuing the last example, if maySplit equals No, then the complete order should not be put on a picklist. Testing should be done to ensure partial backorders are filtered out of picklist availability when maySplit equals No.
        • testing should ensure the oldest orders are added to the picklist first
        • testing should ensure that if an order has any item in a Bulk location, that it is added once under the "Need stock move" column and that it is not added under the "Ready to pick" column. Similarly, if a picklist is created and an order has any item in a Bulk location, then no items from order should not be put into a picklist.
        • Test case:
          A splittable order with 2 items has 1 of its items on backorder. A picklist is created and the 1 available item is put on a picklist. Then the backordered item becomes available, allowing the 2nd item of the order to be picked. What should happen when a picklist is created now? In OFBiz 4.0 I believe it will create a new picklist with the order and 2 of its items, duplicating the one item on the original picklist. My solution will only add the recently backordered item to the new picklist. I do not know what is best for most warehouse situations.

        Hope this helps someone in the future.

        Show
        Albert Mayo added a comment - - edited I like the simplicity of your solution. I may have taken some unneeded steps in attempt to minimize the number of loops – for instance my solution will loop through orders with a previous history of canceled/finished picklists to see if they need to be added again, versus looping through all approved orders. My method may be unnecessary since both situations would be completed quickly, even with thousands of approved orders. Here are some logical points that I considered while I was programming and testing. These may help with test cases when someone attempts to integrate a solution: Joining to the picklist tables for the views can add to the count results if an order has multiple picklists in its history. To test, create and cancel a picklist with the same order multiple times. When the order is available to be picked again, be sure the count does not increase. If an order can be split and an item is partially backordered, should the partially available inventory be added to the picklist? My solution adds the item to the picklist with the available quantity. I believe OFBiz 4.0 does as well. Example: If someone orders a quantity of 3 of an item and a quantity of 1 is on backorder and maySplit equals Yes, then when a picklist is created for the order this particular item should have a pick for a quantity of 2. Continuing the last example, if maySplit equals No, then the complete order should not be put on a picklist. Testing should be done to ensure partial backorders are filtered out of picklist availability when maySplit equals No. testing should ensure the oldest orders are added to the picklist first testing should ensure that if an order has any item in a Bulk location, that it is added once under the "Need stock move" column and that it is not added under the "Ready to pick" column. Similarly, if a picklist is created and an order has any item in a Bulk location, then no items from order should not be put into a picklist. Test case: A splittable order with 2 items has 1 of its items on backorder. A picklist is created and the 1 available item is put on a picklist. Then the backordered item becomes available, allowing the 2nd item of the order to be picked. What should happen when a picklist is created now? In OFBiz 4.0 I believe it will create a new picklist with the order and 2 of its items, duplicating the one item on the original picklist. My solution will only add the recently backordered item to the new picklist. I do not know what is best for most warehouse situations. Hope this helps someone in the future.

          People

          • Assignee:
            Unassigned
            Reporter:
            Albert Mayo
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development