Sorry you haven't had much attention on this question.
So, a Merge Join is often pretty good. This Merge Join has False on the "Many-to-many" property, which is a very good thing, and despite being listed high on cost, that's more because it's being called 4036 times.
Now, that Assert and Stream Aggregate is quite possibly the bigger issue. The Assert must be checking a value which is worked out from the Stream Aggregate, and it might be needing to look through the whole table to find that. So if you're testing COUNT, then working out that COUNT(*) could be rewritten an EXISTS, which might be able to stop early quicker. Or if that doesn't help you could do a sub-query to work out the COUNT using GROUP BY, and then join on that (and maybe put TOP (999999999) on that to help persuade it to instantiate the GROUP BY results, rather than simplifying it out into something more like EXISTS).
This one mostly depends on the query itself, which doesn't seem to have come through in the plan.
By Rob Farley 121 answered May 24 at 07:57 AM