CODEWITHSUNDEEP
sql-servercardinality-estimation
A.G.
A.G.

Reputation: 2149

Query that used to work in prior v2014 of SQL Server, dies in new v2019, and need Legacy Cardinality Estimator to fix. Is there a better way?

We've upgraded a SQL Server to 2019 from 2014.

In 2014 this query was fine:

INSERT INTO [schx].[US_New]
SELECT *
FROM [schx].[US_New_Stg]
WHERE FormattedID NOT IN (SELECT FormattedID
                          FROM [schx].[DLT]
                          WHERE [Type] = 'Hierarchical Root'
                         );

Upon upgrade (no code changes only SQL Server Upgrade), the query killed the process to which it belonged, instead of running for 4 minutes now it went on for 8 hours, and then it would time out.

We ended up adding this: OPTION (USE HINT ( 'FORCE_LEGACY_CARDINALITY_ESTIMATION' )) to the end of the query, and then things were fine again.

2 questions:

  1. Because we have to use the Legacy CE, does this mean that the New CE one isn't as robust as the Legacy CE?
  2. If instead of a subquery in the WHERE clause, we left-joined both and selected NOT NULL, would that remove the need for using the Legacy CE?

Thanks!

Upvotes: 0

Views: 352

Answers (0)

Related Questions