November 14, 2015 at 12:32 AM
Thanks, that's the key thing I was missing. I was expecting it to see that there were exactly three conditions (as opposed to a variable number) and evaluate "build 3 hash tables" as a possible execution plan. Knowing that it doesn't do that completely explains the behavior I'm seeing.
It is puzzling that if, as suggested by someone else in the thread, I expand IN(a,b,c) to (x = a OR x = b OR x = c) it gets substantially faster, though still obviously falls afoul of the problem you describe above (~4 seconds instead of ~6 seconds). Should those two be equivalent?
-Steve
