Performance imprvt in a query

Database
Enthusiast

Performance imprvt in a query

Hi!

I have query and I want to check if it can be optimized. Basically, if all the records in tableC for particular ID do not have a status of 'A' or 'B', I want to update the ID in table 1.
UPDATE table1
FROM (
SELECT A.ID, COUNT(DISTINCT C.EID)
FROM table1 A
LEFT JOIN table2 B
ON (A.ID = B.ID A)
LEFT JOIN table3 C
ON (B.EID = C.EID AND C.STAT in ('A','B') )
WHERE A.STAT_CD ='O'
GROUP BY 1
HAVING COUNT(DISTINCT c.EID) =0
) T
SET STAT='R'
WHERE table1.ID =T.ID AND STAT ='O';

Thanks!!
2 REPLIES
Fan

Re: Performance imprvt in a query

Hi,
take out A in the inner select - I belive it is useless there also you may want to check on count(distinct) because it is not necessary ( you explanation say: does not have A or B - so you do not care about distinct))
Senior Apprentice

Re: Performance imprvt in a query

Additional to sb42's comment, you could do the count before the join:

UPDATE table1
SET STAT='R'
WHERE STAT ='O'
and id in
(
select id from table2 b
where eid in
(
select eid from table3 c
group by 1
having count(case when STAT in ('A','B') then 1 end) = 0
)
)

Dieter