How to speed up this SQL statement

[gimme_this_gimme_that@yahoo.com]

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(depart ments.corp_depa rtment, '') corp_department ,
coalesce(groups .corp_group, '') corp_group,
building.buildi ng ,
emp_type.emp_ty pe,
user_type.user_ type,
emp.office,
emp.my_favourit es,
emp.flag,
emp.last_visite d_date,
emp.no_of_visit s 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.em p_id
LEFT OUTER JOIN bobnet.group_lo okup gl ON eg.group_id
=gl.group_id
WHERE gl.attribute='C orp 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_grou p eg
ON em.emp_id=eg.em p_id
LEFT OUTER JOIN group_lookup gl
ON eg.group_id =gl.group_id
WHERE gl.attribute='C orp Group' ) groups
ON emp.emp_id=grou ps.emp_id
LEFT OUTER JOIN (SELECT em.emp_id, gl.group_name building FROM
employee em
LEFT OUTER JOIN bobnet.emp_grou p eg
ON em.emp_id=eg.em p_id
LEFT OUTER JOIN group_lookup gl
ON eg.group_id =gl.group_id
WHERE gl.attribute='B uilding' ) building
ON emp.emp_id=buil ding.emp_id
LEFT OUTER JOIN (SELECT em.emp_id, gl.group_name emp_type FROM
employee em
LEFT OUTER JOIN bobnet.emp_grou p eg
ON em.emp_id=eg.em p_id
LEFT OUTER JOIN group_lookup gl
ON eg.group_id =gl.group_id
WHERE gl.attribute='C orp 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_grou p eg
ON em.emp_id=eg.em p_id
LEFT OUTER JOIN group_lookup gl
ON eg.group_id =gl.group_id
WHERE gl.attribute='U serType' ) user_type
ON emp.emp_id=user _type.emp_id
where emp.phone <'111-111-1111' and user_type.user_ type='Corporate '

[Lennart]

Re: How to speed up this SQL statement

gimme_this_gimm e_that@yahoo.co m 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.em p_id
LEFT OUTER JOIN bobnet.group_lo okup gl
ON eg.group_id=gl. group_id
WHERE gl.attribute='C orp 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.em p_id
INNER JOIN bobnet.group_lo okup gl
ON eg.group_id=gl. group_id
WHERE gl.attribute='C orp Department' ) departments
....

etc for the other subqueries.


/Lennart

[...]

[Lennart]

Re: How to speed up this SQL statement

gimme_this_gimm e_that@yahoo.co m 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]

Re: How to speed up this SQL statement

On Fri, 06 Jul 2007 17:26:27 -0700, "gimme_this_gim me_that@yahoo.c om"
<gimme_this_gim me_that@yahoo.c omwrote:

>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]

Re: How to speed up this SQL statement

On Jul 7, 5:09 pm, Lennart <erik.lennart.j ons...@gmail.co mwrote:

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

If it is true that bobnet.employee = employee and bobnet.emp_grou p =
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.em p_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(depart ments.group_nam e, '') corp_department ,
coalesce(groups .group_name, '') corp_group,
building.group_ name,
emp_type.group_ name,
user_type.group _name,
emp.office,
emp.my_favourit es,
emp.flag,
emp.last_visite d_date,
emp.no_of_visit s
FROM bobnet.employee emp
LEFT OUTER JOIN
bobnet.emp_grou p eg
ON emp.emp_id=eg.e mp_id
LEFT OUTER JOIN
group_lookup departments
ON eg.group_id = departments.gro up_id
AND departments.att ribute='Corp Department'
LEFT OUTER JOIN
group_lookup groups
ON eg.group_id =groups.group_i d
AND groups.attribut e='Corp Group'
LEFT OUTER JOIN
group_lookup building
ON eg.group_id =building.group _id
AND building.attrib ute='Building'
LEFT OUTER JOIN
group_lookup emp_type
ON eg.group_id =emp_type.group _id
AND emp_type.attrib ute='Corp EmpType'
LEFT OUTER JOIN
group_lookup user_type
ON eg.group_id =user_type.grou p_id
AND user_type.attri bute='UserType'
WHERE emp.phone <'111-111-1111'
AND user_type.group _name='Corporat e'
;

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_grou p eg
ON emp.emp_id=eg.e mp_id

2) LEFT OUTER JOIN
group_lookup building
ON eg.group_id =building.group _id
AND building.attrib ute='Building'

