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.contractor_id,count(*) as tcnt,
(SELECT count(*) FROM work_order t2 WHERE
t1.contractor_id=t2.contractor_id and rrstm=1 and rcdt is NULL) as r1,
(SELECT count(*) FROM work_order t3 WHERE
t1.contractor_id=t3.contractor_id and rrstm=2 and rcdt is NULL) as r2,
(SELECT count(*) FROM work_order t4 WHERE
t1.contractor_id=t4.contractor_id and rrstm=3 and rcdt is NULL) as r3,
SELECT count(*) FROM work_order t5 WHERE
t1.contractor_id=t5.contractor_id and rrstm=4 and rcdt is NULL) as r4,
(SELECT count(*) FROM work_order t6 WHERE
t1.contractor_id=t6.contractor_id and rrstm=5 and rcdt is NULL) as r5,
(SELECT count(*) FROM work_order t7 WHERE
t1.contractor_id=t7.contractor_id and rrstm=6 and rcdt is NULL) as r6,
SELECT count(*) FROM work_order t8 WHERE
t1.contractor_id=t8.contractor_id and rcdt is NULL) as open_count,
(SELECT count(*) FROM work_order t9 WHERE
t1.contractor_id=t9.contractor_id and vendor_rec is not NULL) as
Ack_count,
(SELECT count(*) FROM work_order t10 WHERE
t1.contractor_id=t10.contractor_id and (rtyp is NULL or rtyp<>'R') and
rcdt is NULL) as open_norwo
FROM work_order t1,contractor WHERE
t1.contractor_id=contractor.contractor_id and
contractor.tms_user_id is not NULL GROUP BY
ckey,cnam,t1.contractor_id ORDER BY cnam
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!