Improve performance in Query..

[Jaidev Paruchuri]

I have a table called work_order which has over 1 million records and a
contractor table which has over 3000 records.

When i run this query ,it takes long time since its grouping by
contractor and doing multiple sub SELECTs.

is there any way to improve performance of this query ??

-------------------------------------------------
SELECT ckey,cnam,t1.co ntractor_id,cou nt(*) as tcnt,
(SELECT count(*) FROM work_order t2 WHERE
t1.contractor_i d=t2.contractor _id and rrstm=1 and rcdt is NULL) as r1,
(SELECT count(*) FROM work_order t3 WHERE
t1.contractor_i d=t3.contractor _id and rrstm=2 and rcdt is NULL) as r2,
(SELECT count(*) FROM work_order t4 WHERE
t1.contractor_i d=t4.contractor _id and rrstm=3 and rcdt is NULL) as r3,
SELECT count(*) FROM work_order t5 WHERE
t1.contractor_i d=t5.contractor _id and rrstm=4 and rcdt is NULL) as r4,
(SELECT count(*) FROM work_order t6 WHERE
t1.contractor_i d=t6.contractor _id and rrstm=5 and rcdt is NULL) as r5,
(SELECT count(*) FROM work_order t7 WHERE
t1.contractor_i d=t7.contractor _id and rrstm=6 and rcdt is NULL) as r6,
SELECT count(*) FROM work_order t8 WHERE
t1.contractor_i d=t8.contractor _id and rcdt is NULL) as open_count,
(SELECT count(*) FROM work_order t9 WHERE
t1.contractor_i d=t9.contractor _id and vendor_rec is not NULL) as
Ack_count,
(SELECT count(*) FROM work_order t10 WHERE
t1.contractor_i d=t10.contracto r_id and (rtyp is NULL or rtyp<>'R') and
rcdt is NULL) as open_norwo
FROM work_order t1,contractor WHERE
t1.contractor_i d=contractor.co ntractor_id and
contractor.tms_ user_id is not NULL GROUP BY
ckey,cnam,t1.co ntractor_id ORDER BY cnam


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

Re: Improve performance in Query..

Try this:

SELECT ckey, cnam, W.contractor_id , COUNT(*) AS tcnt,
COUNT(CASE WHEN rrstm=1 AND rcdt IS NULL THEN 1 END) AS r1,
COUNT(CASE WHEN rrstm=2 AND rcdt IS NULL THEN 1 END) AS r2,
COUNT(CASE WHEN rrstm=3 AND rcdt IS NULL THEN 1 END) AS r3,
COUNT(CASE WHEN rrstm=4 AND rcdt IS NULL THEN 1 END) AS r4,
COUNT(CASE WHEN rrstm=5 AND rcdt IS NULL THEN 1 END) AS r5,
COUNT(CASE WHEN rrstm=6 AND rcdt IS NULL THEN 1 END) AS r6,
COUNT(CASE WHEN rcdt IS NULL THEN 1 END) AS open_count,
COUNT(CASE WHEN vendor_rec IS NOT NULL THEN 1 END) AS ack_count,
COUNT(CASE WHEN COALESCE(rtyp,' ')<>'R' AND rcdt IS NULL THEN 1 END)
AS open_norwo
FROM work_order AS W
JOIN contractor AS C
ON W.contractor_id = C.contractor_id
AND C.tms_user_id IS NOT NULL
GROUP BY ckey, cnam, W.contractor_id
ORDER BY cnam

Please always include DDL with your posts so that we don't have to guess at
your columns, keys and constraints.

--
David Portas
------------
Please reply only to the newsgroup
--

[Jaidev Paruchuri]

Re: Improve performance in Query..

David,

The query u replied improved the preformance from
10 mins to 6 seconds.

Thanks for the Immediate and MOST efficient query.

--Jay



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!