Tuning up a SQL query, (query optimization)

Question

I am trying to tune a SQL query which have IN clause in the query.

I tried replacing IN with Join and looked at the query plans.Both are looking similar in execution times, but the result is different.Can someone help me regarding this? I am using a shop database in pgadmin III. Thanks in advance. ORIGINAL QUERY:

SELECT person.id
FROM   SHOP.person
WHERE  person.id IN (SELECT personid
                     FROM   SHOP.contactperson
                     WHERE  companyid = 5);

to

SELECT person.id
FROM   SHOP.person
       JOIN SHOP.contactperson
         ON person.id = contactperson.id
WHERE  contactperson.companyid = 5;

EDITED: NOW THIS QUERY RETURNS CORRECT RESULTS:

SELECT person.id
FROM   SHOP.person
       JOIN SHOP.contactperson
         ON person.id = contactperson.personid
WHERE  contactperson.companyid = 5;

I was using contactperson.id instead of contactperson.id, and when I change it to the correct query it gave me correct results.

Performance tuning is highly vendor-specific - we need to know what concrete database (and which version) you're using. SQL is just the query language - used by the vast majority of relational database systems..... — marc_s
– marc_s, Commented Jan 5, 2014 at 14:56
@marc_s its a shop database, and I am using it in the pgadmin III. — S. N
– S. N, Commented Jan 5, 2014 at 14:57
Please show your table structures including indexes and the query plans. — Martin Smith
– Martin Smith, Commented Jan 5, 2014 at 15:03

wildplasser · Accepted Answer · 2014-01-05 15:07:15Z

2

SELECT pe.id
FROM   SHOP.person pe
WHERE  EXISTS (
   SELECT *     
   FROM   SHOP.contactperson cp
   WHERE cp.personid = pe.id
   AND  cp.companyid = 5
   );

answered Jan 5, 2014 at 15:07

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

M.Ali Over a year ago

I have checked the execution plans for your suggested query and query suggested by Darhazer they are Identical. In what situation this query can perform better then the other solution suggested by Darhazer ???

wildplasser Over a year ago

They are different. If there is an 1:N relation between person and contactperson (which I expect, but you did not ahow us the datamodel) , Darhazer's query will yield more than one row per person, and mine exactly one.

Maxim Krizhanovsky · Accepted Answer · 2014-01-05 15:01:11Z

1

Your join clause is not using the same fields as your original query. You should use personid from the contactperson table.

SELECT person.id
FROM SHOP.person 
Join SHOP.contactperson
ON person.id = contactperson.personid 
WHERE contactperson.companyid = 5;

answered Jan 5, 2014 at 15:01

Maxim Krizhanovsky

26.8k5 gold badges62 silver badges91 bronze badges

1 Comment

Martin Smith Over a year ago

This can still return different results if contactperson.personid is not unique.

Collectives™ on Stack Overflow

Tuning up a SQL query, (query optimization)

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related