How to speed up this SQL statement

[gimme_this_gimme_that]

I want to execute the following SQL statement for a single emp.emp_id.

Just adding and emp.emp_id=256 to the end of the statement results in
a SQL statement that on average takes 5.5 seconds.

I've tried various permutations of the statement where I add "and
emp.emp_id=256" or "em.emp_id=256" but fundamentally, I don't know how
to approach optimizing this statement for a single row.

Your tips would be of help.

Thanks.
SELECT emp.emp_id,
emp.first_name,
emp.middle,
emp.last_name,
emp.login,
emp.email,
emp.emp_number,
emp.cube,
emp.phone,
emp.workstation,
coalesce(departments.corp_department, '') corp_department,
coalesce(groups.corp_group, '') corp_group,
building.building ,
emp_type.emp_type,
user_type.user_type,
emp.office,
emp.my_favourites,
emp.flag,
emp.last_visited_date,
emp.no_of_visits FROM bobnet.employee emp
LEFT OUTER JOIN (SELECT em.emp_id, gl.group_name corp_department FROM
employee em
LEFT OUTER JOIN emp_group eg ON em.emp_id=eg.emp_id
LEFT OUTER JOIN bobnet.group_lookup gl ON eg.group_id
=gl.group_id
WHERE gl.attribute='Corp Department' ) departments
ON emp.emp_id = departments.emp_id
LEFT OUTER JOIN (SELECT em.emp_id, gl.group_name corp_group FROM
employee em
LEFT OUTER JOIN bobnet.emp_group eg
ON em.emp_id=eg.emp_id
LEFT OUTER JOIN group_lookup gl
ON eg.group_id =gl.group_id
WHERE gl.attribute='Corp Group' ) groups
ON emp.emp_id=groups.emp_id
LEFT OUTER JOIN (SELECT em.emp_id, gl.group_name building FROM
employee em
LEFT OUTER JOIN bobnet.emp_group eg
ON em.emp_id=eg.emp_id
LEFT OUTER JOIN group_lookup gl
ON eg.group_id =gl.group_id
WHERE gl.attribute='Building' ) building
ON emp.emp_id=building.emp_id
LEFT OUTER JOIN (SELECT em.emp_id, gl.group_name emp_type FROM
employee em
LEFT OUTER JOIN bobnet.emp_group eg
ON em.emp_id=eg.emp_id
LEFT OUTER JOIN group_lookup gl
ON eg.group_id =gl.group_id
WHERE gl.attribute='Corp EmpType' ) emp_type
ON emp.emp_id=emp_type.emp_id
LEFT OUTER JOIN (SELECT em.emp_id, gl.group_name user_type FROM
employee em
LEFT OUTER JOIN bobnet.emp_group eg
ON em.emp_id=eg.emp_id
LEFT OUTER JOIN group_lookup gl
ON eg.group_id =gl.group_id
WHERE gl.attribute='UserType' ) user_type
ON emp.emp_id=user_type.emp_id
where emp.phone <'111-111-1111' and user_type.user_type='Corporate'

[Lennart]

gi*******************@yahoo.com wrote:

I want to execute the following SQL statement for a single emp.emp_id.

Just adding and emp.emp_id=256 to the end of the statement results in
a SQL statement that on average takes 5.5 seconds.

I've tried various permutations of the statement where I add "and
emp.emp_id=256" or "em.emp_id=256" but fundamentally, I don't know how
to approach optimizing this statement for a single row.

Your tips would be of help.

First, do you have sufficient indexes and updated statistics? You can
use db2advis to see what db2 thinks of indexes (dont take it's word for
granted, but it is a good help).

Second, dont do outer joins unless you really need them. Do you really
need to do outer joins in the subselects? Example:

LEFT OUTER JOIN
(SELECT em.emp_id, gl.group_name corp_department
FROM employee em
LEFT OUTER JOIN emp_group eg
ON em.emp_id=eg.emp_id
LEFT OUTER JOIN bobnet.group_lookup gl
ON eg.group_id=gl.group_id
WHERE gl.attribute='Corp Department' ) departments
....

Couldnt you do this instead?

LEFT OUTER JOIN
(SELECT em.emp_id, gl.group_name corp_department
FROM employee em
INNER JOIN emp_group eg
ON em.emp_id=eg.emp_id
INNER JOIN bobnet.group_lookup gl
ON eg.group_id=gl.group_id
WHERE gl.attribute='Corp Department' ) departments
....

etc for the other subqueries.
/Lennart

[...]

[Lennart]

gi*******************@yahoo.com wrote:

I want to execute the following SQL statement for a single emp.emp_id.

Just adding and emp.emp_id=256 to the end of the statement results in
a SQL statement that on average takes 5.5 seconds.

