Catalog management performance issue

[Michał Cukierman]

Hi,

I have a problem with performence (and Out of Memmory Issue) with
catalog management module. There is a section invoked after
Choose Top Category

Collection<GenericValue> allCategories =
delegator.findList("ProductCategory", null, null, null, null, false);
for (GenericValue curCat: allCategories) {
        Collection<GenericValue> parentCats =
curCat.getRelatedCache("CurrentProductCategoryRollup");

 if (parentCats.isEmpty())
        results.add(curCat);
}

As we see. To get categories without parents we need to fetch for all
categories and for each of them, find a parent.

I do not know Ofbiz api very well for now, so I have implemented:
EntityWhereString where =
EntityCondition.makeConditionWhere("PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID
not in" +
" (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
PRODUCT_CATEGORY_ROLLUP)");
Set<String> fieldsToSelect = FastSet.newInstance();
                        fieldsToSelect.add("productCategoryId");
                        results.addAll(delegator.findList("ProductCategory", where,
fieldsToSelect, null, null, false));


I simply do invoke a SQL query in the database:

select PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID from PRODUCT_CATEGORY where
PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID not in
(select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
PRODUCT_CATEGORY_ROLLUP);

Is there a way ( for sure is ) to implement it in a better manner? For
now it works on my system, but after improving the query it could be
passed to a production.

So I got two questions in fact:

1) How to implement Right/Left joins on DynamicViewEntity
2) How to implement my whereString


Thank you in advance,

[BJ Freeman]

as far as performance how much memory have you allocated to ofbiz?
what version of ofbiz are you using?
There is a top Category that should be checked for first.
have not been in that part of the code for a while.

Michał Cukierman sent the following on 8/17/2010 7:41 AM:

> Hi,
>
> I have a problem with performence (and Out of Memmory Issue) with
> catalog management module. There is a section invoked after
> Choose Top Category
>
> Collection<GenericValue>  allCategories =
> delegator.findList("ProductCategory", null, null, null, null, false);
> for (GenericValue curCat: allCategories) {
> Collection<GenericValue>  parentCats =
> curCat.getRelatedCache("CurrentProductCategoryRollup");
>
>   if (parentCats.isEmpty())
> results.add(curCat);
> }
>
> As we see. To get categories without parents we need to fetch for all
> categories and for each of them, find a parent.
>
> I do not know Ofbiz api very well for now, so I have implemented:
> EntityWhereString where =
> EntityCondition.makeConditionWhere("PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID
> not in" +
> " (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
> PRODUCT_CATEGORY_ROLLUP)");
> Set<String>  fieldsToSelect = FastSet.newInstance();
> fieldsToSelect.add("productCategoryId");
> results.addAll(delegator.findList("ProductCategory", where,
> fieldsToSelect, null, null, false));
>
>
> I simply do invoke a SQL query in the database:
>
> select PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID from PRODUCT_CATEGORY where
> PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID not in
> (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
> PRODUCT_CATEGORY_ROLLUP);
>
> Is there a way ( for sure is ) to implement it in a better manner? For
> now it works on my system, but after improving the query it could be
> passed to a production.
>
> So I got two questions in fact:
>
> 1) How to implement Right/Left joins on DynamicViewEntity
> 2) How to implement my whereString
>
>
> Thank you in advance,
>
>

[Michał Cukierman]

Thanks for the quick answer.

I have allocated 2GB of memory, so it's not a case. The problem is that
I work on huge data sets. Version is 10.4 but 9.04 is has the same code.
I have ommited those informations 'couse as you see - it's not relevant
to the topic.

I don't understand your last assumption. Maybe it will someting in my
understanding?

For now I think the best would be to improve the code I have proposed as
it fixes:
1) My issues
2) Coding way of selecting everything to get a couple of categories.

So, thats why I asked about:

> 1) How to implement Right/Left joins on DynamicViewEntity
(I was able to find out how to creat inner join only)
> > 2) How to implement my whereString
(Haven't found in a code such a use case)


Regards,
Michał

 


Dnia 2010-08-17, wto o godzinie 07:54 -0700, BJ Freeman pisze:

> as far as performance how much memory have you allocated to ofbiz?
> what version of ofbiz are you using?
> There is a top Category that should be checked for first.
> have not been in that part of the code for a while.
>
> Michał Cukierman sent the following on 8/17/2010 7:41 AM:
> > Hi,
> >
> > I have a problem with performence (and Out of Memmory Issue) with
> > catalog management module. There is a section invoked after
> > Choose Top Category
> >
> > Collection<GenericValue>  allCategories =
> > delegator.findList("ProductCategory", null, null, null, null, false);
> > for (GenericValue curCat: allCategories) {
> > Collection<GenericValue>  parentCats =
> > curCat.getRelatedCache("CurrentProductCategoryRollup");
> >
> >   if (parentCats.isEmpty())
> > results.add(curCat);
> > }
> >
> > As we see. To get categories without parents we need to fetch for all
> > categories and for each of them, find a parent.
> >
> > I do not know Ofbiz api very well for now, so I have implemented:
> > EntityWhereString where =
> > EntityCondition.makeConditionWhere("PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID
> > not in" +
> > " (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
> > PRODUCT_CATEGORY_ROLLUP)");
> > Set<String>  fieldsToSelect = FastSet.newInstance();
> > fieldsToSelect.add("productCategoryId");
> > results.addAll(delegator.findList("ProductCategory", where,
> > fieldsToSelect, null, null, false));
> >
> >
> > I simply do invoke a SQL query in the database:
> >
> > select PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID from PRODUCT_CATEGORY where
> > PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID not in
> > (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
> > PRODUCT_CATEGORY_ROLLUP);
> >
> > Is there a way ( for sure is ) to implement it in a better manner? For
> > now it works on my system, but after improving the query it could be
> > passed to a production.
> >
> > So I got two questions in fact:
> >
> > 1) How to implement Right/Left joins on DynamicViewEntity
> > 2) How to implement my whereString
> >
> >
> > Thank you in advance,
> >
> >

[Ruth Hoffman-2]

Hi Micha:

Could I ask how big are your data sets? The reason I ask is that several
years ago, prior to 9.04 I had an out-of-memory problem that was never
resolved. The database tables that I was querying had between 4 - 12
million records. My database (and OFBiz usage of the same) went away
before I couldn't do any further investigation into the problem.

So, I'm still curious about OFBiz support for really large data sets.

Regards,
Ruth

Michał Cukierman wrote:

