How can I further optimise this query?

Struggling how to deal with the clustered index scan that seems to be the biggest performance bottleneck.

Is it to do with the Count(*), the OR joins, or the IS (NOT) NULLs? Or something else?

How would you optimise this or rewrite the query?

Thanks! Robin

Plan.queryanalysis (42.2 kB)
avatar image By colonel32 1 asked Mar 05, 2015 at 03:51 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

Something in a plan is always going to be the biggest bottleneck. You just need to decide at what point you need to worry about it.

Since this query runs in 43 milliseconds, I'm not sure how much time and effort it's worth to try and make it run faster. It's possible that an index on contno, divno and WFMClientID might reduce the I/O cost, but that's just a guess - I have no idea how many other columns are in that table, and whether the added maintenance from that index would be worth shaving a few milliseconds off the query.

avatar image By Aaron Bertrand ♦ 1.7k answered Mar 10, 2015 at 01:49 PM
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

Topics:

x641
x455
x109
x39
x24

asked: Mar 05, 2015 at 03:51 PM

Seen: 260 times

Last Updated: Mar 10, 2015 at 01:49 PM