NOT IN Vs. NOT EXISTS

June 30, 2010
For some time, I have been pondering the use of NOT EXISTS in my subquery statements.

A friend of mine guided me towards this method of excluding certain records quite a while ago. So I have been using NOT EXISTS but did not quite fully understand the difference between NOT IN and NOT EXISTS.

In my particular situation a query with a four table join needed some optimizing plus certain records excluded from the query.

I played around a bit and found that using NOT EXISTS produced a substantial difference in performance. Using NOT IN on the where clause of the query took in this case 9 minutes and 35 seconds.

Using NOT EXISTS produced the results in exactly 688 milliseconds.

This is a monumental difference.

I also got a very clear understanding of what’s going on when I came across this post: NOT EXISTS vs NOT IN.

While the post ascertains that in certain circumstances, there is no difference between the two, the best thing is to test both. I will forever keep this in mind. Thanks to my colleague Bill, to Gail from SQL in the Wild and a mention to the O’Reilly book Oracle Performance Tuning which also directs you in this path. The book is authored by Mark Gurry and Peter Corrigan.

So this is what we’re talking about:

The fight between not exists and not in!

Who wins this match?

——————————————————————–
Select [columns]

From [table1]

Join [table2] on [table1.column] = [table2.column]
Join [table3] on [table1.column] = [table3.column]
Join [table4] on [table1.column] = [table4.column]

Where condition1 = ‘x’
And table1.column NOT IN ( Select [column]
From [table1] tableAlias
Where tableAlias.column = ‘xvalue’)

That took a whopping 9:35 minutes to complete.

Whereas the query below, took all of 688 milliseconds!
———————————————————————-
Select [columns]

From [table1]

Join [table2] on [table1.column] = [table2.column]
Join [table3] on [table1.column] = [table3.column]
Join [table4] on [table1.column] = [table4.column]

Where condition1 = ‘x’
And NOT EXISTS ( Select ‘xvar’
From [table1] tableAlias
Where tableAlias.column = ‘xvalue’
And tableAlias.column = table1.column)

I think in this case, we know who the winner is!

Advertisements

Successful People and their commonality!

June 17, 2010

I caught this notice on my twitter account: 50 Things Successful People have In Common.

I found it interesting and retweeded. I also went a step further, because I do like to experiment. I created a poster of the commonality between successful people.

Hopefully the image is large enough to be able to see or print clearly.

Things Successful People Have in Common

Commonality between successulf people.