How to use merge hints to isolate complex queries in SQL Server 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 &lt;1s and cv &lt;1s expect this to be &lt;~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?
If you use a multi-statement UDF, then your inner select is executed exactly once for each outer row. The multi-statement UDF is treated as a black box: the execution plan will now show access to the objects used in your complex view.

On the other hand, a subquery and/or an inline UDF is flattened out by the optimizer. When this is the case, the execution plan will include access to the objects used in your complex view.