> Thanks for the quick answer.
>
> I have allocated 2GB of memory, so it's not a case. The problem is that
> I work on huge data sets. Version is 10.4 but 9.04 is has the same code.
> I have ommited those informations 'couse as you see - it's not relevant
> to the topic.
>
> I don't understand your last assumption. Maybe it will someting in my
> understanding?
>
> For now I think the best would be to improve the code I have proposed as
> it fixes:
> 1) My issues
> 2) Coding way of selecting everything to get a couple of categories.
>
> So, thats why I asked about:
>
>  
>> 1) How to implement Right/Left joins on DynamicViewEntity
>>    
> (I was able to find out how to creat inner join only)
>  
>>> 2) How to implement my whereString
>>>      
> (Haven't found in a code such a use case)
>
>
> Regards,
> Michał
>
>  
>
>
> Dnia 2010-08-17, wto o godzinie 07:54 -0700, BJ Freeman pisze:
>  
>> as far as performance how much memory have you allocated to ofbiz?
>> what version of ofbiz are you using?
>> There is a top Category that should be checked for first.
>> have not been in that part of the code for a while.
>>
>> Michał Cukierman sent the following on 8/17/2010 7:41 AM:
>>    
>>> Hi,
>>>
>>> I have a problem with performence (and Out of Memmory Issue) with
>>> catalog management module. There is a section invoked after
>>> Choose Top Category
>>>
>>> Collection<GenericValue>  allCategories =
>>> delegator.findList("ProductCategory", null, null, null, null, false);
>>> for (GenericValue curCat: allCategories) {
>>> Collection<GenericValue>  parentCats =
>>> curCat.getRelatedCache("CurrentProductCategoryRollup");
>>>
>>>   if (parentCats.isEmpty())
>>> results.add(curCat);
>>> }
>>>
>>> As we see. To get categories without parents we need to fetch for all
>>> categories and for each of them, find a parent.
>>>
>>> I do not know Ofbiz api very well for now, so I have implemented:
>>> EntityWhereString where =
>>> EntityCondition.makeConditionWhere("PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID
>>> not in" +
>>> " (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
>>> PRODUCT_CATEGORY_ROLLUP)");
>>> Set<String>  fieldsToSelect = FastSet.newInstance();
>>> fieldsToSelect.add("productCategoryId");
>>> results.addAll(delegator.findList("ProductCategory", where,
>>> fieldsToSelect, null, null, false));
>>>
>>>
>>> I simply do invoke a SQL query in the database:
>>>
>>> select PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID from PRODUCT_CATEGORY where
>>> PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID not in
>>> (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
>>> PRODUCT_CATEGORY_ROLLUP);
>>>
>>> Is there a way ( for sure is ) to implement it in a better manner? For
>>> now it works on my system, but after improving the query it could be
>>> passed to a production.
>>>
>>> So I got two questions in fact:
>>>
>>> 1) How to implement Right/Left joins on DynamicViewEntity
>>> 2) How to implement my whereString
>>>
>>>
>>> Thank you in advance,
>>>
>>>
>>>      
>
>
>  

[Michał Cukierman]

Hi Ruth,
I do not know exact data set size, as we are in development stage at the
moment. What I know is that we need to prepare for:
- unknown no of categories (tests made on 100 000 - then the error
eccured)
- at least 2*suppliers number catalogs
- around 200 different manufacturers
- around 10,20 suppliters
- at the moment I am working with fetures, it's possible that I will
split number of categories.

My market is automotive industry. As you know, parts for such a
companies are very well categorised. Those criteria basis on:
a) Car selection
1)Make
2)Model
3)Year
4)Engine
5)Body Type

b) Part selection
-)Number
-)Placement
-)Vendor
-)Price
-)Supplier
-)Location
-)Material
1)Type (i.e. engine parts)
2)Sub-type (i.e bearings)
3)Part (i.e. crankshaft bearing)
4)Part instances

+ information about replacements
+ information about related parts

So this is only regarding categorisation. At the beggining I wanted to
put it all in one tree ( without criteria marked by "-)"). Just like in
www.rockauto.com
Now I do think about putting there more criteria to allow users browse
for what do they need. See example:
www.autopartswarehouse.com

Part catalog information will be integrated next year (soonest). Now we
need to prepare Phase0 project. As you see, with such amount of data we
need to fix issues that do not bother when playing with medium size shop
selling clothes.

Anyway I think that I could improve my working solution and pass it to
you. It will be easier for maintance for myself. Thats why my question
was regarding improvement of my code.

Hope this brings some light on the topic.



Dnia 2010-08-17, wto o godzinie 11:33 -0400, Ruth Hoffman pisze:

> Hi Micha:
>
> Could I ask how big are your data sets? The reason I ask is that
> several
> years ago, prior to 9.04 I had an out-of-memory problem that was
> never
> resolved. The database tables that I was querying had between 4 - 12
> million records. My database (and OFBiz usage of the same) went away
> before I couldn't do any further investigation into the problem.
>
> So, I'm still curious about OFBiz support for really large data sets.
>
> Regards,
> Ruth
>
> Michał Cukierman wrote:
> > Thanks for the quick answer.
> >
> > I have allocated 2GB of memory, so it's not a case. The problem is
> that
> > I work on huge data sets. Version is 10.4 but 9.04 is has the same
> code.
> > I have ommited those informations 'couse as you see - it's not
> relevant
> > to the topic.
> >
> > I don't understand your last assumption. Maybe it will someting in
> my
> > understanding?
> >
> > For now I think the best would be to improve the code I have
> proposed as
> > it fixes:
> > 1) My issues
> > 2) Coding way of selecting everything to get a couple of categories.
> >
> > So, thats why I asked about:
> >
> >  
> >> 1) How to implement Right/Left joins on DynamicViewEntity
> >>    
> > (I was able to find out how to creat inner join only)
> >  
> >>> 2) How to implement my whereString
> >>>      
> > (Haven't found in a code such a use case)
> >
> >
> > Regards,
> > Michał
> >
> >  
> >
> >
> > Dnia 2010-08-17, wto o godzinie 07:54 -0700, BJ Freeman pisze:
> >  
> >> as far as performance how much memory have you allocated to ofbiz?
> >> what version of ofbiz are you using?
> >> There is a top Category that should be checked for first.
> >> have not been in that part of the code for a while.
> >>
> >> Michał Cukierman sent the following on 8/17/2010 7:41 AM:
> >>    
> >>> Hi,
> >>>
> >>> I have a problem with performence (and Out of Memmory Issue) with
> >>> catalog management module. There is a section invoked after
> >>> Choose Top Category
> >>>
> >>> Collection<GenericValue>  allCategories =
> >>> delegator.findList("ProductCategory", null, null, null, null,
> false);
> >>> for (GenericValue curCat: allCategories) {
> >>>     Collection<GenericValue>  parentCats =
> >>> curCat.getRelatedCache("CurrentProductCategoryRollup");
> >>>
> >>>   if (parentCats.isEmpty())
> >>>     results.add(curCat);
> >>> }
> >>>
> >>> As we see. To get categories without parents we need to fetch for
> all
> >>> categories and for each of them, find a parent.
> >>>
> >>> I do not know Ofbiz api very well for now, so I have implemented:
> >>> EntityWhereString where =
> >>>
> EntityCondition.makeConditionWhere("PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID
> >>> not in" +
> >>> " (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
> >>> PRODUCT_CATEGORY_ROLLUP)");
> >>> Set<String>  fieldsToSelect = FastSet.newInstance();
> >>>                     fieldsToSelect.add("productCategoryId");
> >>>                     results.addAll(delegator.findList("ProductCategory", where,
> >>> fieldsToSelect, null, null, false));
> >>>
> >>>
> >>> I simply do invoke a SQL query in the database:
> >>>
> >>> select PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID from PRODUCT_CATEGORY
> where
> >>> PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID not in
> >>> (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
> >>> PRODUCT_CATEGORY_ROLLUP);
> >>>
> >>> Is there a way ( for sure is ) to implement it in a better manner?
> For
> >>> now it works on my system, but after improving the query it could
> be
> >>> passed to a production.
> >>>
> >>> So I got two questions in fact:
> >>>
> >>> 1) How to implement Right/Left joins on DynamicViewEntity
> >>> 2) How to implement my whereString
> >>>
> >>>
> >>> Thank you in advance,
> >>>
> >>>
> >>>      
> >
> >
> >  
>

