Join queries taking more execution time than their corresponding nested queries

Question

Hello, I have 2 tables namely Person_Organization and Person_Organization_other and nested query is :-

SELECT Person_Organization_id FROM Person_Organization_other 
WHERE company_name IN 
(
    SELECT company_name from Person_Organization_other
    WHERE Person_Organization_id IN
    (
        SELECT Person_Organization_Id FROM Person_Organization 
        WHERE person_id =117 AND delete_flag=0
    )
)

whereas the above query's corresponding query with join that I tried is :-

SELECT  poo.Person_Organization_id 
FROM Person_Organization_other poo, Person_Organization_other poo1, Person_Organization po
WERE poo1.Person_Organization_id=po.Person_Organization_Id 
AND po.person_id=117 
AND po.delete_flag=0 
AND poo.company_name=poo1.company_name
GROUP BY poo.Person_Organization_id

However the nested query is found to take less time as compared to it's corresponding query with joins. I used SQL profiler trace to compare times of executed queries. For the nested quey it took 30 odd ms. For the joined quiery it took 41 odd ms

I was under the impression that as a rule nested queries are less perfomant and should be "flattened out" using joins.
Could someone explain what i am doing wrong?

regards Nitin

Answer 1

By separating your tables with commas, you are effectively CROSS JOINing them together. I would try doing explicit INNER JOINs between the tables and see if that helps performance.

Answer 2

You are using cross joins. Try inner joins.

select poo.Person_Organization_id 
from Person_Organization po 
 INNER JOIN Person_Organization_other poo ON
  poo.Person_Organization_id=po.Person_Organization_Id
 INNER JOIN Person_Organization_other poo1 ON
  poo1.Person_Organization_id=po.Person_Organization_Id AND   
  poo.company_name=poo1.company_name
 where po.person_id=117 AND po.delete_flag=0 
 group by poo.Person_Organization_id

Answer 3

The view that nested queries are less performant and should be flattened out using joins is a myth - it is true that inappropriate nested subqueries can cause performance issues, however in many cases using a subquery is just as good as using a join.

In fact the SQL server optimises all queries that it executes by reducing them to an execution tree - often queries that use a JOIN end up with identical execution trees to equivalent sql statements that use nested queries instead.

In this case the execution time of these is really low anyway - the difference could just as easily be explained as due to caches etc... not being filled.

My advice would be to use whatever syntax makes more sense to you - if you have a performance problem then by all means go back and check to see if a nested subquery is the cause of your problem, however I definitely wouldn't spend time worrying about "flattening out" queries that aren't causing problems.