I'm running into a performance problem that I've not seen before.

Currently I am getting a large graph via a chain of prefetch paths. Near the top of the graph, there is a fairly inefficient fetch due to a large filter on some fields where an index can't be applied.

However, the problem I see with speed increases in the later/deeper queries that fill the graph. In general, will the queries that get generated during the course of a graph fetch tend to take longer the deeper they go?

For some reason I had actually thought that the framework took the result of each query and used it to formulate the next query.

Thanks,

Phil

psandler wrote:

I'm running into a performance problem that I've not seen before.

Currently I am getting a large graph via a chain of prefetch paths. Near the top of the graph, there is a fairly inefficient fetch due to a large filter on some fields where an index can't be applied.

However, the problem I see with speed increases in the later/deeper queries that fill the graph. In general, will the queries that get generated during the course of a graph fetch tend to take longer the deeper they go?

For some reason I had actually thought that the framework took the result of each query and used it to formulate the next query.

Did you check whether the threshold feature would help you a bit?

The thing is that currently we indeed miss an optimization in the prefetch paths where the threshold based query is still using a subquery.

THough with deep graphs you have to take into account this: the child is fetched with the filter on the parent. If the filter on the parent is complex, the filter on the child is also complex (as it contains this filter). This is only done if the threshold is exceeded so a subquery on an actual table is required. If that's the case with ALL the nodes in a path in the graph, you thus end up with a large subquery in subquery in subquery in subquery etc. filter.

This is required in this case, because otherwise it perhaps has to pass in 5000 parent PK field values, which thus means that the query can't run.

Ahhh. I tinkered with the threshold this morning--my fetch went from taking 15 seconds down to 1. I wasn't aware of this feature. Was it new in 2.0? Maybe I just never looked at it because I never saw any kind of performance problem before.

The thing is that currently we indeed miss an optimization in the prefetch paths where the threshold based query is still using a subquery.

Do you mean that the framework could look for an opportunity to switch back to using a range after it reaches a certain depth, regardless of how many rows are returned?

Thanks for your help!

Phil

Do you mean that the framework could look for an opportunity to switch back to using a range after it reaches a certain depth, regardless of how many rows are returned?

The problem with the Range: is the number of parameteres passed. It has a limit and the longer the list the slower the query.

The optimization we currently miss is this one: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=8610

Shouldn't be too hard to add.