[jira] Updated: (OFBIZ-3856) quicker picklist option functionality

["Albert Mayo (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/OFBIZ-3856?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Albert Mayo updated OFBIZ-3856:
-------------------------------

    Attachment: PickListServices.java
                quicker picklist.diff

Attached is PickListServices.java with 3 new functions that are used to more 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.

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.

> quicker picklist option functionality
> -------------------------------------
>
>                 Key: OFBIZ-3856
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-3856
>             Project: OFBiz
>          Issue Type: Improvement
>          Components: product
>    Affects Versions: Release 4.0, Release 09.04, SVN trunk
>            Reporter: Albert Mayo
>            Priority: Minor
>         Attachments: PickListServices.java, quicker picklist.diff
>
>
> 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. 

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.