1

There is a rather complex SQL Server query I have been attempting to optimize for some months now which takes a very long time to execute despite multiple index additions (adding covering, non-clustered indexes) and query refactoring/changes. Without getting into the full details, the execution plan is below. Is there anything here which jumps out to anyone as particularly inefficient or bad? I got rid of all key lookups and there appears to be heavy use of index seeks which is why I am confused that it still takes a huge amount of time somehow. When the query runs, the bottleneck is clearly CPU (not disk I/O). Thanks much for any thoughts.

execution plan

trance_dude
  • 91
  • 1
  • 7
  • 2
    Can you also show your query and table/index structures? – Siyual Sep 14 '17 at 19:18
  • 2
    Also, upload the actual XML plan to https://www.brentozar.com/pastetheplan/ – Dan Guzman Sep 14 '17 at 19:32
  • 1
    `Is there anything here which jumps out to anyone as particularly inefficient or bad?` Yes. These 3 scans on the inside of nested loops joins.https://i.stack.imgur.com/RrUxh.png – Martin Smith Sep 14 '17 at 20:01
  • Thanks for the comments guys. Dan Guzman, that is a cool site, will take a look. @Martin Smith, what makes you single these scans out given that they only take up 4% each of the total query cost? Also, all three of those scans refer to tables with only a few entries each. Perhaps this is why it is choosing to scan instead of seek. Does this fact affect your statement at all? thx – trance_dude Sep 14 '17 at 23:19
  • Upvoted as you actually posted a query plan and actually looked at it. Most people say help me make this query run faster, never post the execution plan and expect a solution. Well cone on this. – Namphibian Sep 15 '17 at 02:50
  • Only just noticed that you asked me a question there. I don't know the sizes of the tables involved as you only posted an image not the much more useful plan XML. But scans on the inside of nested loops are often problematic as, if the outer query returns more rows than expected, they will be executed more than expected. And costing of scans under semi joins and anti semi joins can often be wrong. They are costed on the basis that only a partial scan will be needed but if there is no match that allows the scan to exit early it will need to scan the whole thing. – Martin Smith Sep 17 '17 at 14:00

1 Answers1

0

OK so I made a change based on Martin's comments which have seemingly greatly helped the query speed. I'm not 100% positive this is the solution bc I've been running this a lot and it's possible that so much underlying data has been put into memory that it is now fast. But I think there is actually a true difference.

Specifically, the 3 scans inside of the nested loops were being caused by sub-queries on very small tables that contain a small set of records to be completely excluded from the result set. Conceptually, the query was something like:

SELECT fields
FROM (COMPLEX JOIN)
WHERE id_field NOT IN (SELECT bad_ID_field FROM BAD_IDs)

the idea being that if a record appears in BAD_IDs it should never be included in the results.

I tinkered with this and changed it to something like:

SELECT fields
FROM (COMPLEX JOIN)
LEFT JOIN BAD_IDs ON id_field = bad_ID_field
WHERE BAD_IDs.bad_ID_field IS NULL

This is logically the same thing - it excludes results for any ID in BAD_IDs - but it uses a join instead of a subquery. Even the execution plan is almost identical; a TOP operation gets changed to a FILTER elsewhere in the tree, but the clustered index scan is still there.

But, it seems to run massively faster! Is this to be expected? I have always assumed that a subquery used in the fashion I did was OK and that the server would know how to create the fastest (and presumably identical, which it almost is) execution plan. Is this not correct?

Thx!

trance_dude
  • 91
  • 1
  • 7
  • 2
    Generally I rewrite all `NOT IN (SELECT...` to `NOT EXISTS`. Once upon a time this could be better optimised by the query planner. In later version of SQL this might make no difference at all. – Nick.Mc Sep 15 '17 at 00:49
  • @Nick.McDermaid - it depends on the columns nullability. I assume that they are `not null` as I don't see the extra apparatus in the original plan to deal with posssible nulls. https://stackoverflow.com/a/11074428/73226 – Martin Smith Sep 15 '17 at 07:35
  • what does the new plan look like? – Martin Smith Sep 15 '17 at 07:37