[odb-users] Parent/child datamodel and performance

[Special Gunpowder]

Hello,

I am hoping to get some advice on how to improve the performance when loading a large number of objects. I'm pretty sure my datamodel is at fault and have the freedom to change it any way I need to, but not sure how best to proceed. I searched but could not find existing questions that address my issue.

This is a simplified version of my datamodel with hopefully enough detail to explain the problem:

I have a Parent object with two lists of child objects. One child list of DbValueChild objects is implemented as a 'db value' type. The other child list of DbRelChild objects is implemented as a list of 'related' objects. Each DbRelChild also has a list of related objects (DbRelGrandchild). These four classes (simplified) are shown below.

It all works; when I load the Parent, I get all the children and grandchildren loaded as well, which is exactly what I need.

But it is slow - very slow. When I read one Parent, ODB executes one query to load all the DbValueChild items. But it looks like for the DbRelChild items, a separate query is executed for each DbRelChild and then for each DbRelGrandchild object as well.

Loading 1 Parent with 10 DbValueChild children and 50 DbRelChild children takes about 3 seconds. I don't have a specific performance target, but this is way too slow for my needs, since I need to load tens of thousands of Parent objects and as things are now this will take hours.

How can this be improved? Do I need to remodel implement all the children as db value types?

All advice welcome.
Thanks in advance.

-SpecialGunpowder


class Parent
{
private:
   std::vector<std::shared_ptr<DbValueChild>> _dbValueChildList;
   std::vector<std::shared_ptr<DbRelChild>>   _dbRelChildList;
};
#pragma db member(Parent::_dbRelChildList) value_not_null

// -------------------------------------

#pragma db value
class DbValueChild
{
private:
   unsigned long  _id;
   std::string    _name;
};

// -------------------------------------

class DbRelChild
{
private:
   unsigned long    _id;
   std::vector<std::shared_ptr<DbRelGrandchild>> _DbRelGrandchildList;
};

#pragma db member(DbRelChild::_DbRelGrandchildList) value_null unordered

// -------------------------------------

class DbRelGrandchild
{
private:
   unsigned long _id;
   unsigned long _sequence;
};