[OFBIZ-3856] quicker picklist option functionality

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: Release 4.0, Release 09.04, Trunk, Upcoming Branch
• Component/s: product
• Sprint: Bug Crush Event - 21/2/2015
• Assignee: Unassigned
• Reporter: Albert Mayo
• Created: 12/Jul/10 15:27
• Updated: 11/Jan/22 08:48

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.