Resolved: Performance Choice: IF Exists, or use Join

[yrwyddfa]

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)

[si_the_geek]

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.

[kaffenils]

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.

[szlamany]

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.

[yrwyddfa]

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.

I'll keep to joins. Thanks szlamany!

[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.

[yrwyddfa]

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.

Thanks again.