[David E. Jones-2]

One thing to keep in mind is that with database queries and OFBiz there is no inherent nature of how it handles large data sets. Each bit of code and each query needs to be designed with a certain expected data set size, and typically the more data you want to allow for in the data store the more effort and testing it requires.

In other words, a query and bit of code that doesn't handle a certain large table means absolutely nothing about how other queries and other code will handle similarly large tables.

-David


On Aug 17, 2010, at 9:33 AM, Ruth Hoffman wrote:

> Hi Micha:
>
> Could I ask how big are your data sets? The reason I ask is that several years ago, prior to 9.04 I had an out-of-memory problem that was never resolved. The database tables that I was querying had between 4 - 12 million records. My database (and OFBiz usage of the same) went away before I couldn't do any further investigation into the problem.
>
> So, I'm still curious about OFBiz support for really large data sets.
>
> Regards,
> Ruth
>
> Michał Cukierman wrote:
>> Thanks for the quick answer.
>>
>> I have allocated 2GB of memory, so it's not a case. The problem is that
>> I work on huge data sets. Version is 10.4 but 9.04 is has the same code.
>> I have ommited those informations 'couse as you see - it's not relevant
>> to the topic.
>>
>> I don't understand your last assumption. Maybe it will someting in my
>> understanding?
>>
>> For now I think the best would be to improve the code I have proposed as
>> it fixes:
>> 1) My issues
>> 2) Coding way of selecting everything to get a couple of categories.
>>
>> So, thats why I asked about:
>>
>>  
>>> 1) How to implement Right/Left joins on DynamicViewEntity    
>> (I was able to find out how to creat inner join only)
>>  
>>>> 2) How to implement my whereString      
>> (Haven't found in a code such a use case)
>>
>>
>> Regards,
>> Michał
>>
>>
>>
>> Dnia 2010-08-17, wto o godzinie 07:54 -0700, BJ Freeman pisze:
>>  
>>> as far as performance how much memory have you allocated to ofbiz?
>>> what version of ofbiz are you using?
>>> There is a top Category that should be checked for first.
>>> have not been in that part of the code for a while.
>>>
>>> Michał Cukierman sent the following on 8/17/2010 7:41 AM:
>>>    
>>>> Hi,
>>>>
>>>> I have a problem with performence (and Out of Memmory Issue) with
>>>> catalog management module. There is a section invoked after
>>>> Choose Top Category
>>>>
>>>> Collection<GenericValue>  allCategories =
>>>> delegator.findList("ProductCategory", null, null, null, null, false);
>>>> for (GenericValue curCat: allCategories) {
>>>> Collection<GenericValue>  parentCats =
>>>> curCat.getRelatedCache("CurrentProductCategoryRollup");
>>>>
>>>>  if (parentCats.isEmpty())
>>>> results.add(curCat);
>>>> }
>>>>
>>>> As we see. To get categories without parents we need to fetch for all
>>>> categories and for each of them, find a parent.
>>>>
>>>> I do not know Ofbiz api very well for now, so I have implemented:
>>>> EntityWhereString where =
>>>> EntityCondition.makeConditionWhere("PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID
>>>> not in" +
>>>> " (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
>>>> PRODUCT_CATEGORY_ROLLUP)");
>>>> Set<String>  fieldsToSelect = FastSet.newInstance();
>>>> fieldsToSelect.add("productCategoryId");
>>>> results.addAll(delegator.findList("ProductCategory", where,
>>>> fieldsToSelect, null, null, false));
>>>>
>>>>
>>>> I simply do invoke a SQL query in the database:
>>>>
>>>> select PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID from PRODUCT_CATEGORY where
>>>> PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID not in
>>>> (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
>>>> PRODUCT_CATEGORY_ROLLUP);
>>>>
>>>> Is there a way ( for sure is ) to implement it in a better manner? For
>>>> now it works on my system, but after improving the query it could be
>>>> passed to a production.
>>>>
>>>> So I got two questions in fact:
>>>>
>>>> 1) How to implement Right/Left joins on DynamicViewEntity
>>>> 2) How to implement my whereString
>>>>
>>>>
>>>> Thank you in advance,
>>>>
>>>>
>>>>      
>>
>>
>>  

[Ruth Hoffman-2]

Hi David:
Thanks for chiming in! I understand and my experience has been exactly
as you say: "Each bit of code and each query needs to be designed with a
certain expected data set size"

What you get with OFBiz out-of-the-box is a middle of the road
"compromise". The really good news is that OFBiz is flexible enough to
handle any necessary tweeks.

FYI - I wanted to do more exploration into testing with large data sets
and try and arrive at some guidelines to help determine just how those
"tweeks" might work. I had intended to document the same, but then my
database went away and I turned my attention to other things.

Regards,
Ruth

David E Jones wrote:

> One thing to keep in mind is that with database queries and OFBiz there is no inherent nature of how it handles large data sets. Each bit of code and each query needs to be designed with a certain expected data set size, and typically the more data you want to allow for in the data store the more effort and testing it requires.
>
> In other words, a query and bit of code that doesn't handle a certain large table means absolutely nothing about how other queries and other code will handle similarly large tables.
>
> -David
>
>
> On Aug 17, 2010, at 9:33 AM, Ruth Hoffman wrote:
>
>  
>> Hi Micha:
>>
>> Could I ask how big are your data sets? The reason I ask is that several years ago, prior to 9.04 I had an out-of-memory problem that was never resolved. The database tables that I was querying had between 4 - 12 million records. My database (and OFBiz usage of the same) went away before I couldn't do any further investigation into the problem.
>>
>> So, I'm still curious about OFBiz support for really large data sets.
>>
>> Regards,
>> Ruth
>>
>> Michał Cukierman wrote:
>>    
>>> Thanks for the quick answer.
>>>
>>> I have allocated 2GB of memory, so it's not a case. The problem is that
>>> I work on huge data sets. Version is 10.4 but 9.04 is has the same code.
>>> I have ommited those informations 'couse as you see - it's not relevant
>>> to the topic.
>>>
>>> I don't understand your last assumption. Maybe it will someting in my
>>> understanding?
>>>
>>> For now I think the best would be to improve the code I have proposed as
>>> it fixes:
>>> 1) My issues
>>> 2) Coding way of selecting everything to get a couple of categories.
>>>
>>> So, thats why I asked about:
>>>
>>>  
>>>      
>>>> 1) How to implement Right/Left joins on DynamicViewEntity    
>>>>        
>>> (I was able to find out how to creat inner join only)
>>>  
>>>      
>>>>> 2) How to implement my whereString      
>>>>>          
>>> (Haven't found in a code such a use case)
>>>
>>>
>>> Regards,
>>> Michał
>>>
>>>
>>>
>>> Dnia 2010-08-17, wto o godzinie 07:54 -0700, BJ Freeman pisze:
>>>  
>>>      
>>>> as far as performance how much memory have you allocated to ofbiz?
>>>> what version of ofbiz are you using?
>>>> There is a top Category that should be checked for first.
>>>> have not been in that part of the code for a while.
>>>>
>>>> Michał Cukierman sent the following on 8/17/2010 7:41 AM:
>>>>    
>>>>        
>>>>> Hi,
>>>>>
>>>>> I have a problem with performence (and Out of Memmory Issue) with
>>>>> catalog management module. There is a section invoked after
>>>>> Choose Top Category
>>>>>
>>>>> Collection<GenericValue>  allCategories =
>>>>> delegator.findList("ProductCategory", null, null, null, null, false);
>>>>> for (GenericValue curCat: allCategories) {
>>>>> Collection<GenericValue>  parentCats =
>>>>> curCat.getRelatedCache("CurrentProductCategoryRollup");
>>>>>
>>>>>  if (parentCats.isEmpty())
>>>>> results.add(curCat);
>>>>> }
>>>>>
>>>>> As we see. To get categories without parents we need to fetch for all
>>>>> categories and for each of them, find a parent.
>>>>>
>>>>> I do not know Ofbiz api very well for now, so I have implemented:
>>>>> EntityWhereString where =
>>>>> EntityCondition.makeConditionWhere("PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID
>>>>> not in" +
>>>>> " (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
>>>>> PRODUCT_CATEGORY_ROLLUP)");
>>>>> Set<String>  fieldsToSelect = FastSet.newInstance();
>>>>> fieldsToSelect.add("productCategoryId");
>>>>> results.addAll(delegator.findList("ProductCategory", where,
>>>>> fieldsToSelect, null, null, false));
>>>>>
>>>>>
>>>>> I simply do invoke a SQL query in the database:
>>>>>
>>>>> select PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID from PRODUCT_CATEGORY where
>>>>> PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID not in
>>>>> (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
>>>>> PRODUCT_CATEGORY_ROLLUP);
>>>>>
>>>>> Is there a way ( for sure is ) to implement it in a better manner? For
>>>>> now it works on my system, but after improving the query it could be
>>>>> passed to a production.
>>>>>
>>>>> So I got two questions in fact:
>>>>>
>>>>> 1) How to implement Right/Left joins on DynamicViewEntity
>>>>> 2) How to implement my whereString
>>>>>
>>>>>
>>>>> Thank you in advance,
>>>>>
>>>>>
>>>>>      
>>>>>          
>>>  
>>>      
>
>
>  

