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,