?
Solved

SQL Optimization for JOIN

Posted on 2007-04-11
2
Medium Priority
?
487 Views
Last Modified: 2009-07-29
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.
0
Comment
Question by:DJmistral
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 18891802
select e.emp_id
        ,case when a.empid is null then 'Working' else 'Acting' end as Status
        ,coalesce(a.jobtitle,c.jobtitle) as jobtitle
from t_employee as e
left outer join t_acting as a
on e.emp_id = a.emp_id
inner join t_current as c
on a.emp_id=c.emp_id
order by ae.emp_id

0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 18891960
Hi DJ,

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.

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question