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

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

avatar image By Raptor 0 asked Dec 12, 2016 at 05:12 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Jan 08, 2017 at 05:21 PM

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...

10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By Rob Farley 196 answered Jan 09, 2017 at 07:40 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: Dec 12, 2016 at 05:12 PM

Seen: 289 times

Last Updated: Jan 09, 2017 at 07:40 AM