Poor Performance with Not In in SQL 2014 CTP2 - by wBob

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
0
Sign in
to vote
ID 834582 Comments
Status Resolved Workarounds
Type Bug Repros 0
Opened 3/15/2014 12:00:09 PM
Access Restriction Public

Description

Very poor performance from query in SQL 2014 CTP2 using Not In.  Repro from newsgroup question here:
http://social.msdn.microsoft.com/Forums/en-US/926955b9-3e6a-40ad-ada4-30d5a84156ed/sql-server-2014-ctp2-taking-time-for-the-query?forum=sql14db#cb7861b2-c452-45b9-9337-6929771c4ddf

Same query and repro return in just a few seconds in SQL 2008 R2 and SQL 2012.
Sign in to post a comment.
Posted by Jos [MSFT] on 12/15/2014 at 9:48 AM
Thanks again for submitting this feedback.

The behavior is by design for new CE:
"In the attached bad plan, the top nest loop join will take forever to run because its outer-side has 50k rows. We pick this plan because the estimate of the outer side is 1. That is because the second join from the top is a LASJ with no predicate, so it will output all rows from left side if right side has 0 rows but 0 row if left side has even 1 row. Unfortunately, with new CE we never produce estimates less than 1. So we bump the cardinality of the right side of the LASJ from 0 to 1, and consequently, the estimate of the output is changed from 50K to 1."

As there is an effective workaround and this pattern does not appear to be common, we have decided not to fix the issue at this stage.

--
Jos de Bruijn - SQL Server PM
Posted by Jos [MSFT] on 3/25/2014 at 9:37 AM
Thanks for submitting this feedback. We will investigate.

--
Jos de Bruijn - SQL Server PM