souLTower (1/24/2013)
That helps but still doesn't explain why thewhere 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.
When you operate with constants SQL Server builds an execution plan considering the actual values assigned to the constants.
When it selects it from the table it jhas no idea what kind of values are in there (because the table is a heap) and it has to build more "pessimistic", but more universal plan, which might be not that good in this particular case but will perform better in average in other cases.
Add an index to your lookup table. It should help the optimizer in building more effective plan considering the current data in the table.
_____________
Code for TallyGenerator