DJmistral
asked on
SQL Optimization for JOIN
Is there a way in DB2 SQL that allows you to pick which table data will come from in a join? Example:
TABLE t_Employee (EmpID, Name, Status)
TABLE t_Acting (ActID, EmpID, JobTitle, Salary)
TABLE t_Current(CurrID, EmpID, JobTitle, Salary)
- Everyone (old and current) in the database, always has a record in t_Employee.
- Every employee who is currently working, always has a record in t_Current with foreign key EmpID, Status = "Current"
- If an employee is currently acting in another position, they have a record in t_Acting with foreign key EmpID, Status = "Acting"
So if Emp A is working as a team-leader, Emp A has a record in t_Employee and t_Current with TEAM LEADER as job title.
If Emp B is working as a team-leader, but is currently acting as a Manager, Emp B's t_Current record will have him as TEAM LEADER in job title, however he will also have t_Acting record with MANAGER in job title.
When I want to do a query of all currently working employees, but only list their name and job-title. If I query the above example, I want a result of:
Name, Status, JobTitle
Emp A, Working, TEAM LEADER
Emp B, Acting, MANAGER
I currently have a solution that works, but is terribly slow as there is 7600 records in the employee table.
What it is now (I'm querying specific Columns in this statement, but I omitted them here for simplicity):
SELECT * FROM t_Employee INNER JOIN ((SELECT * FROM t_Acting) UNION (SELECT * FROM t_Current WHERE EmpID NOT IN (SELECT EmpID FROM t_Acting))) INNER JOIN ... a couple other small tables.
TABLE t_Employee (EmpID, Name, Status)
TABLE t_Acting (ActID, EmpID, JobTitle, Salary)
TABLE t_Current(CurrID, EmpID, JobTitle, Salary)
- Everyone (old and current) in the database, always has a record in t_Employee.
- Every employee who is currently working, always has a record in t_Current with foreign key EmpID, Status = "Current"
- If an employee is currently acting in another position, they have a record in t_Acting with foreign key EmpID, Status = "Acting"
So if Emp A is working as a team-leader, Emp A has a record in t_Employee and t_Current with TEAM LEADER as job title.
If Emp B is working as a team-leader, but is currently acting as a Manager, Emp B's t_Current record will have him as TEAM LEADER in job title, however he will also have t_Acting record with MANAGER in job title.
When I want to do a query of all currently working employees, but only list their name and job-title. If I query the above example, I want a result of:
Name, Status, JobTitle
Emp A, Working, TEAM LEADER
Emp B, Acting, MANAGER
I currently have a solution that works, but is terribly slow as there is 7600 records in the employee table.
What it is now (I'm querying specific Columns in this statement, but I omitted them here for simplicity):
SELECT * FROM t_Employee INNER JOIN ((SELECT * FROM t_Acting) UNION (SELECT * FROM t_Current WHERE EmpID NOT IN (SELECT EmpID FROM t_Acting))) INNER JOIN ... a couple other small tables.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Joining 7,600 records is trivial. You should see a sub-second response to this kind of query.
Lowfatspread's suggestion eliminates the derived tables. (Generally a very good thing.) It also generates a result set based on two joins that may well be the result of index scans.
Try Lowfatspread's query. If this doesn't come back almost instantly you've got a problem that needs to be addressed. Report back to us and if there's still an issue we'll help you to solve it.