Query Performance question

[Veeru71]

We have the following query and is taking considerable longer time to
run.

select A.*, B.name,B.user_type,C.task_id, c.queue_id, c.workid
FROM A, B, C
WHERE (B.user_type <2 and B.user_type is not null)
AND C.task_id = A.task_id
AND B.queue_id = C.queue_id
AND C.queue_id IN (1, 5, 9)
AND C.ready_status IN (1,2,3)
AND C.assign_user_id IS NULL
AND A.state_code in ('MA')

Our DBA has tried to optimize this query and basically added few dummy
COALESCE functions to the WHERE clause as following....

select A.*, B.name,B.user_type,C.task_id, c.queue_id, c.workid
FROM A, B, C
WHERE (B.user_type <2 and B.user_type is not null)
AND C.task_id = A.task_id
AND B.queue_id = C.queue_id
AND C.queue_id IN (1, 5, 9)
AND C.ready_status IN (1,2,3)
AND C.assign_user_id IS NULL
AND A.state_code in ('MA')
AND C.queue_id = COALESCE(C.queue_id, C.queue_id)
AND C.task_id = COALESCE(C.task_id, C.task_id)
AND C.ready_status = COALESCE(C.ready_status, C.ready_status)
AND A.task_id = COALESCE(A.task_id, A.task_id)
What is the significance of these COALSCE functions ??
How are they supposed to alter the query plan for better performance ?

Thanks in advance

[stefan.albert]

longer than before?

are statistics actual ?
do you have "good" indexes ?
On Dec 13, 11:37 am, Veeru71 <[e-mail]:

We have the following query and is taking considerable longer time to
run.

select A.*, B.name,B.user_type,C.task_id, c.queue_id, c.workid
FROM A, B, C
WHERE (B.user_type <2 and B.user_type is not null)
AND C.task_id = A.task_id
AND B.queue_id = C.queue_id
AND C.queue_id IN (1, 5, 9)
AND C.ready_status IN (1,2,3)
AND C.assign_user_id IS NULL
AND A.state_code in ('MA')

Our DBA has tried to optimize this query and basically added few dummy
COALESCE functions to the WHERE clause as following....

select A.*, B.name,B.user_type,C.task_id, c.queue_id, c.workid
FROM A, B, C
WHERE (B.user_type <2 and B.user_type is not null)
AND C.task_id = A.task_id
AND B.queue_id = C.queue_id
AND C.queue_id IN (1, 5, 9)
AND C.ready_status IN (1,2,3)
AND C.assign_user_id IS NULL
AND A.state_code in ('MA')
AND C.queue_id = COALESCE(C.queue_id, C.queue_id)
AND C.task_id = COALESCE(C.task_id, C.task_id)
AND C.ready_status = COALESCE(C.ready_status, C.ready_status)
AND A.task_id = COALESCE(A.task_id, A.task_id)

What is the significance of these COALSCE functions ??
How are they supposed to alter the query plan for better performance ?

Thanks in advance

[Stefano P.]

>select A.*, B.name,B.user_type,C.task_id, c.queue_id, c.workid

>FROM A, B, C
WHERE (B.user_type <2 and B.user_type is not null)
AND C.task_id = A.task_id
AND B.queue_id = C.queue_id
AND C.queue_id IN (1, 5, 9)
AND C.ready_status IN (1,2,3)
AND C.assign_user_id IS NULL
AND A.state_code in ('MA')

>Thanks in advance

I can say
that I think the best thing to do is for you to reply with the query's
plan, and let some of the fine minds that frequent these parts have a
crack at tuning your query.

Regards,
--Jeff

Without suggestion from Index Advisor and/or Database Monitor (and
coming from i5/OS DB2 world) - and without knowing anything about actual
indexing and constraints (and statistics) in your db - I'd try to build
the following indexes:
Create Index A1 on A (state_code, task_id);
Create Index B1 on B (user_type, queue_id, name);
Create Index C1 on C (assign_user_id, ready_status, queue_id, task_id,
workid).

On DB2 for i5/OS, B.name and C.workid would not be necessary for
indexing itself but they would probably make the query run faster (as
db2 engine could use directly B1 and C1 without accessing B and C ;-)

>Thanks in advance

HTH
Stefano P.

--
"Niuna impresa, per minima che sia,
può avere cominciamento e fine senza queste tre cose:
e cioè senza sapere, senza potere, senza con amor volere"
[Anonimo fiorentino, XIV sec.]

(togliere le "pinzillacchere" dall'indirizzo email ;-)