NOT IN Subquery Optimization

Question

I have a dynamic query that runs indentifying CDs that members have not rented yet. I am using the NOT IN subquery but when I have large member table it makes them really slow. Any suggestions how to optimize the query

SELECT DVDTitle AS "DVD Title" 
FROM DVD 
WHERE DVDId NOT IN 
    (SELECT DISTINCT DVDId FROM Rental WHERE MemberId = AL240);

thanks

What are the table definitions including column nullability and indexes? — Martin Smith, Sep 14 '12 at 14:23
give us more information, like the number of rows, indexes, and so on — Gonzalo.-, Sep 14 '12 at 19:23

Aaron Bertrand · Accepted Answer · 2016-04-27T13:45:57.493

6

Using NOT EXISTS will have slightly better performance because it can "short circuit" rather than evaluating the entire set for each match. At the very least, it will be "no worse" than NOT IN or an OUTER JOIN, though there are exceptions to every rule. Here is how I would write this query:

SELECT DVDTitle AS [DVD Title] 
  FROM dbo.DVD AS d
  WHERE NOT EXISTS 
  (
    SELECT 1 FROM dbo.Rental 
     WHERE MemberId = 'AL240'
     AND DVDId = d.DVDId
  );

I would guess you will optimize performance better by investigating the execution plan and ensuring that your indexes are best suited for this query (without causing negative impact to other parts of your workload).

Also see Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

edited Apr 27 '16 at 13:45

answered Sep 14 '12 at 14:23

Aaron Bertrand

272,866
37
466
490

If neither `DVDId` column is nullable I would expect `NOT IN` and `NOT EXISTS` to have the same plan. If either are nullable the `NOT IN` version [needs to do more work though](http://stackoverflow.com/a/11074428/73226) – Martin Smith Sep 14 '12 at 14:27
@MartinSmith yes, that's true, but notice that I also eliminated the `DISTINCT` – Aaron Bertrand Sep 14 '12 at 14:28
Just default to NOT EXIST always if you're unsure. It's pretty much always the best choice for anti joins. – Erik Bergstedt Apr 27 '16 at 12:55

D'Arcy Rittich · Answer 2 · 2012-09-14T14:32:17.893

5

SELECT DVDTitle AS "DVD Title" 
FROM DVD d
left outer join Rental r on d.DVDId = r.DVDId
WHERE r.MemberId = 'AL240'
    and r.DVDId is null

Make sure you have indexes on:

d.DVDId
r.DVDId
r.MemberId

edited Sep 14 '12 at 14:32

answered Sep 14 '12 at 14:24

D'Arcy Rittich

167,292
40
290
283

@K-Seeker Out of curiosity, did you observe an actual performance improvement here? – Aaron Bertrand Sep 14 '12 at 14:58
Hi Aaron, there was a significant inprovement. I have not tested your suugestion yet but I tried the NOT EXISTS and it was not that much difference than the NO IN subquery. I will keep you posted. thanks again. Dan – K-Seeker Sep 14 '12 at 15:50

NOT IN Subquery Optimization

2 Answers2