Multiple subqueries at the same table - performance

advertisements

My company recently change database engine from paradox to ms sql 2008. It is way more faster, but we have problem with one query with low performance.

Query searches if fields NUMER from DM201211 table not exists in any of DO_WZ1, DO_WZ2 ... DO_WZ6 fields in DOK_SP table.

SELECT
Z.STATUS,COUNT(K.NUMER) AS DOC_COUNT, MIN(K.ZDNIA) AS OLDEST
FROM DM201311 K LEFT OUTER JOIN ZLEC Z ON K.DO_ZAMOWIENIA=Z.NR
WHERE NUMER NOT IN (SELECT DO_WZ1 FROM DOK_SP WHERE DO_WZ1 IS NOT NULL AND ID_DOK<>'-1') AND NUMER NOT IN (SELECT DO_WZ2 FROM DOK_SP WHERE DO_WZ2 IS NOT NULL AND ID_DOK<>'-1') AND NUMER NOT IN (SELECT DO_WZ3 FROM DOK_SP WHERE DO_WZ3 IS NOT NULL AND ID_DOK<>'-1') AND NUMER NOT IN (SELECT DO_WZ4 FROM DOK_SP WHERE DO_WZ4 IS NOT NULL AND ID_DOK<>'-1') AND NUMER NOT IN (SELECT DO_WZ5 FROM DOK_SP WHERE DO_WZ5 IS NOT NULL AND ID_DOK<>'-1') AND NUMER NOT IN (SELECT DO_WZ6 FROM DOK_SP WHERE DO_WZ6 IS NOT NULL AND ID_DOK<>'-1')
GROUP BY Z.STATUS

That worked pretty well in paradox (few secs of execution). Now in MS SQL 2008 it executes about 17 seconds. After my short investigation I've find out that multiple subqueries cause this situation. Is there any chnce to optimize it with removing subqueries?

As you see all subqueries are done for the same table. I've tried with EXIST clause but I've only get down from 17 secs to 14 secs. I believe that there is better solution.

NOTE: T-SQL is not allowed for me to approach this!


SELECT Z.STATUS,COUNT(K.NUMER) AS DOC_COUNT, MIN(K.ZDNIA) AS OLDEST
FROM DM201311 K LEFT OUTER JOIN ZLEC Z ON K.DO_ZAMOWIENIA=Z.NR
WHERE NUMER NOT IN (SELECT COALESCE(DO_WZ1, DO_WZ2, DO_WZ3, DO_WZ4, DO_WZ5, DO_WZ6)
                    FROM DOK_SP WHERE COALESCE(DO_WZ1, DO_WZ2, DO_WZ3, DO_WZ4, DO_WZ5, DO_WZ6) IS NOT NULL
                    AND ID_DOK<>'-1')
GROUP BY Z.STATUS