I've tried various permutations of the statement where I add "and
emp.emp_id=256" or "em.emp_id=256" but fundamentally, I don't know how
to approach optimizing this statement for a single row.

Your tips would be of help.

Is (for example) bobnet.employee <employee?
/Lennart

[Brian Tkatch]

On Fri, 06 Jul 2007 17:26:27 -0700, "gi*******************@yahoo.com"
<gi*******************@yahoo.comwrote:

>I want to execute the following SQL statement for a single emp.emp_id.

Just adding and emp.emp_id=256 to the end of the statement results in
a SQL statement that on average takes 5.5 seconds.

I've tried various permutations of the statement where I add "and
emp.emp_id=256" or "em.emp_id=256" but fundamentally, I don't know how
to approach optimizing this statement for a single row.

Your tips would be of help.

That a lot of OUTER JOINs. Perhaps you can use sub-selects instead?

B.

[Tonkuma]

On Jul 7, 5:09 pm, Lennart <[e-mail]:

>
Is (for example) bobnet.employee <employee?

/Lennart

If it is true that bobnet.employee = employee and bobnet.emp_group =
emp_group, original query can be much simplified.

Comparing each subquery, all have same phrase(by ignoring schema is
specified or not) as following.

LEFT OUTER JOIN (SELECT em.emp_id, gl.group_name XXXXXXXXX FROM
bobnet.employee em
LEFT OUTER JOIN emp_group eg
ON em.emp_id=eg.emp_id
......
)
yyyyyyyy
ON emp.emp_id = yyyyyyyy.emp_id

I think those are redundant. Following query might be equivalent
original query.
SELECT emp.emp_id,
emp.first_name,
emp.middle,
emp.last_name,
emp.login,
emp.email,
emp.emp_number,
emp.cube,
emp.phone,
emp.workstation,
coalesce(departments.group_name, '') corp_department,
coalesce(groups.group_name, '') corp_group,
building.group_name,
emp_type.group_name,
user_type.group_name,
emp.office,
emp.my_favourites,
emp.flag,
emp.last_visited_date,
emp.no_of_visits
FROM bobnet.employee emp
LEFT OUTER JOIN
bobnet.emp_group eg
ON emp.emp_id=eg.emp_id
LEFT OUTER JOIN
group_lookup departments
ON eg.group_id = departments.group_id
AND departments.attribute='Corp Department'
LEFT OUTER JOIN
group_lookup groups
ON eg.group_id =groups.group_id
AND groups.attribute='Corp Group'
LEFT OUTER JOIN
group_lookup building
ON eg.group_id =building.group_id
AND building.attribute='Building'
LEFT OUTER JOIN
group_lookup emp_type
ON eg.group_id =emp_type.group_id
AND emp_type.attribute='Corp EmpType'
LEFT OUTER JOIN
group_lookup user_type
ON eg.group_id =user_type.group_id
AND user_type.attribute='UserType'
WHERE emp.phone <'111-111-1111'
AND user_type.group_name='Corporate'
;

And as Lennart already pointed out, it is worth to replace some LEFT
OUTER JOIN with INNER JOIN, if possible.
I thought some candidates to replace with INNER JOIN are
1) LEFT OUTER JOIN
bobnet.emp_group eg
ON emp.emp_id=eg.emp_id

2) LEFT OUTER JOIN
group_lookup building
ON eg.group_id =building.group_id
AND building.attribute='Building'

3) LEFT OUTER JOIN
group_lookup emp_type
ON eg.group_id =emp_type.group_id
AND emp_type.attribute='Corp EmpType'

4) LEFT OUTER JOIN
group_lookup user_type
ON eg.group_id =user_type.group_id
AND user_type.attribute='UserType'

Because, 1) is used in all subquery. So, it might be that there is
always matching row.
For 2), 3) and 4), COALESCE is not used in SELECT list, while for
departments.group_name and groups.group_name, COALESCE is used.

These guesses might be too over imaginative.
Only, gimme_this_gimme_that@yah*oo.com(originator of this thread)
would know truth.

[Lennart]

Tonkuma wrote:

On Jul 7, 5:09 pm, Lennart <[e-mail]:

>>
Is (for example) bobnet.employee <employee?

/Lennart

If it is true that bobnet.employee = employee and bobnet.emp_group =
emp_group, original query can be much simplified.

Yes, that is my thought exactly

