1

I've got a query with, more or less, this structure:

SELECT 
    MAINTABLE_ID
FROM 
    MAINTABLE WITH (NOLOCK)
LEFT JOIN
    TABLE_1 WITH (NOLOCK) ON TABLE_1_FK_MAINTABLE_ID = MAINTABLE_ID
LEFT JOIN
    TABLE_2 WITH (NOLOCK) ON TABLE_2_FK_MAINTABLE_ID = MAINTABLE_ID
[...]
LEFT JOIN
    TABLE_N WITH (NOLOCK) ON TABLE_N_FK_MAINTABLE_ID = MAINTABLE_ID   
WHERE 
    TABLE_1_ID IS NULL
    AND TABLE_2_ID IS NULL
    [...]
    AND TABLE_N_ID IS NULL      

N is about 50. The table names are aliases to four part named tables. This query is pretty fast from SQL Server 2008 to SQL Server 2012. But in SQL Server 2014 (same database) it never ends. The query has been tested with different hardware configurations (these work with the others editions of SQL Server).

What's wrong with it?

Thank you.

7
4

It may be to do with the new cardinality estimator. Try running the query with this disabled. Also try the FORCE ORDER hint, eg

...your query
OPTION ( QUERYTRACEON 9481 ) -- turn off new cardinality estimator

...your query
OPTION ( FORCE ORDER )

I would also ask what foreign keys, indexes and constraints do you have in place?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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