[David E. Jones-2]

I wouldn't say that OFBiz OOTB is a middle of the road compromise, that would imply consistency in the code which is not the case. The fact is some code is designed and tested to properly handle millions or even billions of rows in its target table, and other code can only have a few thousand or perhaps tens of thousands. There is no general rule or easy way to simplify that down. Things that are used more by larger organization have tended to be written or improved over time to handle more data. Things that aren't haven't, it's that simple.

-David


On Aug 17, 2010, at 10:19 AM, Ruth Hoffman wrote:

> Hi David:
> Thanks for chiming in! I understand and my experience has been exactly as you say: "Each bit of code and each query needs to be designed with a certain expected data set size"
>
> What you get with OFBiz out-of-the-box is a middle of the road "compromise". The really good news is that OFBiz is flexible enough to handle any necessary tweeks.
>
> FYI - I wanted to do more exploration into testing with large data sets and try and arrive at some guidelines to help determine just how those "tweeks" might work. I had intended to document the same, but then my database went away and I turned my attention to other things.
>
> Regards,
> Ruth
>
> David E Jones wrote:
>> One thing to keep in mind is that with database queries and OFBiz there is no inherent nature of how it handles large data sets. Each bit of code and each query needs to be designed with a certain expected data set size, and typically the more data you want to allow for in the data store the more effort and testing it requires.
>>
>> In other words, a query and bit of code that doesn't handle a certain large table means absolutely nothing about how other queries and other code will handle similarly large tables.
>>
>> -David
>>
>>
>> On Aug 17, 2010, at 9:33 AM, Ruth Hoffman wrote:
>>
>>  
>>> Hi Micha:
>>>
>>> Could I ask how big are your data sets? The reason I ask is that several years ago, prior to 9.04 I had an out-of-memory problem that was never resolved. The database tables that I was querying had between 4 - 12 million records. My database (and OFBiz usage of the same) went away before I couldn't do any further investigation into the problem.
>>>
>>> So, I'm still curious about OFBiz support for really large data sets.
>>>
>>> Regards,
>>> Ruth
>>>
>>> Michał Cukierman wrote:
>>>    
>>>> Thanks for the quick answer.
>>>>
>>>> I have allocated 2GB of memory, so it's not a case. The problem is that
>>>> I work on huge data sets. Version is 10.4 but 9.04 is has the same code.
>>>> I have ommited those informations 'couse as you see - it's not relevant
>>>> to the topic.
>>>>
>>>> I don't understand your last assumption. Maybe it will someting in my
>>>> understanding?
>>>>
>>>> For now I think the best would be to improve the code I have proposed as
>>>> it fixes:
>>>> 1) My issues
>>>> 2) Coding way of selecting everything to get a couple of categories.
>>>>
>>>> So, thats why I asked about:
>>>>
>>>>      
>>>>> 1) How to implement Right/Left joins on DynamicViewEntity            
>>>> (I was able to find out how to creat inner join only)
>>>>      
>>>>>> 2) How to implement my whereString                
>>>> (Haven't found in a code such a use case)
>>>>
>>>>
>>>> Regards,
>>>> Michał
>>>>
>>>>
>>>>
>>>> Dnia 2010-08-17, wto o godzinie 07:54 -0700, BJ Freeman pisze:
>>>>      
>>>>> as far as performance how much memory have you allocated to ofbiz?
>>>>> what version of ofbiz are you using?
>>>>> There is a top Category that should be checked for first.
>>>>> have not been in that part of the code for a while.
>>>>>
>>>>> Michał Cukierman sent the following on 8/17/2010 7:41 AM:
>>>>>          
>>>>>> Hi,
>>>>>>
>>>>>> I have a problem with performence (and Out of Memmory Issue) with
>>>>>> catalog management module. There is a section invoked after
>>>>>> Choose Top Category
>>>>>>
>>>>>> Collection<GenericValue>  allCategories =
>>>>>> delegator.findList("ProductCategory", null, null, null, null, false);
>>>>>> for (GenericValue curCat: allCategories) {
>>>>>> Collection<GenericValue>  parentCats =
>>>>>> curCat.getRelatedCache("CurrentProductCategoryRollup");
>>>>>>
>>>>>> if (parentCats.isEmpty())
>>>>>> results.add(curCat);
>>>>>> }
>>>>>>
>>>>>> As we see. To get categories without parents we need to fetch for all
>>>>>> categories and for each of them, find a parent.
>>>>>>
>>>>>> I do not know Ofbiz api very well for now, so I have implemented:
>>>>>> EntityWhereString where =
>>>>>> EntityCondition.makeConditionWhere("PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID
>>>>>> not in" +
>>>>>> " (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
>>>>>> PRODUCT_CATEGORY_ROLLUP)");
>>>>>> Set<String>  fieldsToSelect = FastSet.newInstance();
>>>>>> fieldsToSelect.add("productCategoryId");
>>>>>> results.addAll(delegator.findList("ProductCategory", where,
>>>>>> fieldsToSelect, null, null, false));
>>>>>>
>>>>>>
>>>>>> I simply do invoke a SQL query in the database:
>>>>>>
>>>>>> select PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID from PRODUCT_CATEGORY where
>>>>>> PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID not in
>>>>>> (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
>>>>>> PRODUCT_CATEGORY_ROLLUP);
>>>>>>
>>>>>> Is there a way ( for sure is ) to implement it in a better manner? For
>>>>>> now it works on my system, but after improving the query it could be
>>>>>> passed to a production.
>>>>>>
>>>>>> So I got two questions in fact:
>>>>>>
>>>>>> 1) How to implement Right/Left joins on DynamicViewEntity
>>>>>> 2) How to implement my whereString
>>>>>>
>>>>>>
>>>>>> Thank you in advance,
>>>>>>
>>>>>>
>>>>>>              
>>>>      
>>
>>
>>  

