0

I have a query of the following type:

SELECT *
FROM CUSTOMER C
JOIN USERTABLE U on C.AccountmanagerID = US.ID

The user table also has external users which never can be the accountmanager. This table has about 30k records of which only about 500 users can be the accountmanager. I can filter them based on the length of their username. Which gives only 1k records back.

SELECT *
FROM CUSTOMER C
JOIN USERTABLE U on C.AccountmanagerID = US.ID
AND len(U.USERNAME) = 3

When i run the query plans the second one is way more efficient, which i can imagine, but I can't find any complete reasoning behind this. Can someone enlighten me and give me some background information. I am also wondering if this is always the case or is this just coincidence.

Link to the queryplan: https://www.brentozar.com/pastetheplan/?id=HypDy3MnP

Thanks in advance

2
  • Upload the actual execution plans here and add the links to your question.
    – Dan Guzman
    Dec 12, 2020 at 20:53
  • Thank you for your feedback, i have updated my question
    – Myron
    Dec 12, 2020 at 21:02

1 Answer 1

2

The difference is your second Execution Plan is using an Index Seek (which is typically a more performant operation) on your Customer table:

Execution Plans

This is because your additional filter results in a cardinality that returns much less records and makes an Index Seek operation more applicable as opposed to an Index Scan. Notice the difference in thicknesses between the arrows in your first Execution Plan and the second one. There's a lot less rows being returned in the second plan because of your second query's JOIN predicate helping filter down the data upfront.

This also results in a more efficient operation being used to JOIN the two Tables together, a Nested Loops operator instead of Merge Join.

To answer your last question, this is neither a coincidence nor always guaranteed to be the case, Execution Plans and what the SQL Engine does under the hood is fairly complex, and therefor testing and comparing plans and results is the best way to understand what's going because speculating for a given scenario is difficult and varies from one scenario to the next.

In this case it just worked out that the filter you chose reduced enough records from the User table upfront, that the INNER JOIN to the Customer table was a lot smaller and allowed it to use an Index Seek operation which is more performant (and again also resulted in the JOIN operation to be something more efficient too, a Nested Loops operation).

You can see the reduction in records to JOIN to in your Customer table by looking at the "Estimated Number of Rows" (in this case, you should use the Actual Execution Plan instead of the Estimated Execution Plan when possible) property when you highlight over the Index Scan operation on "OSUSR_6VB_MSS_CUSTOMER" in the first Execution Plan vs the Index Seek operation in the second Execution Plan, went from 31,877 rows to approximately 472 rows:

Execution Plan 1

Execution Plan 2

2
  • 1
    Thank you so much for your detailed answer, this really helped me!
    – Myron
    Dec 13, 2020 at 6:47
  • @Myron No problem! Any other questions, feel free to ask on StackExchange, and I'll do my best to answer them.
    – J.D.
    Dec 13, 2020 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.