Printable View
I have two tables. I will allow a record to be returned from one table if a bit flag is set in the other. So the question:
Is it faster to perform an IF EXISTS query the the second table to see if a record exists where the bit is set, or is it better to use an inner join.
This is solely about performance, and not about pure set semantics.
Cheers,
M
(Using SQL 2k)
Things like this depend on a few factors, such as the number of rows in each table and the indexes you have..
I would expect that Exists would be slower (as you need to run a query per row), but an IN sub-query would be at roughly the same level as a Join.
I would definitely recommend trying the options out to see which is fastest in your situation.
As si_the_geek says: It depends.
If you are running SQL Server, I would recommend that you execute both queries and display the Actual Exection Plan. This will give you a good indication on which query is faster. If you have never analyzed a execution plan it can be quite time consuming to understand in the beginning, but once you get the hang of it it will give you a lot of important information about the performance of a query.
As already said you can find this out yourself by looking at execution plans...
But keep in mind, as a general rule, that a JOIN is a natural (thus fast) concept for the query optimizer and query executer to perform.
a WHERE EXISTS IN (sub-query) is much less clear. If the sub-query has a WHERE clause that relies on a row value from the main-query it's going to be executed for each and every row's consideration - that's expensive.
Many thanks. I've used the Query Optimiser for years, and it really doesn't show a great deal of difference on a single user DB - hence useless. I needed corroborated ideas that will enable queries to be run in a multi user scenario - and this - which I've now confirmed, is the answer I'm looking for.Quote:
Originally Posted by szlamany
I'll keep to joins. Thanks szlamany!
Although I believe that Kaffenils suggestion was to run both queries in QUERY ANALYZER with the SHOW EXECUTION PLAN toggled to on (QUERY menu - SHOW EXECUTION PLAN).
Then the time spent in each step of the query is displayed and can be analyzed for it's effect.
As I've said, I've done this for years - it really isn't that helpful in my query unless I can get 10,000 users on board (a slight exaggeration) I just couldn't weight up the options unless I had a third opinion (yours) which swayed it in the right direction.Quote:
Originally Posted by szlamany
Thanks again.