PicklistOptions processing time

[mayo]

Has anyone else noticed the amount of time it could take to load the PicklistOptions view for larger batches of orders and a larger database history?

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.

I am using OFBiz 4.0. Has this already been fixed in the trunk version?  If not, I have developed a faster picklist process for my copmany that makes minimal hits to the database and utilizes better prepared SQL statements to get the same result. It may be a nice foundation to a substantially improved picklist process for the trunk version. Would anyone be interested in this code?  If so, should I create a Jira ticket and attach the code?

[Jacques Le Roux]

Hi Albert,

When you say "better prepared SQL statements" do you mean that you have not used the Entity Engine?

Jacques

From: "mayo" <[hidden email]>

> Has anyone else noticed the amount of time it could take to load the
> PicklistOptions view for larger batches of orders and a larger database
> history?
>
> 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.
>
> I am using OFBiz 4.0. Has this already been fixed in the trunk version?  If
> not, I have developed a faster picklist process for my copmany that makes
> minimal hits to the database and utilizes better prepared SQL statements to
> get the same result. It may be a nice foundation to a substantially improved
> picklist process for the trunk version. Would anyone be interested in this
> code?  If so, should I create a Jira ticket and attach the code?
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/PicklistOptions-processing-time-tp2280129p2280129.html
> Sent from the OFBiz - Dev mailing list archive at Nabble.com.
>

[mayo]

No, I used DynamicViewEntity to make more complex SQL to more quickly filter out any orders that should not be picked.  For instance in OFBiz 4.0 (and maybe the trunk version), the picklistoptions function will (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 yesterday took ~4 minutes for 40 pickable orders.

My code uses DynamicViewEntity to create join statements and make minimal hits to the database--I think a total of 4 hits.  For the same 40 pickable orders, the process ran in 2-3 seconds.  My code is missing some of the bells and whistles that you have, but it gives a good foundation and structure.  You will be able to add all the bells and whistles very quickly.

My company is using my code in production right now.  The user has verified it is working correctly to this point.  Let me know if anyone is interested in seeing the code.  I would like to contribute to the community is some way, since I do not have time to properly develop on the trunk.

[J. Eckard-2]

We've done the same thing here, but using a view entity instead of a  
dynamic entity. Ours uses 3 queries, 1 to find orders ready to pick, 1  
to find those already on a picklist (subtracted from the first list)  
and 1 to find those that need stock moves (also subtracted from the  
original list).

I would be interested to see your approach.

-Joe

On Jul 7, 2010, at 9:59 AM, mayo wrote:

>
> No, I used DynamicViewEntity to make more complex SQL to more  
> quickly filter
> out any orders that should not be picked.  For instance in OFBiz 4.0  
> (and
> maybe the trunk version), the picklistoptions function will  
> (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  
> yesterday took
> ~4 minutes for 40 pickable orders.
>
> My code uses DynamicViewEntity to create join statements and make  
> minimal
> hits to the database--I think a total of 4 hits.  For the same 40  
> pickable
> orders, the process ran in 2-3 seconds.  My code is missing some of  
> the
> bells and whistles that you have, but it gives a good foundation and
> structure.  You will be able to add all the bells and whistles very  
> quickly.
>
> My company is using my code in production right now.  The user has  
> verified
> it is working correctly to this point.  Let me know if anyone is  
> interested
> in seeing the code.  I would like to contribute to the community is  
> some
> way, since I do not have time to properly develop on the trunk.
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/PicklistOptions-processing-time-tp2280129p2280947.html
> Sent from the OFBiz - Dev mailing list archive at Nabble.com.

[J. Eckard-2]

We've done the same thing here, but using a view entity instead of a  
dynamic entity. Ours uses 3 queries, 1 to find orders ready to pick, 1  
to find those already on a picklist (subtracted from the first list)  
and 1 to find those that need stock moves (also subtracted from the  
original list).

I would be interested to see your approach.

-Joe

On Jul 7, 2010, at 9:59 AM, mayo wrote:

>
> No, I used DynamicViewEntity to make more complex SQL to more  
> quickly filter
> out any orders that should not be picked.  For instance in OFBiz 4.0  
> (and
> maybe the trunk version), the picklistoptions function will  
> (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  
> yesterday took
> ~4 minutes for 40 pickable orders.
>
> My code uses DynamicViewEntity to create join statements and make  
> minimal
> hits to the database--I think a total of 4 hits.  For the same 40  
> pickable
> orders, the process ran in 2-3 seconds.  My code is missing some of  
> the
> bells and whistles that you have, but it gives a good foundation and
> structure.  You will be able to add all the bells and whistles very  
> quickly.
>
> My company is using my code in production right now.  The user has  
> verified
> it is working correctly to this point.  Let me know if anyone is  
> interested
> in seeing the code.  I would like to contribute to the community is  
> some
> way, since I do not have time to properly develop on the trunk.
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/PicklistOptions-processing-time-tp2280129p2280947.html
> Sent from the OFBiz - Dev mailing list archive at Nabble.com.

[mayo]

Ok cool.  I did something similar.  What is the best way for me to get the code to you?

[samhamilton]

Hi Guys - we are running with a 41GB database and would love to see your
code too!

If you create a jira issue and add the patch there then we can start
working towards getting the code committed back into the project.

Cheers
Sam

On 07/07/2010 23:28, mayo wrote:
>
> Ok cool.  I did something similar.  What is the best way for me to get the
> code to you?

[mayo]

So is this a confirmed issue for the trunk version?  I only know that it is an issue for the 4.0 release.  Since this could be seen as an enhancement, I do not want to create a ticket for the 4.0 release.  If someone can confirm this is a problem in the trunk, I will create a ticket for trunk and post my code as an example of a solution.

[J. Eckard-2]

Yes, the logic is basically the same in trunk, with some additional  
grouping options.


On Jul 12, 2010, at 10:12 AM, mayo wrote:

>
> So is this a confirmed issue for the trunk version?  I only know  
> that it is
> an issue for the 4.0 release.  Since this could be seen as an  
> enhancement, I
> do not want to create a ticket for the 4.0 release.  If someone can  
> confirm
> this is a problem in the trunk, I will create a ticket for trunk and  
> post my
> code as an example of a solution.
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/PicklistOptions-processing-time-tp2280129p2286163.html
> Sent from the OFBiz - Dev mailing list archive at Nabble.com.

[mayo]

I have created the ticket and attached my files:
https://issues.apache.org/jira/browse/OFBIZ-3856