3) LEFT OUTER JOIN
group_lookup emp_type
ON eg.group_id =emp_type.group _id
AND emp_type.attrib ute='Corp EmpType'

4) LEFT OUTER JOIN
group_lookup user_type
ON eg.group_id =user_type.grou p_id
AND user_type.attri bute='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.gro up_name and groups.group_na me, COALESCE is used.

These guesses might be too over imaginative.
Only, gimme_this_gimm e_that@yahoo.c om(originator of this thread)
would know truth.

[Lennart]

Re: How to speed up this SQL statement

Tonkuma wrote:

On Jul 7, 5:09 pm, Lennart <erik.lennart.j ons...@gmail.co mwrote:

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

If it is true that bobnet.employee = employee and bobnet.emp_grou p =
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(depart ments.group_nam e, '') corp_department ,
coalesce(groups .group_name, '') corp_group,
building.group_ name,
emp_type.group_ name,
user_type.group _name,
emp.office,
emp.my_favourit es,
emp.flag,
emp.last_visite d_date,
emp.no_of_visit s
FROM bobnet.employee emp
LEFT OUTER JOIN
bobnet.emp_grou p eg
ON emp.emp_id=eg.e mp_id
LEFT OUTER JOIN
group_lookup departments
ON eg.group_id = departments.gro up_id
AND departments.att ribute='Corp Department'
LEFT OUTER JOIN
group_lookup groups
ON eg.group_id =groups.group_i d
AND groups.attribut e='Corp Group'
LEFT OUTER JOIN
group_lookup building
ON eg.group_id =building.group _id
AND building.attrib ute='Building'
LEFT OUTER JOIN
group_lookup emp_type
ON eg.group_id =emp_type.group _id
AND emp_type.attrib ute='Corp EmpType'
LEFT OUTER JOIN
group_lookup user_type
ON eg.group_id =user_type.grou p_id
AND user_type.attri bute='UserType'
WHERE emp.phone <'111-111-1111'
AND user_type.group _name='Corporat e'
;

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.at tribute
when 'Building' then ... else ... end
case group_lookup.at tribute
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_grou p eg
ON emp.emp_id=eg.e mp_id

2) LEFT OUTER JOIN
group_lookup building
ON eg.group_id =building.group _id
AND building.attrib ute='Building'

3) LEFT OUTER JOIN
group_lookup emp_type
ON eg.group_id =emp_type.group _id
AND emp_type.attrib ute='Corp EmpType'

4) LEFT OUTER JOIN
group_lookup user_type
ON eg.group_id =user_type.grou p_id
AND user_type.attri bute='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.gro up_name and groups.group_na me, COALESCE is used.

These guesses might be too over imaginative.
Only, gimme_this_gimm e_that@yahoo.c om(originator of this thread)
would know truth.

[gimme_this_gimme_that@yahoo.com]

Re: How to speed up this SQL statement

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@yahoo.com]

Re: How to speed up this SQL statement

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(depart ments.group_nam e, '') corp_department ,
coalesce(groups .group_name, '') corp_group,
building.group_ name,
emp_type.group_ name,
user_type.group _name,
emp.office,
emp.my_favourit es,
emp.flag,
emp.last_visite d_date,
emp.no_of_visit s
FROM bobnet.employee emp
LEFT OUTER JOIN
bobnet.emp_grou p eg
ON emp.emp_id=eg.e mp_id
LEFT OUTER JOIN
group_lookup departments
ON eg.group_id = departments.gro up_id
AND departments.att ribute='Corp Department'
LEFT OUTER JOIN
group_lookup groups
ON eg.group_id =groups.group_i d
AND groups.attribut e='Corp Group'
LEFT OUTER JOIN
group_lookup building
ON eg.group_id =building.group _id
AND building.attrib ute='Building'
LEFT OUTER JOIN
group_lookup emp_type
ON eg.group_id =emp_type.group _id
AND emp_type.attrib ute='Corp EmpType'
LEFT OUTER JOIN
group_lookup user_type
ON eg.group_id =user_type.grou p_id
AND user_type.attri bute='UserType'
WHERE emp.phone <'111-111-1111'
AND user_type.group _name='Corporat e'
;

[Brian Tkatch]

Re: How to speed up this SQL statement

On Thu, 12 Jul 2007 13:43:32 -0700, "gimme_this_gim me_that@yahoo.c om"
<gimme_this_gim me_that@yahoo.c omwrote:

>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.