Loading ~ 9000 objects

[Nobody/Anonymous]

I have two classes:
a)  ProductCatalog with the following fields: ID,Name and Items (CPersistentCollection)
b) ProductCatalogItem with the following fields: ID,Name and CatalogID

Here is the mapping:
            <association fromClass="ProductCatalog"
                toClass="ProductCatalogItem"
                cardinality="oneToMany"
                target="Items"
                retrieveAutomatic="true"
                deleteAutomatic="false"
                saveAutomatic="false"
                inverse="false">
                <entry fromAttribute="ID" toAttribute="CatalogID"/>
            </association>       

In the database ProductCatalog object has ~9000 ProductCatalogItem associated objects.

Retrieval of the ProductCatalogItem objects lasts > 5 min.
Network configuration is OK.

Is it possible to load somehow these objects in one reasonable amount of time?

Thanks in advance

Sanjin

[Richard Banks]

Try setting the retrieveAutomatic="lazy".

This will cause proxy versions of the ProductCatalogItem objects to be loaded, and will be done in a single query.

The difference between a proxy and a full version of an obejct is that the proxy only populates attributes with proxy="true" (which is the default) and it does not retrieve associations for the object.

Having retrieveAutomatic="true" will force the framework to retrieve key-only information for the ProductCatalogItems in the first select, and then for each retrieved key, another query will be performed for the full retrieval of the ProductCatalogItem objects (including any of it's associated objects). resulting in 1 + ~9000 queries.

- Richard

[Nobody/Anonymous]

Here is my solution for the problem mentioned in the subject. Feel free to make comments.

I implemented manually lazy loading and ProductCatalogItem are not cached (using loadPersistentObject instead of the loadObject method)

a) mapping:

<association fromClass="ProductCatalog"
toClass="ProductCatalogItem"
cardinality="oneToMany"
target="Items"
retrieveAutomatic="false"
deleteAutomatic="false"
saveAutomatic="false"
inverse="false">
<entry fromAttribute="ID" toAttribute="CatalogID"/>
</association>

b) in the code

public CPersistentCollection Items
{
    get
    {
        if (items == null)
        {
            items = new CPersistentCollection();
            items.ContainerObject = this;

            ProductCatalogItem pcItem = new ProductCatalogItem();
            CRetrieveCriteria rc = new CRetrieveCriteria();
            rc.ClassMap = pcItem.getClassMap();

            rc.WhereCondition.addSelectEqualTo("CatalogID", this.ID);

            CCursor c = rc.perform();
            //must be false to prevent multiple retrievals of the ProductCatalogItem objects
            c.HoldsProxies = false;

            while(!c.EOF && c.hasElements())
            {
                CPersistentObject obj = (CPersistentObject)(new ProductCatalogItem());

                c.loadPersistentObject(ref obj); // objects are not cached
                //c.loadObject(ref obj);         // objects are cached, performance degradation

                items.Add(obj);

                c.nextCursor();
            }
           
            CPersistentObject pc = this;
            pbroker.addToCache(ref pc);//manually add ProductCatalog object to cache
        }
        return items;
    }
    set
    {
        items = value;
        SetDirtyFlag();
    }
}

[Richard Banks]

I've got no real problem with that method.

If it works - it's good :-)

One thing though, you can set c.HoldsProxies = true; as long as you use loadProxy in the loop you should be OK. (but I haven't checked if it's any faster or not).

- Richard.