• Post Reply Bookmark Topic Watch Topic
  • New Topic

SQL performance tuning  RSS feed

 
Bony Sen
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Can anyone tell me which is better for performance; using WHERE EXISTS or using IN clause?
 
Pat Farrell
Rancher
Posts: 4686
7
Linux Mac OS X VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It depends. It depends on tons of things. Like how many records are in the table, what index(s) you have, how unique your values are, and what RDBMS package and version you are using.

Each vendor optimizes code, especially in areas like this, nearly every release. Some do better than others, some haven't changed this part of the optimization tree in years.

The answer can only be determined heuristically. And you have to re-test when something changes.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 37181
515
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"in" is generally better if you have a short subquery. For example, seeing if a record is in a table with a small number of rows. Of course, it depends on many things - which Pat listed.
 
steve souza
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ditto to what everyone else said. With Sybase you should run a showplan and look at statistics io and cpu. These are all commands that can be issued before you run your query. Other databases probably have similar capabilities.

I know in Sybase 'not exists' often out performed 'not in'. It could better use an index. Not sure if this is true of 'in', 'exists' though.
 
Prashant Saraf
Ranch Hand
Posts: 57
Eclipse IDE Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One thing i can say about in statement is "It takes limited number of records", if your in has thousands of item then it will fail. _in_ is good for small sql.

Thanks
Prashant
 
Tim Holloway
Bartender
Posts: 18663
71
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Prashant Saraf wrote:One thing i can say about in statement is "It takes limited number of records", if your in has thousands of item then it will fail. _in_ is good for small sql.

Thanks
Prashant


I presume you mean using literal values for the "IN" clause. Sooner or later, you'll probably exceed the SQL parser's character buffer size. If the IN criteria are from a SELECT, that would operate differently, and would depend both on the database internals and on the organization of the data itself.

EXPLAIN is your friend!
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!