How to use merge hints to isolate complex queries in SQL Server

Question

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?

Answer 1

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.

Answer 2

The optimizer bases much of it's decisions on the statistics for the tables. If they are not up to date then SQL can't make a good choice. Whenever you see poor behavior from queries, when you know that the number of rows involved is very small compared to the overall size of the tables, the first thing you should do is make sure that the statistics are up to date. This is quickly done. The usual method is reindexing the tables, but you can get similar results with commands that specifically update the statistics. The number of times that specifying hints has actually helped queries I've worked with has been extremely small.