app engine's back referencing is too slow. How can I make it faster?

Question

Google app engine has smart feature named back references and I usually iterate them where the traditional SQL's computed column need to be used.

Just imagine that need to accumulate specific force's total hp.

class Force(db.Model):
  hp = db.IntegerProperty()
class UnitGroup(db.Model):
  force = db.ReferenceProperty(reference_class=Force,collection_name="groups")
  hp = db.IntegerProperty()
class Unit(db.Model):
  group = db.ReferenceProperty(reference_class=UnitGroup,collection_name="units")
  hp = db.IntegerProperty()

When I code like following, it was horribly slow (almost 3s) with 20 forces with single group - single unit. (I guess back-referencing force reload sub entities. Am I right?)

def get_hp(self):
    hp = 0
    for group in self.groups:
        group_hp = 0
        for unit in group.units:
            group_hp += unit.hp
        hp += group_hp
    return hp

How can I optimize this code? Please consider that there are more properties should be computed for each force/unit-groups and I don't want to save these collective properties to each entities. :)

Answer 1

It appears that Force, UnitGroup and Unit would fit well into an Entity Group. Is it true that each Unit belongs to one and only one UnitGroup and that each UnitGroup belongs to one and only one Force? If that is true, then you can store these entities as an entity group and use an ancestor query to reduce the number of datastore queries.

Putting entities in a entity group is accomplished by setting its parent property when it is created. Once that is done, you can get all of the Units that belongs to a force with a single ancestor query.

That being said, the right thing to do is to compute the values when you write the entities to the database, not when you read them. This simple fact has been noted over and over again here and elsewhere, and it is the best way to get good performance out of your AppEngine application. It might seem like a lot of work up front, and it is very counter-intuitive for anyone who has a background with traditional SQL databases, but it is what you want to do, plain and simple.

Answer 2

Storage is cheap on App Engine, and CPU and request times less so. The predominant thing you should remember about the datastore is that you should optimize for reads, not writes -- you will end up reading your data far more often than you write it.

If you think you may ever need to know a Force's total hp, you should store it in a field called total_hp, and update it with the new value each time you update/add/remove its UnitGroups and Units. You should probably do this in a transaction, too, which means they'll need to be in the same Entity Group.