On adapter this controls how prefetches are performed, subqueries or a list of parameters. when under the threshold uses the parameter list, when over uses a sub query. As I understand it one of the reasons for this is a limit on the number of parameters.

I think there needs to be a third way. When the parententity fetch is expensive but is returning more than the threshold, not only is the parent entity fetch expensive but each of the prefetchs is expensive.

If the parent entity fetch was used to create a temp table containing the keys and the prefetches used (joned to) this temp table they could more cheaply return the prefetches.

If I'm looking at this right, one of my current projects takes 20 seconds to return about 3000 parent entities, the 7 prefetches then each take another 20 seconds. If I instead return 40 parents in 6 seconds the 7 prefetches each take about a second.

Am I missing something? Is this possible/practical to implement? If you can't for v2, soon after?

The threshold is used as a maximum for the # of parameters in an IN() statement which is efficient. It's set to 50 by default but that's very conservative, most times it can be 200 or even more and it's still faster than a subquery.

Each child node that's fetched will be merged with the entities fetched in the parent node. The # of entities in the parent node fetch is compared with the threshold. So if you set it to 100 and you fetch a path A-B-C-D, and you have 50 A, 300 B's and 90 C's it will use for the C's a subquery, but for the B's and D's it will use a parameterized query. THe problem is though that the subquery for the B filter when fetching the C's, is also used for fetching the D's, that's unavoidable. This can slow down the fetch indeed.

So what do you think of the temp table idea?

arschr wrote:

So what do you think of the temp table idea?

The temp table is hard to implement, because a prefetch path fetch is actually code build on top of the available fetch methods. This means that when it fetches a node, it doesn't really know anything else about the path, besides the parent data fetched (if any).

Of course not impossible, but not that easy as well. I do understand what you mean though: an expensive filter for parent entities burdens the whole path fetch, as it has to be re-used with every child node in the path. So if those filter results could be stored somewhere, it could be more efficient, even if that stored solution is relatively slow.

For now I can't implement it, perhaps in the future. I'll add it to the todo list so it will be reconsidered in a future version.

Thanks