True, but I have changed only one thing: a unique index to a nonunique index. Now call me old fashioned, but I believe that there should be either no difference in how these are implemented for a read-only scenario, or the unique index should be more efficient, hence this shouldn't cause a significant change in the plan.
This is why I have the audacity to call it a bug.
The way I see it, the plan is big, but that's not necessarily the same as complex. Big things that can be broken down in to small simple chunks are not necessarily that complex, and the indexing and constraints are the fuel to drive the problem solving on a micro level. This should scale.
I'm kinda wanting a little more consistency and control than I'm getting here.
1) Nothing to do with "old fashioned" - just not understanding the optimization process in sufficient detail. Not sure why you don't think it could be a VERY important piece of information to the optimizer that a column (or set of them) can only exist zero or one times in a table though - instead of possibly umpteen kajillion times. 🙂
2) As the plan gets big, the number of permutations (and the CPU effort and duration it takes to calculate them all) grows . . . hmm, is it geometrically or exponentially? Doesn't really matter which - both are bad. More importantly though is that every piece of statistical information used to do the math in the cost-based optimizer becomes less precise and meaningful and any skews are magnified with each layer of complexity/size/join.
I would love it if I could have more control
To coin a phrase: "You can't HANDLE more control!!!" 😀 Seriously, you are complaining vociferously about how complex your queries and views are, and the amount of effort and maintenance hastle it is to tune/refactor them. Do you honestly think having to LEARN and truly UNDERSTAND the rocket-science that is the optimization engine and how to tweak it to the Nth degree will be LESS effort?? No way Hose! There are actually many things one can do to 'override' the optimizer, and there are certainly times to do so. But thankfully 98.3% of the developers (and code) out there don't NEED to do so. And when one does need it, that is what a hired-gun is for. Get someone who DOES know the engine back and forth and who's line of work is improving SQL Server query performance.
One other thing - when one DOES override the optimizer to make a particular query fly for a given input or set of inputs, there is quite often some other set of inputs that are HORRIBLY inefficient as a result of the 'tuning'. The best I have ever done on a single sproc for tuning (just a hair under 6 ORDERS OF MAGNITUDE performance improvement) was primarily the result of removing forced index hints.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service