Home Dashboard Directory Help
Search

Poor Performance with Not In in SQL 2014 CTP2 by wBob


Status: 

Active


2
0
Sign in
to vote
Type: Bug
ID: 834582
Opened: 3/15/2014 12:00:09 PM
Access Restriction: Public
2
Workaround(s)
view
0
User(s) can reproduce this bug

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.
Details
Sign in to post a comment.
Posted by Microsoft on 3/25/2014 at 9:37 AM
Thanks for submitting this feedback. We will investigate.

--
Jos de Bruijn - SQL Server PM
Sign in to post a workaround.
Posted by wBob on 3/16/2014 at 5:47 AM
Removing last ORDER BY also give normal performance.
Posted by wBob on 3/15/2014 at 12:03 PM
Disabling new cardinality model ( eg with trace flag 9481 ) or temporarily disabling transformation rule LASJtoLASJSEL corrects performance.
Rewriting "bad" query with either LEFT JOIN or EXCEPT also restores performance.
File Name Submitted By Submitted On File Size  
SQL 2014 CTP2 Issue.png 3/15/2014 93 KB
SQL 2014 CTE Not In Performance Issue.sql 3/15/2014 1 KB