samedi 29 novembre 2014

SELECT with lots of left join is very slow in SQL Server 2014


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.





Aucun commentaire:

Enregistrer un commentaire