Query Performance question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Veeru71 - 13 Dec 2007 10:37 GMT

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

Reply to this Message

jefftyzzer - 13 Dec 2007 17:54 GMT

> We have the following query and is taking considerable longer time to
> run.
[quoted text clipped - 30 lines]
>
> Thanks in advance

Adding redundant predicates is a tried-and-true (though admittedly
dated) method for attempting to influence the optimizer's choice of
table join-order, among other things.

The more selective a table looks, the more likely it is that the
optimizer will start with it, or at least use it as the outer table
in, say, an NL join. As to why s/he opted for column =
coalesce(column, column) over column = column I can't say. 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

Reply to this Message

Stefano P. - 15 Dec 2007 17:18 GMT

>> select A.*, B.name,B.user_type,C.task_id, c.queue_id, c.workid
>> FROM A, B, C
[quoted text clipped - 5 lines]
>> 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.

Signature

"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 ;-)

Reply to this Message

stefan.albert - 14 Dec 2007 13:52 GMT

longer than before?

are statistics actual ?
do you have "good" indexes ?

> We have the following query and is taking considerable longer time to
> run.
[quoted text clipped - 30 lines]
>
> Thanks in advance

Reply to this Message