I have a query in my production environment which is taking long time to execute. I did not write this query but I must find a way to make it quicker since it is causing a big performance issue at the moment. I need to replace NOT IN with Left Join but not sure how to rewrite it. It looks like following at the moment
SELECT TOP 1 IT.ITEMID
FROM (SELECT CAST(ITEMID AS NUMERIC) + 1 ITEMID
FROM Items
WHERE ISNUMERIC(ITEMID) = 1
AND CAST(ITEMID AS NUMERIC) >= 50000) IT
WHERE IT.ITEMID NOT IN (SELECT CAST(ITEMID AS NUMERIC) ITEMID
FROM Items
WHERE ISNUMERIC(ITEMID) = 1)
ORDER BY IT.ITEMID
Kindly suggest how am I supposed to rewrite it using Left Join for better performance. Any help/guidance is greatly appreciated.