Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a question about best approach. I am not sure which approach is best when data is considered variable in size.

Consider the following 3 TABLES:

EMPLOYEE

EMPLOYEE_ID, EMP_NAME

PROJECT

PROJECT_ID, PROJ_NAME

EMP_PROJ (many to many of above two tables)

EMPLOYEE_ID, PROJECT_ID

Problem: Given an EmployeeID, find ALL the employees of ALL Projects that this Employee is associated with.

I have tried this in two way.. both approaches differ only by few milliseconds no matter what size of data is used.

SELECT EMP_NAME FROM EMPLOYEE
WHERE EMPLOYEE_ID IN (
    SELECT EMPLOYEE_ID FROM EMP_PROJ    
    WHERE PROJECT_ID IN (
        SELECT PROJECT_ID FROM EMP_PROJ p, EMPLOYEE e
        WHERE p.EMPLOYEE_ID = E.EMPLOYEE_ID 
        AND  E.EMPLOYEE_ID = 123)

go

select c.EMP_NAME FROM
(SELECT PROJECT_ID FROM EMP_PROJ
WHERE EMPLOYEE_ID = 123) a
JOIN 
EMP_PROJ b
ON a.PROJECT_ID = b.PROJECT_ID
JOIN 
EMPLOYEE c
ON b.EMPLOYEE_ID = c.EMPLOYEE_ID

As of now, I expect around 5000 Employees and Projects each.. but have no idea about what kinda many-many relationship exists. Which approach would u recommend? thanks!

EDIT: Execution Plan of Approach 1

"Hash Join  (cost=86.55..106.11 rows=200 width=98)"
"  Hash Cond: (employee.employee_id = emp_proj.employee_id)"
"  ->  Seq Scan on employee  (cost=0.00..16.10 rows=610 width=102)"
"  ->  Hash  (cost=85.07..85.07 rows=118 width=4)"
"        ->  HashAggregate  (cost=83.89..85.07 rows=118 width=4)"
"              ->  Hash Semi Join  (cost=45.27..83.60 rows=118 width=4)"
"                    Hash Cond: (emp_proj.project_id = p.project_id)"
"                    ->  Seq Scan on emp_proj  (cost=0.00..31.40 rows=2140 width=8)"
"                    ->  Hash  (cost=45.13..45.13 rows=11 width=4)"
"                          ->  Nested Loop  (cost=0.00..45.13 rows=11 width=4)"
"                                ->  Index Scan using employee_pkey on employee e  (cost=0.00..8.27 rows=1 width=4)"
"                                      Index Cond: (employee_id = 123)"
"                                ->  Seq Scan on emp_proj p  (cost=0.00..36.75 rows=11 width=8)"
"                                      Filter: (p.employee_id = 123)"

Execution Plan of Approach 2:

"Nested Loop  (cost=60.61..112.29 rows=118 width=98)"
"  ->  Index Scan using employee_pkey on employee e  (cost=0.00..8.27 rows=1 width=4)"
"        Index Cond: (employee_id = 123)"
"  ->  Hash Join  (cost=60.61..102.84 rows=118 width=102)"
"        Hash Cond: (b.employee_id = c.employee_id)"
"        ->  Hash Join  (cost=36.89..77.49 rows=118 width=8)"
"              Hash Cond: (b.project_id = p.project_id)"
"              ->  Seq Scan on emp_proj b  (cost=0.00..31.40 rows=2140 width=8)"
"              ->  Hash  (cost=36.75..36.75 rows=11 width=8)"
"                    ->  Seq Scan on emp_proj p  (cost=0.00..36.75 rows=11 width=8)"
"                          Filter: (employee_id = 123)"
"        ->  Hash  (cost=16.10..16.10 rows=610 width=102)"
"              ->  Seq Scan on employee c  (cost=0.00..16.10 rows=610 width=102)"

So looks like the Execution plan of Approach 2 is slightly better, because the 'cost' is 60 as opposed to 85 of approach 1. Is that the right way to analyze this?

How does one know it will hold true even for all sorts of many-many combinations?

share|improve this question
1  
What RDBMS is that explain plan from? The best performing approch will probably be implementation dependant. –  Martin Smith Oct 29 '11 at 10:55
3  
Looks like a Postgres explain plan to me. Personally I'd go with the join based approach, but read some of the answers below about rewriting the query. Oh, and I'd suggest the OP use explain analyze rather than just explain. –  xzilla Oct 29 '11 at 15:28
    
I agree with xzilla: explain analyze might reveal more differences between the plans –  a_horse_with_no_name Nov 4 '11 at 7:36
add comment

3 Answers 3

You can try this query :


select distinct e2.employee_id, ep.project_id 
from employee e, employee e2, emp_proj ep
where
e.employee_id = 123
and e.employee_id = ep.employee_id
and e2.project_id = ep.project_id;
share|improve this answer
add comment

What your query is looking for is just

SELECT EMP_NAME 
FROM EMPLOYEE e
WHERE E.EMPLOYEE_ID = 123
and exists (select * from EMP_PROJ  where  EMPLOYEE_ID = 123);

or

SELECT EMP_NAME 
FROM EMPLOYEE e
WHERE E.EMPLOYEE_ID = 123
and exists (select * from EMP_PROJ ep where  ep.EMPLOYEE_ID = E.EMPLOYEE_ID );
share|improve this answer
    
Wouldn't the sub-query be faster if it was SELECT 1 instead of SELECT *? –  Daniel Serodio Jul 16 '12 at 22:49
    
Might depend on DBMS. I know for sure that SQL-Server is optimizing Select *. (cf. Itzik Ben-Gan in Microsoft® SQL Server® 2012 T-SQL Fundamentals) –  bernd_k Jul 18 '12 at 5:05
add comment

In SQL Server, with a few assumptions like "those fields can't contain NULLs", those queries should give the almost the same plan.

But also consider the type of join you're doing. An IN clause like this is a Semi Join, not an Inner Join. An Inner Join can project onto multiple rows, thereby giving duplicates (compared to using IN or EXISTS). So you may want to consider this behaviour when choosing how you write your query.

share|improve this answer
2  
I agree with the use of exists rather than a join when trying to avaoid duplication. From my own experience with SQL server was the exists and inner join produced the same query plan anyway. I did have some performance concerns about 'in' statements but they only surfaced when the select in the in statement started to return several thousand rows. –  GrumpyMonkey Oct 29 '11 at 12:08
6  
@GrumpyMonkey - In SQL Server 2005+ IN and EXISTS always give the same plan in my experience. NOT IN and NOT EXISTS are different however with NOT EXISTS preferred - Some performance comparisons here –  Martin Smith Oct 29 '11 at 12:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.