[Ruth Hoffman-2]

Hi David:

Would you be interested in describing for us all - here on the list -
where some of that code is and how an implementation was arrived at to
"properly handle millions or even billions of rows" in a target table.
I, for one would be very interested in knowing just where that code
might be and how it was tested.

Regards,
Ruth

David E Jones wrote:

> I wouldn't say that OFBiz OOTB is a middle of the road compromise, that would imply consistency in the code which is not the case. The fact is some code is designed and tested to properly handle millions or even billions of rows in its target table, and other code can only have a few thousand or perhaps tens of thousands. There is no general rule or easy way to simplify that down. Things that are used more by larger organization have tended to be written or improved over time to handle more data. Things that aren't haven't, it's that simple.
>
> -David
>
>
> On Aug 17, 2010, at 10:19 AM, Ruth Hoffman wrote:
>
>  
>> Hi David:
>> Thanks for chiming in! I understand and my experience has been exactly as you say: "Each bit of code and each query needs to be designed with a certain expected data set size"
>>
>> What you get with OFBiz out-of-the-box is a middle of the road "compromise". The really good news is that OFBiz is flexible enough to handle any necessary tweeks.
>>
>> FYI - I wanted to do more exploration into testing with large data sets and try and arrive at some guidelines to help determine just how those "tweeks" might work. I had intended to document the same, but then my database went away and I turned my attention to other things.
>>
>> Regards,
>> Ruth
>>
>> David E Jones wrote:
>>    
>>> One thing to keep in mind is that with database queries and OFBiz there is no inherent nature of how it handles large data sets. Each bit of code and each query needs to be designed with a certain expected data set size, and typically the more data you want to allow for in the data store the more effort and testing it requires.
>>>
>>> In other words, a query and bit of code that doesn't handle a certain large table means absolutely nothing about how other queries and other code will handle similarly large tables.
>>>
>>> -David
>>>
>>>
>>> On Aug 17, 2010, at 9:33 AM, Ruth Hoffman wrote:
>>>
>>>  
>>>      
>>>> Hi Micha:
>>>>
>>>> Could I ask how big are your data sets? The reason I ask is that several years ago, prior to 9.04 I had an out-of-memory problem that was never resolved. The database tables that I was querying had between 4 - 12 million records. My database (and OFBiz usage of the same) went away before I couldn't do any further investigation into the problem.
>>>>
>>>> So, I'm still curious about OFBiz support for really large data sets.
>>>>
>>>> Regards,
>>>> Ruth
>>>>
>>>> Michał Cukierman wrote:
>>>>    
>>>>        
>>>>> Thanks for the quick answer.
>>>>>
>>>>> I have allocated 2GB of memory, so it's not a case. The problem is that
>>>>> I work on huge data sets. Version is 10.4 but 9.04 is has the same code.
>>>>> I have ommited those informations 'couse as you see - it's not relevant
>>>>> to the topic.
>>>>>
>>>>> I don't understand your last assumption. Maybe it will someting in my
>>>>> understanding?
>>>>>
>>>>> For now I think the best would be to improve the code I have proposed as
>>>>> it fixes:
>>>>> 1) My issues
>>>>> 2) Coding way of selecting everything to get a couple of categories.
>>>>>
>>>>> So, thats why I asked about:
>>>>>
>>>>>      
>>>>>          
>>>>>> 1) How to implement Right/Left joins on DynamicViewEntity            
>>>>>>            
>>>>> (I was able to find out how to creat inner join only)
>>>>>      
>>>>>          
>>>>>>> 2) How to implement my whereString                
>>>>>>>              
>>>>> (Haven't found in a code such a use case)
>>>>>
>>>>>
>>>>> Regards,
>>>>> Michał
>>>>>
>>>>>
>>>>>
>>>>> Dnia 2010-08-17, wto o godzinie 07:54 -0700, BJ Freeman pisze:
>>>>>      
>>>>>          
>>>>>> as far as performance how much memory have you allocated to ofbiz?
>>>>>> what version of ofbiz are you using?
>>>>>> There is a top Category that should be checked for first.
>>>>>> have not been in that part of the code for a while.
>>>>>>
>>>>>> Michał Cukierman sent the following on 8/17/2010 7:41 AM:
>>>>>>          
>>>>>>            
>>>>>>> Hi,
>>>>>>>
>>>>>>> I have a problem with performence (and Out of Memmory Issue) with
>>>>>>> catalog management module. There is a section invoked after
>>>>>>> Choose Top Category
>>>>>>>
>>>>>>> Collection<GenericValue>  allCategories =
>>>>>>> delegator.findList("ProductCategory", null, null, null, null, false);
>>>>>>> for (GenericValue curCat: allCategories) {
>>>>>>> Collection<GenericValue>  parentCats =
>>>>>>> curCat.getRelatedCache("CurrentProductCategoryRollup");
>>>>>>>
>>>>>>> if (parentCats.isEmpty())
>>>>>>> results.add(curCat);
>>>>>>> }
>>>>>>>
>>>>>>> As we see. To get categories without parents we need to fetch for all
>>>>>>> categories and for each of them, find a parent.
>>>>>>>
>>>>>>> I do not know Ofbiz api very well for now, so I have implemented:
>>>>>>> EntityWhereString where =
>>>>>>> EntityCondition.makeConditionWhere("PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID
>>>>>>> not in" +
>>>>>>> " (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
>>>>>>> PRODUCT_CATEGORY_ROLLUP)");
>>>>>>> Set<String>  fieldsToSelect = FastSet.newInstance();
>>>>>>> fieldsToSelect.add("productCategoryId");
>>>>>>> results.addAll(delegator.findList("ProductCategory", where,
>>>>>>> fieldsToSelect, null, null, false));
>>>>>>>
>>>>>>>
>>>>>>> I simply do invoke a SQL query in the database:
>>>>>>>
>>>>>>> select PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID from PRODUCT_CATEGORY where
>>>>>>> PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID not in
>>>>>>> (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
>>>>>>> PRODUCT_CATEGORY_ROLLUP);
>>>>>>>
>>>>>>> Is there a way ( for sure is ) to implement it in a better manner? For
>>>>>>> now it works on my system, but after improving the query it could be
>>>>>>> passed to a production.
>>>>>>>
>>>>>>> So I got two questions in fact:
>>>>>>>
>>>>>>> 1) How to implement Right/Left joins on DynamicViewEntity
>>>>>>> 2) How to implement my whereString
>>>>>>>
>>>>>>>
>>>>>>> Thank you in advance,
>>>>>>>
>>>>>>>
>>>>>>>              
>>>>>>>              
>>>>>      
>>>>>          
>>>  
>>>      
>
>
>  

