SELECT a.cProduct_id,a.cProduct_Name,a.cStatus_id, (SELECT TOP 1 cItemCode FROM Product_Codes WHERE cCode1 = '10044444055555') AS cCode1, (SELECT TOP 1 cItemCode FROM Product_Codes WHERE cCode2 = '10044444055555') AS cCode2, (SELECT TOP 1 cItemCode FROM Product_Codes WHERE cCode3 = '10044444055555') AS cCode3 FROM Product a WHERE a.cStatus_id = 'RELE'Table "Product_Codes" has index for fields "cCode1","cCode2", and "cCode3".
"WHERE a.cStatus_id = 'RELE' AND a.iProduct_id = a.iProductGroup_id "then the query instead of taking a second, takes over 3 minutes to run. I noticed that the last 2 queries (on "Product_Codes") are using the index of the field "cCode1" (which is clustered by the way) for some reason and doing a "Clustered index SCAN".