I am building a feature which will conditionally need to output entity data. The entity type is a custom one I created, which is complex and implements multiple nested field collections.
My issue is that looping through entities using
Entity::loadMultiple() is very slow, as each entity needs to use
FieldCollectionItem::load() multiple times to get to the data it needs to output. Over 1000 entity instances this uses up a lot of resources and takes a long time to load.
I am trying to circumvent this by retrieving the data via a SQL query and joining tables together. I'm hitting a road block when retrieving data from field collection items with long machine names, as the data appears to be stored in tables with names like
field_collection_item__5d28be6634, basically just "field_collection_item__" with a 10-character hexadecimal hash added to it.
This would not theoretically be a big issue, except it looks like all records for the same field collection item seem to be split up across many of these tables with no pattern or connection that I can discern.
How do I know which tables to retrieve my data from for any given parent field collection instance?
Edit: I have revisited this recently and thought some of these tables might be revision tables, but none of them include the "_r_" in the table name. I still cannot determine which tables to use as there seem to be multiple tables (not including revision tables) for one field collection item. Looping through entities and loading nested field collections is taking upwards of 5 minutes which is just too long for my users.