This query is running about 15 seconds, can it be faster?

Raptor 2016-12-12 17:12:02

I think the cross database joins may be the culprit, but it was performing okay prior to the weekend.

Aaron Bertrand 2017-01-08 17:21:24
This kind of structure always makes me nervous: AND ((Column8 LIKE ?) OR (Column4 LIKE ?)… Also there are a lot of operations against this same index, and due to anonymization, we don't really have much insight into this. Object1 must be a view, yes? That contains a bunch of self-joins? Very hard to optimize what we can't see…
Rob Farley 2017-01-09 07:40:21
You have a lot of Merge Joins going on that use Object4.Column1, needing to Sort the results of a Seek against Object4.Column17 (using Index3). I would make sure that Index3 is a composite index on (Column17, Column1), rather than just on Column17. That way, you could avoid those expensive Sorts, and this query might fly.

Aaron's right that you should provide information about what's in Object1, because if those Seeks on Column17 aren't actually equality predicates, then data sorted on Column1 might not be possible.