2 Replies - 4462 Views - Last Post: 05 July 2012 - 11:44 AM

#1 cupidvogel  Icon User is offline

  • D.I.C Addict

Reputation: 31
  • View blog
  • Posts: 593
  • Joined: 25-November 10

Performance comparison of In and Exists

Posted 01 July 2012 - 10:33 AM

I want to know which one of the following - Exists, or In, is faster, and why. I have done lots of Googling, and the consensus looks like that In is more efficient when the outer query has a large data-set and the inner query returns a small one, while Exists is faster for the reverse case. In fact the article at the link Oracle Tips say that it happens because in case of Exists, the outer query is run first, while in case of In, the inner query is run first. If it is so, can anyone explain why?

Is This A Good Question/Topic? 0
  • +

Replies To: Performance comparison of In and Exists

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: Performance comparison of In and Exists

Posted 04 July 2012 - 04:17 AM

It really depends on the situation, but generally speaking, EXISTS is a better choice for filtering out where there are no matches. Why? Because the EXISTS clause will stop checking after it finds the first match. IN, on the other hand, will generate the whitelist before checking against it. But as I said, it depends on the situation.

In the case where you have a known (i.e. - static) set of results to check against, as in the case of a multi-valued parameter in MSSQL, then IN is the path I choose. If I need to check against a dynamic whitelist, I'll use EXISTS.

There is another option as well, and that involves using INNER JOIN. With an INNER JOIN, if the joining attribute doesn't exist, then the INNER JOIN will filter out that row. This can be handy when you're using temporary tables, which I use often when optimisng queries, or when you have a convoluted whitelist that is comprised of data resulting from JOINs itself.

Again, I iterate, it depends on the situation and the query. IN, LIKE and JOINs on complex data (e.g. - text) are often the most laborious tasks for a DB engine, and are best avoided where possible.
Was This Post Helpful? 4
  • +
  • -

#3 cupidvogel  Icon User is offline

  • D.I.C Addict

Reputation: 31
  • View blog
  • Posts: 593
  • Joined: 25-November 10

Re: Performance comparison of In and Exists

Posted 05 July 2012 - 11:44 AM

Thanks...
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1