I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal. However when I know the number of rows for one query is very small I think I should be able to use hints to control the joins.
select cj.a, cv.b from (select distinct a from complexJoin) cj -- 2 rows inner loop join complexView cv on cj.a = cv.a order by cj.a, cv.b
If cj is <1s and cv <1s expect this to be <~2s but using any hint (merge/hash/loop) it is often > 1min.
I also tried to use CROSS APPLY because the docs claimed that the inner select is executed exactly once for each outer row. The query takes ~100x longer than running the inner query twice manually so perhaps I don't understand the docs.
select cj.a, cv.b from (select distinct a from complexJoin) cj -- 2 rows cross apply (select * from complexView where a = cj.a) cv order by cj.a, cv.b
If I populate a temp table with results from "cj" and then join _with_no_hint_ or use the cross apply it is fast but do I really have to resort to that? If I use the temp table and attempt "any" of the join hints (loop/merge/hash) it is slow so maybe that is a key point.
I don't believe diving into the depths of the query plan (both are complex to begin with) are required to solve this type of problem in general: I just want guaranteed isolation without resorting to a temp table--is that really not possible?