I think those are redundant. Following query might be equivalent
original query.
SELECT emp.emp_id,
emp.first_name,
emp.middle,
emp.last_name,
emp.login,
emp.email,
emp.emp_number,
emp.cube,
emp.phone,
emp.workstation,
coalesce(departments.group_name, '') corp_department,
coalesce(groups.group_name, '') corp_group,
building.group_name,
emp_type.group_name,
user_type.group_name,
emp.office,
emp.my_favourites,
emp.flag,
emp.last_visited_date,
emp.no_of_visits
FROM bobnet.employee emp
LEFT OUTER JOIN
bobnet.emp_group eg
ON emp.emp_id=eg.emp_id
LEFT OUTER JOIN
group_lookup departments
ON eg.group_id = departments.group_id
AND departments.attribute='Corp Department'
LEFT OUTER JOIN
group_lookup groups
ON eg.group_id =groups.group_id
AND groups.attribute='Corp Group'
LEFT OUTER JOIN
group_lookup building
ON eg.group_id =building.group_id
AND building.attribute='Building'
LEFT OUTER JOIN
group_lookup emp_type
ON eg.group_id =emp_type.group_id
AND emp_type.attribute='Corp EmpType'
LEFT OUTER JOIN
group_lookup user_type
ON eg.group_id =user_type.group_id
AND user_type.attribute='UserType'
WHERE emp.phone <'111-111-1111'
AND user_type.group_name='Corporate'
;

It might even be simplified further (havent given it too much thought
though), by joining group_lookup only once and move the attribute='Corp
EmpType' etc to a case stmt in the select clause

SELECT emp.emp_id,
[...]
case group_lookup.attribute
when 'Building' then ... else ... end
case group_lookup.attribute
when ...

But as you point out below, it's hard to tell so we can only guess.

/Lennart

And as Lennart already pointed out, it is worth to replace some LEFT
OUTER JOIN with INNER JOIN, if possible.
I thought some candidates to replace with INNER JOIN are
1) LEFT OUTER JOIN
bobnet.emp_group eg
ON emp.emp_id=eg.emp_id

2) LEFT OUTER JOIN
group_lookup building
ON eg.group_id =building.group_id
AND building.attribute='Building'

3) LEFT OUTER JOIN
group_lookup emp_type
ON eg.group_id =emp_type.group_id
AND emp_type.attribute='Corp EmpType'

4) LEFT OUTER JOIN
group_lookup user_type
ON eg.group_id =user_type.group_id
AND user_type.attribute='UserType'

Because, 1) is used in all subquery. So, it might be that there is
always matching row.
For 2), 3) and 4), COALESCE is not used in SELECT list, while for
departments.group_name and groups.group_name, COALESCE is used.

These guesses might be too over imaginative.
Only, gimme_this_gimme_that@yah*oo.com(originator of this thread)
would know truth.

[gimme_this_gimme_that]

Thanks Lenart.

Yes the outer joins are necessary. There are many instances where a
department, building, emp_type, or user_type doesn't exist for the
employee.

[gimme_this_gimme_that]

The number of rows of this statement is not *exactly* the same as the
statement I posted.
It appears return nearly the same results and appears to return
something closer to what is actually needed.

Nice Tip!!!!

Thanks.

I think those are redundant. Following query might be equivalent
original query.
SELECT emp.emp_id,
emp.first_name,
emp.middle,
emp.last_name,
emp.login,
emp.email,
emp.emp_number,
emp.cube,
emp.phone,
emp.workstation,
coalesce(departments.group_name, '') corp_department,
coalesce(groups.group_name, '') corp_group,
building.group_name,
emp_type.group_name,
user_type.group_name,
emp.office,
emp.my_favourites,
emp.flag,
emp.last_visited_date,
emp.no_of_visits
FROM bobnet.employee emp
LEFT OUTER JOIN
bobnet.emp_group eg
ON emp.emp_id=eg.emp_id
LEFT OUTER JOIN
group_lookup departments
ON eg.group_id = departments.group_id
AND departments.attribute='Corp Department'
LEFT OUTER JOIN
group_lookup groups
ON eg.group_id =groups.group_id
AND groups.attribute='Corp Group'
LEFT OUTER JOIN
group_lookup building
ON eg.group_id =building.group_id
AND building.attribute='Building'
LEFT OUTER JOIN
group_lookup emp_type
ON eg.group_id =emp_type.group_id
AND emp_type.attribute='Corp EmpType'
LEFT OUTER JOIN
group_lookup user_type
ON eg.group_id =user_type.group_id
AND user_type.attribute='UserType'
WHERE emp.phone <'111-111-1111'
AND user_type.group_name='Corporate'
;

[Brian Tkatch]

On Thu, 12 Jul 2007 13:43:32 -0700, "gi*******************@yahoo.com"
<gi*******************@yahoo.comwrote:

>Thanks Lenart.

Yes the outer joins are necessary. There are many instances where a
department, building, emp_type, or user_type doesn't exist for the
employee.

Use sub-queries.

B.