Efficient OSQL union query from two classes from the same ancestor

Question

I have a complex class hierarchy of this form:

- A (common ancestor)
    * B
    - C
        * D
        * E
    * F
    - G
        * H
        * I

From my application I need this kind of query:

SELECT FROM D
WHERE ...

But recently I would like also to do this kind of query:

SELECT FROM A
WHERE @class IN ['D', 'I'] AND ...

My question is how much is efficient this last query, and which is the best practice to optimize it.

Answer 1

trying your query with a sample dataset I've created

SELECT FROM A
WHERE @class IN ['D', 'I']

and i'm seeing execution time from 40ms (cold cache) to 15ms (hot cache).

Using this one, instead, I see an improvement (3ms with hot cache):

select expand($c)

let $a=(SELECT FROM D),
$b=(SELECT FROM I),
$c=unionAll($a,$b)

EDIT

here is the query using WHERE and LIMIT conditions:

select from (
  select expand($c)

  let $a=(SELECT FROM D),
  $b=(SELECT FROM I),
  $c=unionAll($a,$b)
  )
where value = 5 LIMIT 10

Hope it helps. Ivan