[Michał Cukierman]

I know that Ofbiz is limited in some areas, but as far it's the best
place to start. It's better to improve some code than write it all. What
I also think: I have software for free, I can put some effort to improve
it.

So to keep the problem in mind. What is the way of implementing:

1) How to implement Right/Left joins on DynamicViewEntity
2) How to implement my whereString
 - > select .... where not in (select ...)

And by the way:
class:CategoryWorker
method:getCategoriesWithNoParent

My change do not affect the system I think. It gets some parameters and
return a query result. So there is not much to break :)


Dnia 2010-08-17, wto o godzinie 10:23 -0600, David E Jones pisze:

> I wouldn't say that OFBiz OOTB is a middle of the road compromise, that would imply consistency in the code which is not the case. The fact is some code is designed and tested to properly handle millions or even billions of rows in its target table, and other code can only have a few thousand or perhaps tens of thousands. There is no general rule or easy way to simplify that down. Things that are used more by larger organization have tended to be written or improved over time to handle more data. Things that aren't haven't, it's that simple.
>
> -David
>
>
> On Aug 17, 2010, at 10:19 AM, Ruth Hoffman wrote:
>
> > Hi David:
> > Thanks for chiming in! I understand and my experience has been exactly as you say: "Each bit of code and each query needs to be designed with a certain expected data set size"
> >
> > What you get with OFBiz out-of-the-box is a middle of the road "compromise". The really good news is that OFBiz is flexible enough to handle any necessary tweeks.
> >
> > FYI - I wanted to do more exploration into testing with large data sets and try and arrive at some guidelines to help determine just how those "tweeks" might work. I had intended to document the same, but then my database went away and I turned my attention to other things.
> >
> > Regards,
> > Ruth
> >
> > David E Jones wrote:
> >> One thing to keep in mind is that with database queries and OFBiz there is no inherent nature of how it handles large data sets. Each bit of code and each query needs to be designed with a certain expected data set size, and typically the more data you want to allow for in the data store the more effort and testing it requires.
> >>
> >> In other words, a query and bit of code that doesn't handle a certain large table means absolutely nothing about how other queries and other code will handle similarly large tables.
> >>
> >> -David
> >>
> >>
> >> On Aug 17, 2010, at 9:33 AM, Ruth Hoffman wrote:
> >>
> >>  
> >>> Hi Micha:
> >>>
> >>> Could I ask how big are your data sets? The reason I ask is that several years ago, prior to 9.04 I had an out-of-memory problem that was never resolved. The database tables that I was querying had between 4 - 12 million records. My database (and OFBiz usage of the same) went away before I couldn't do any further investigation into the problem.
> >>>
> >>> So, I'm still curious about OFBiz support for really large data sets.
> >>>
> >>> Regards,
> >>> Ruth
> >>>
> >>> Michał Cukierman wrote:
> >>>    
> >>>> Thanks for the quick answer.
> >>>>
> >>>> I have allocated 2GB of memory, so it's not a case. The problem is that
> >>>> I work on huge data sets. Version is 10.4 but 9.04 is has the same code.
> >>>> I have ommited those informations 'couse as you see - it's not relevant
> >>>> to the topic.
> >>>>
> >>>> I don't understand your last assumption. Maybe it will someting in my
> >>>> understanding?
> >>>>
> >>>> For now I think the best would be to improve the code I have proposed as
> >>>> it fixes:
> >>>> 1) My issues
> >>>> 2) Coding way of selecting everything to get a couple of categories.
> >>>>
> >>>> So, thats why I asked about:
> >>>>
> >>>>      
> >>>>> 1) How to implement Right/Left joins on DynamicViewEntity            
> >>>> (I was able to find out how to creat inner join only)
> >>>>      
> >>>>>> 2) How to implement my whereString                
> >>>> (Haven't found in a code such a use case)
> >>>>
> >>>>
> >>>> Regards,
> >>>> Michał
> >>>>
> >>>>
> >>>>
> >>>> Dnia 2010-08-17, wto o godzinie 07:54 -0700, BJ Freeman pisze:
> >>>>      
> >>>>> as far as performance how much memory have you allocated to ofbiz?
> >>>>> what version of ofbiz are you using?
> >>>>> There is a top Category that should be checked for first.
> >>>>> have not been in that part of the code for a while.
> >>>>>
> >>>>> Michał Cukierman sent the following on 8/17/2010 7:41 AM:
> >>>>>          
> >>>>>> Hi,
> >>>>>>
> >>>>>> I have a problem with performence (and Out of Memmory Issue) with
> >>>>>> catalog management module. There is a section invoked after
> >>>>>> Choose Top Category
> >>>>>>
> >>>>>> Collection<GenericValue>  allCategories >>>>>> delegator.findList("ProductCategory", null, null, null, null, false);
> >>>>>> for (GenericValue curCat: allCategories) {
> >>>>>> Collection<GenericValue>  parentCats >>>>>> curCat.getRelatedCache("CurrentProductCategoryRollup");
> >>>>>>
> >>>>>> if (parentCats.isEmpty())
> >>>>>> results.add(curCat);
> >>>>>> }
> >>>>>>
> >>>>>> As we see. To get categories without parents we need to fetch for all
> >>>>>> categories and for each of them, find a parent.
> >>>>>>
> >>>>>> I do not know Ofbiz api very well for now, so I have implemented:
> >>>>>> EntityWhereString where >>>>>> EntityCondition.makeConditionWhere("PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID
> >>>>>> not in" +
> >>>>>> " (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
> >>>>>> PRODUCT_CATEGORY_ROLLUP)");
> >>>>>> Set<String>  fieldsToSelect = FastSet.newInstance();
> >>>>>> fieldsToSelect.add("productCategoryId");
> >>>>>> results.addAll(delegator.findList("ProductCategory", where,
> >>>>>> fieldsToSelect, null, null, false));
> >>>>>>
> >>>>>>
> >>>>>> I simply do invoke a SQL query in the database:
> >>>>>>
> >>>>>> select PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID from PRODUCT_CATEGORY where
> >>>>>> PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID not in
> >>>>>> (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
> >>>>>> PRODUCT_CATEGORY_ROLLUP);
> >>>>>>
> >>>>>> Is there a way ( for sure is ) to implement it in a better manner? For
> >>>>>> now it works on my system, but after improving the query it could be
> >>>>>> passed to a production.
> >>>>>>
> >>>>>> So I got two questions in fact:
> >>>>>>
> >>>>>> 1) How to implement Right/Left joins on DynamicViewEntity
> >>>>>> 2) How to implement my whereString
> >>>>>>
> >>>>>>
> >>>>>> Thank you in advance,
> >>>>>>
> >>>>>>
> >>>>>>              
> >>>>      
> >>
> >>
> >>  
>

[Ruth Hoffman-2]

Hi Micha:

Sorry, but I'm probably not the best person to answer this question.
Hopefully David or someone who has spent considerable time working with
the API could lend a hand. Its been several years since I've had to look
at optimizing SQL statements within OFBiz.

Regards,
Ruth

Michał Cukierman wrote:

