how to fix the full table scan and merge join?

avatar image By jrb2971 1 asked May 17 at 02:05 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By Rob Farley 196 answered May 24 at 07:57 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: May 17 at 02:05 PM

Seen: 51 times

Last Updated: May 24 at 07:57 AM