How can I further optimise this query?

colonel32 2015-03-05 15:51:23

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

Aaron Bertrand 2015-03-10 13:49:49
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.