> I know that Ofbiz is limited in some areas, but as far it's the best
> place to start. It's better to improve some code than write it all. What
> I also think: I have software for free, I can put some effort to improve
> it.
>
> So to keep the problem in mind. What is the way of implementing:
>
> 1) How to implement Right/Left joins on DynamicViewEntity
> 2) How to implement my whereString
>  - > select .... where not in (select ...)
>
> And by the way:
> class:CategoryWorker
> method:getCategoriesWithNoParent
>
> My change do not affect the system I think. It gets some parameters and
> return a query result. So there is not much to break :)
>
>
> Dnia 2010-08-17, wto o godzinie 10:23 -0600, David E Jones pisze:
>  
>> I wouldn't say that OFBiz OOTB is a middle of the road compromise, that would imply consistency in the code which is not the case. The fact is some code is designed and tested to properly handle millions or even billions of rows in its target table, and other code can only have a few thousand or perhaps tens of thousands. There is no general rule or easy way to simplify that down. Things that are used more by larger organization have tended to be written or improved over time to handle more data. Things that aren't haven't, it's that simple.
>>
>> -David
>>
>>
>> On Aug 17, 2010, at 10:19 AM, Ruth Hoffman wrote:
>>
>>    
>>> Hi David:
>>> Thanks for chiming in! I understand and my experience has been exactly as you say: "Each bit of code and each query needs to be designed with a certain expected data set size"
>>>
>>> What you get with OFBiz out-of-the-box is a middle of the road "compromise". The really good news is that OFBiz is flexible enough to handle any necessary tweeks.
>>>
>>> FYI - I wanted to do more exploration into testing with large data sets and try and arrive at some guidelines to help determine just how those "tweeks" might work. I had intended to document the same, but then my database went away and I turned my attention to other things.
>>>
>>> Regards,
>>> Ruth
>>>
>>> David E Jones wrote:
>>>      
>>>> One thing to keep in mind is that with database queries and OFBiz there is no inherent nature of how it handles large data sets. Each bit of code and each query needs to be designed with a certain expected data set size, and typically the more data you want to allow for in the data store the more effort and testing it requires.
>>>>
>>>> In other words, a query and bit of code that doesn't handle a certain large table means absolutely nothing about how other queries and other code will handle similarly large tables.
>>>>
>>>> -David
>>>>
>>>>
>>>> On Aug 17, 2010, at 9:33 AM, Ruth Hoffman wrote:
>>>>
>>>>  
>>>>        
>>>>> Hi Micha:
>>>>>
>>>>> Could I ask how big are your data sets? The reason I ask is that several years ago, prior to 9.04 I had an out-of-memory problem that was never resolved. The database tables that I was querying had between 4 - 12 million records. My database (and OFBiz usage of the same) went away before I couldn't do any further investigation into the problem.
>>>>>
>>>>> So, I'm still curious about OFBiz support for really large data sets.
>>>>>
>>>>> Regards,
>>>>> Ruth
>>>>>
>>>>> Michał Cukierman wrote:
>>>>>    
>>>>>          
>>>>>> Thanks for the quick answer.
>>>>>>
>>>>>> I have allocated 2GB of memory, so it's not a case. The problem is that
>>>>>> I work on huge data sets. Version is 10.4 but 9.04 is has the same code.
>>>>>> I have ommited those informations 'couse as you see - it's not relevant
>>>>>> to the topic.
>>>>>>
>>>>>> I don't understand your last assumption. Maybe it will someting in my
>>>>>> understanding?
>>>>>>
>>>>>> For now I think the best would be to improve the code I have proposed as
>>>>>> it fixes:
>>>>>> 1) My issues
>>>>>> 2) Coding way of selecting everything to get a couple of categories.
>>>>>>
>>>>>> So, thats why I asked about:
>>>>>>
>>>>>>      
>>>>>>            
>>>>>>> 1) How to implement Right/Left joins on DynamicViewEntity            
>>>>>>>              
>>>>>> (I was able to find out how to creat inner join only)
>>>>>>      
>>>>>>            
>>>>>>>> 2) How to implement my whereString                
>>>>>>>>                
>>>>>> (Haven't found in a code such a use case)
>>>>>>
>>>>>>
>>>>>> Regards,
>>>>>> Michał
>>>>>>
>>>>>>
>>>>>>
>>>>>> Dnia 2010-08-17, wto o godzinie 07:54 -0700, BJ Freeman pisze:
>>>>>>      
>>>>>>            
>>>>>>> as far as performance how much memory have you allocated to ofbiz?
>>>>>>> what version of ofbiz are you using?
>>>>>>> There is a top Category that should be checked for first.
>>>>>>> have not been in that part of the code for a while.
>>>>>>>
>>>>>>> Michał Cukierman sent the following on 8/17/2010 7:41 AM:
>>>>>>>          
>>>>>>>              
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> I have a problem with performence (and Out of Memmory Issue) with
>>>>>>>> catalog management module. There is a section invoked after
>>>>>>>> Choose Top Category
>>>>>>>>
>>>>>>>> Collection<GenericValue>  allCategories >>>>>> delegator.findList("ProductCategory", null, null, null, null, false);
>>>>>>>> for (GenericValue curCat: allCategories) {
>>>>>>>> Collection<GenericValue>  parentCats >>>>>> curCat.getRelatedCache("CurrentProductCategoryRollup");
>>>>>>>>
>>>>>>>> if (parentCats.isEmpty())
>>>>>>>> results.add(curCat);
>>>>>>>> }
>>>>>>>>
>>>>>>>> As we see. To get categories without parents we need to fetch for all
>>>>>>>> categories and for each of them, find a parent.
>>>>>>>>
>>>>>>>> I do not know Ofbiz api very well for now, so I have implemented:
>>>>>>>> EntityWhereString where >>>>>> EntityCondition.makeConditionWhere("PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID
>>>>>>>> not in" +
>>>>>>>> " (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
>>>>>>>> PRODUCT_CATEGORY_ROLLUP)");
>>>>>>>> Set<String>  fieldsToSelect = FastSet.newInstance();
>>>>>>>> fieldsToSelect.add("productCategoryId");
>>>>>>>> results.addAll(delegator.findList("ProductCategory", where,
>>>>>>>> fieldsToSelect, null, null, false));
>>>>>>>>
>>>>>>>>
>>>>>>>> I simply do invoke a SQL query in the database:
>>>>>>>>
>>>>>>>> select PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID from PRODUCT_CATEGORY where
>>>>>>>> PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID not in
>>>>>>>> (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
>>>>>>>> PRODUCT_CATEGORY_ROLLUP);
>>>>>>>>
>>>>>>>> Is there a way ( for sure is ) to implement it in a better manner? For
>>>>>>>> now it works on my system, but after improving the query it could be
>>>>>>>> passed to a production.
>>>>>>>>
>>>>>>>> So I got two questions in fact:
>>>>>>>>
>>>>>>>> 1) How to implement Right/Left joins on DynamicViewEntity
>>>>>>>> 2) How to implement my whereString
>>>>>>>>
>>>>>>>>
>>>>>>>> Thank you in advance,
>>>>>>>>
>>>>>>>>
>>>>>>>>              
>>>>>>>>                
>>>>>>      
>>>>>>            
>>>>  
>>>>        
>
>
>  

[BJ Freeman]

