Optimization of Hierarchical Data Propagation I have a system in existence which works on parent-child model.


They share common attributes, as they belong to same class.
A parent can enforces certain data on any user selected attribute, and the data is required to be propagated down the hierarchy for the same attribute in all children and their children.
For a given attribute, if a child's parent dies (object gets destroyed) or child has not been enforced by parent, child can behave the same way, like in point #2, (on user request) for its children.


My system is pretty big (in database). For example:


Each object of the class has 150 atributes.
There are in total about 8 million attributes for objects created. Hence, the objects are close to 53,333.
There is a single parent for the objects. Its a big tree.


If I want to enforce data on the top most parent for a single attribute, it takes about 8 mins for it to complete.

Does anyone know a model which can perform faster than hierarchical model, but similar to behaviour?
I am guessing that you used a technique known as "Adjacency list" to store your tree in the database. However, other solutions are also available, including "Path enumeration", "Nested sets" and "Closure table". The latter one, closure table, is the best one and you may want to design again your database using this technique for storing your hierarchical data.

For more information, see these slides, in particular the section on naive trees which starts at the slide #48. Or, this book from the same author. Finally, a more formal treatment of this subject is also included in this book.