By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,851 Members | 1,729 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,851 IT Pros & Developers. It's quick & easy.

Query Performance question

P: n/a
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
Dec 13 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
longer than before?

are statistics actual ?
do you have "good" indexes ?
On Dec 13, 11:37 am, Veeru71 <m_ad...@hotmail.comwrote:
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
Dec 14 '07 #2

P: n/a
>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 ;-)
Dec 15 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.