During this past time, we have done some debugging regarding the SQL statement that is generated by the DocumentHelper.
What we have found out is that for each type (ClassName) that we specify in the MultiDocumentQuery, the DocumentHelper emits a specific SQL block with two left outer joins. Moreover, each such SQL block is combined with each other using a UNION ALL statement.
In our example, if we use a MutliDocumentQuery for our three levels of the Footer structure then we end up executing an SQL statement with two UNION ALL and three blocks with two left outer joins, which seems slow on performance.
In addition, we have other similar (MutliDocument) queries that use the Path predicate which in addition to the UNION ALL and left outer joins introduce a LIKE operator in the generated SQL statement, which also is slow in terms of performance.
Is there any other way to construct a (MultiDocument) query with DocumentHelper that may alter the generated SQL statement in order to performs better?
Instead of using a single MutliDocument query, should we use two or three single document queries (as we described in our original post) since such queries will utilize the NodeID field which is an indexed field in order to expect better performance?
Thanks in advance,