CODEWITHSUNDEEP
sql-serverperformance
user221295
user221295

Reputation: 1

Join queries taking more execution time than their corresponding nested queries

I have 2 tables 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
WHERE
    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 query it took 30 odd ms. For the joined query 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

Upvotes: 0

Views: 1066

Answers (4)

Prabhavith
Prabhavith

Reputation: 486

Your order of tables might reduce the performance your table order in from clause should be in increasing order of number of rows

Upvotes: 0

Justin
Justin

Reputation: 86729

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.

Upvotes: 0

Manu
Manu

Reputation: 29143

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

Upvotes: 1

Justin Swartsel
Justin Swartsel

Reputation: 3431

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.

Upvotes: 0

Related Questions