Micheal I have, in the past had to deal with large databases.
even the best enterprise DB has memory problems in this case.
i have had to write Procedures (functions) in the database to parse the
data so it is usable.
That said it is more than just joins, indexes, and where statements, it
is staging the data in a way to make it usable.
one thing that David implemented was Cursors, however not all Databases
that are implemented by ofbiz supports cursors.
that said the place to add these is in the Delegator Classes in my opinion.



Michał Cukierman sent the following on 8/17/2010 9:35 AM:

> I know that Ofbiz is limited in some areas, but as far it's the best
> place to start. It's better to improve some code than write it all. What
> I also think: I have software for free, I can put some effort to improve
> it.
>
> So to keep the problem in mind. What is the way of implementing:
>
> 1) How to implement Right/Left joins on DynamicViewEntity
> 2) How to implement my whereString
>   ->  select .... where not in (select ...)
>
> And by the way:
> class:CategoryWorker
> method:getCategoriesWithNoParent
>
> My change do not affect the system I think. It gets some parameters and
> return a query result. So there is not much to break :)
>
>
> Dnia 2010-08-17, wto o godzinie 10:23 -0600, David E Jones pisze:
>> I wouldn't say that OFBiz OOTB is a middle of the road compromise, that would imply consistency in the code which is not the case. The fact is some code is designed and tested to properly handle millions or even billions of rows in its target table, and other code can only have a few thousand or perhaps tens of thousands. There is no general rule or easy way to simplify that down. Things that are used more by larger organization have tended to be written or improved over time to handle more data. Things that aren't haven't, it's that simple.
>>
>> -David
>>
>>
>> On Aug 17, 2010, at 10:19 AM, Ruth Hoffman wrote:
>>
>>> Hi David:
>>> Thanks for chiming in! I understand and my experience has been exactly as you say: "Each bit of code and each query needs to be designed with a certain expected data set size"
>>>
>>> What you get with OFBiz out-of-the-box is a middle of the road "compromise". The really good news is that OFBiz is flexible enough to handle any necessary tweeks.
>>>
>>> FYI - I wanted to do more exploration into testing with large data sets and try and arrive at some guidelines to help determine just how those "tweeks" might work. I had intended to document the same, but then my database went away and I turned my attention to other things.
>>>
>>> Regards,
>>> Ruth
>>>
>>> David E Jones wrote:
>>>> One thing to keep in mind is that with database queries and OFBiz there is no inherent nature of how it handles large data sets. Each bit of code and each query needs to be designed with a certain expected data set size, and typically the more data you want to allow for in the data store the more effort and testing it requires.
>>>>
>>>> In other words, a query and bit of code that doesn't handle a certain large table means absolutely nothing about how other queries and other code will handle similarly large tables.
>>>>
>>>> -David
>>>>
>>>>
>>>> On Aug 17, 2010, at 9:33 AM, Ruth Hoffman wrote:
>>>>
>>>>
>>>>> Hi Micha:
>>>>>
>>>>> Could I ask how big are your data sets? The reason I ask is that several years ago, prior to 9.04 I had an out-of-memory problem that was never resolved. The database tables that I was querying had between 4 - 12 million records. My database (and OFBiz usage of the same) went away before I couldn't do any further investigation into the problem.
>>>>>
>>>>> So, I'm still curious about OFBiz support for really large data sets.
>>>>>
>>>>> Regards,
>>>>> Ruth
>>>>>
>>>>> Michał Cukierman wrote:
>>>>>
>>>>>> Thanks for the quick answer.
>>>>>>
>>>>>> I have allocated 2GB of memory, so it's not a case. The problem is that
>>>>>> I work on huge data sets. Version is 10.4 but 9.04 is has the same code.
>>>>>> I have ommited those informations 'couse as you see - it's not relevant
>>>>>> to the topic.
>>>>>>
>>>>>> I don't understand your last assumption. Maybe it will someting in my
>>>>>> understanding?
>>>>>>
>>>>>> For now I think the best would be to improve the code I have proposed as
>>>>>> it fixes:
>>>>>> 1) My issues
>>>>>> 2) Coding way of selecting everything to get a couple of categories.
>>>>>>
>>>>>> So, thats why I asked about:
>>>>>>
>>>>>>
>>>>>>> 1) How to implement Right/Left joins on DynamicViewEntity
>>>>>> (I was able to find out how to creat inner join only)
>>>>>>
>>>>>>>> 2) How to implement my whereString
>>>>>> (Haven't found in a code such a use case)
>>>>>>
>>>>>>
>>>>>> Regards,
>>>>>> Michał
>>>>>>
>>>>>>
>>>>>>
>>>>>> Dnia 2010-08-17, wto o godzinie 07:54 -0700, BJ Freeman pisze:
>>>>>>
>>>>>>> as far as performance how much memory have you allocated to ofbiz?
>>>>>>> what version of ofbiz are you using?
>>>>>>> There is a top Category that should be checked for first.
>>>>>>> have not been in that part of the code for a while.
>>>>>>>
>>>>>>> Michał Cukierman sent the following on 8/17/2010 7:41 AM:
>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> I have a problem with performence (and Out of Memmory Issue) with
>>>>>>>> catalog management module. There is a section invoked after
>>>>>>>> Choose Top Category
>>>>>>>>
>>>>>>>> Collection<GenericValue>   allCategories>>>>>>  delegator.findList("ProductCategory", null, null, null, null, false);
>>>>>>>> for (GenericValue curCat: allCategories) {
>>>>>>>> Collection<GenericValue>   parentCats>>>>>>  curCat.getRelatedCache("CurrentProductCategoryRollup");
>>>>>>>>
>>>>>>>> if (parentCats.isEmpty())
>>>>>>>> results.add(curCat);
>>>>>>>> }
>>>>>>>>
>>>>>>>> As we see. To get categories without parents we need to fetch for all
>>>>>>>> categories and for each of them, find a parent.
>>>>>>>>
>>>>>>>> I do not know Ofbiz api very well for now, so I have implemented:
>>>>>>>> EntityWhereString where>>>>>>  EntityCondition.makeConditionWhere("PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID
>>>>>>>> not in" +
>>>>>>>> " (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
>>>>>>>> PRODUCT_CATEGORY_ROLLUP)");
>>>>>>>> Set<String>   fieldsToSelect = FastSet.newInstance();
>>>>>>>> fieldsToSelect.add("productCategoryId");
>>>>>>>> results.addAll(delegator.findList("ProductCategory", where,
>>>>>>>> fieldsToSelect, null, null, false));
>>>>>>>>
>>>>>>>>
>>>>>>>> I simply do invoke a SQL query in the database:
>>>>>>>>
>>>>>>>> select PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID from PRODUCT_CATEGORY where
>>>>>>>> PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID not in
>>>>>>>> (select PRODUCT_CATEGORY_ROLLUP.PRODUCT_CATEGORY_ID from
>>>>>>>> PRODUCT_CATEGORY_ROLLUP);
>>>>>>>>
>>>>>>>> Is there a way ( for sure is ) to implement it in a better manner? For
>>>>>>>> now it works on my system, but after improving the query it could be
>>>>>>>> passed to a production.
>>>>>>>>
>>>>>>>> So I got two questions in fact:
>>>>>>>>
>>>>>>>> 1) How to implement Right/Left joins on DynamicViewEntity
>>>>>>>> 2) How to implement my whereString
>>>>>>>>
>>>>>>>>
>>>>>>>> Thank you in advance,
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>
>>>>
>>>>
>>
>
>