+ Start a Discussion

Limit to the number of child records for a roll-up summary?

I have an application that deals with an extremely large number of records. It includes a child object that has a lookup relationship to a parent object, where a single parent record could have a hundred thousand (100,000) or more child records. 


The schema uses a lookup relationship, rather than a master/detail relationship, because a user may want to move a child record from one parent to another.


I need to compute several roll-up summary values on the parent object to count the number of child records, the number of distinct values in a particular field, the min and max of values in other fields, etc. Because I'm not using a master/detail relationship, I can't use roll-up summary fields, so I wrote a scheduled Batch Apex process to perform the calculations. Unfortunately, I'm finding that Batch Apex just can't handle this reliably. (See http://boards.developerforce.com/t5/Apex-Code-Development/Batch-Apex-problems/m-p/244937 for just some of the problems I've encountered.)


So now I'm considering whether to switch to a master/detail relationship. I can write some Apex code to handle the situation where a user wants to move a child to a different parent -- in that case, I'll clone the child record, change the clone to refer to the new parent, and then delete the old child record. But now I'm wondering whether roll-up summary fields can handle very large numbers of child records. 


Has anyone worked with roll-up summary fields that compute counts, counts distrincts, sums, mins, and maxes over an extremely large number of child records? If so, what's your experience with it? Does it perform reasonably well? Or does it take a long time to compute new values when a child record is touched?


Thanks very much for your input!