souLTower (1/23/2013)

---

The execution plans are pretty big. I'm not sure how to post them.

I get completely different execution plans between fldValue=123 and fldValue IN (select value from tempTable). I know that the optimizer is a mystery but to me, I'm asking for the same thing. It boils down to:

A) where fldValue=CONSTANT

B) where fldValue IN (CONSTANT, CONSTANT2, etc)

C) where fldValue IN (Select from table with 1 row)

Execution plan and performace for A and B is the same. Very fast.

C is terrible

Is there a hint or something I can try? I've tried adding an index to the temp table, using a table variable, no joy. I'm left with:

Create the IN () list as a SQL variable and run the query dynamically.

Query the temp table, get the value I want row by row, query the main query for each value and put the results in a temp table. Then query the temp table.

All of those ideas seem stupid to me.

Thanks for any additional thoughts.

ST