I briefly touched on the issue of master/detail data retrieval way back in 2014 but I’m revisiting it again.
I have various master/detail hierarchies in my system. One in particular is quite large, being four levels deep with a total of 13 tables (1-8-3-1).
I’m not using the moAllInOneFetch option anywhere, so details are retrieved “on the fly” as I iterate through parent records. This works fine most of the time but, when working with a lot of records, performance suffers.
I have a situation where I need to load a large number of the above hierarchy. For each top-level master record, I need to iterate through the entire hierarchy. This is causing a big performance problem as literally thousands of queries are generated as I iterate through the hierarchy for each master.
Is there any way I can retrieve an entire hierarchy for a given master in one hit? I’ve messed about the moAllInOneFetch but it’s never worked correctly for me with a multi-level hierarchy.
I don’t even mind working out which detail (and sub-detail) records are required for a given master myself, if there’s some way I can manually fetch them all in one go and populate the detail tables directly?
EDIT: I just tried a quick test with a three-level hierarchy (moAllInOneFetch off everywhere) and tried pre-populating the third level with all records belonging to all second-level records for all top-level records. Unfortunately, when I then subsequently open the top-level table, it appears to throw away all these records and then re-retrieve them individually as I iterate the second level table.
Is there some way I can stop this? If I could pre-populate the lower levels with all the records I need so it doesn’t fetch them individually as I iterate their parents, that would solve the problem.