That helps but still doesn't explain why the

where IN (sub select)

has such a different execution plan than

WHERE IN (constants)

What I've done to improve the performance (and I'm not really proud of) is to loop over the list of values, query the main view into a temp table using WHERE fldValue = syntax, then query the temp table for the final output. I tried changing indexes, join hints, lots of things but this worked best and was extensible whether querying against 1 value or 100.