SELECT Barraportfolioname FROM portfolio WHERE id IN (SELECT DISTINCT i1.portfolioid FROM Import i1 LEFT OUTER JOIN Import i2 ON i1.PortfolioID = i2.PortfolioID AND i2.ImportSetID = 82 WHERE i1.ImportSetID = 83 AND i2.ID IS NULL)
is taking 0 seconds whereas following query is taking 5 seconds.
SELECT DISTINCT p.BarraPortfolioName AS name FROM Import i1 INNER JOIN Portfolio p ON p.ID = i1.PortfolioID LEFT OUTER JOIN Import i2 ON i1.PortfolioID = i2.PortfolioID AND i2.ImportSetID = 82 WHERE i1.ImportSetID = 83 AND i2.ID IS NULL;
I'm using SQL Server. Both the tables have indexes on all the columns which are being used in query i.e. portfolioid, id,importsetid.
Edit by gbn, based on OP comment
This works much better than previous two queries.
select BarraPortfolioName from ( select distinct p.BarraPortfolioName,portfolioid from import i1 inner join portfolio p on p.id=i1.portfolioid where importsetid = ? ) as p1 left outer join ( select distinct portfolioid from import where importsetid = ? ) as p2 on p1.portfolioid = p2.portfolioid where p2.portfolioid is null