Help with Update Query

[adimulam]

Hi All,
I wrote this following UPDATE JOIN query. It works well when I have a small amount of data in both tables. But it takes a while when I have more data. Is there any way that I can improve this? Thank you in advance!

Update ALLPOL set POLH_AGENTTOTAL_PRIOR_Q = (select sum(CSFD_QUARTER_DATA) from ALLCSFD where ALLCSFD.CSFDPOL in (select POLH_POLICY_SEQUENCE from ALLPOL as P where P.POLH_AGENT_ID=ALLPOL.POLH_AGENT_ID AND P.POLH_AGENT_HOUSEHOLD_INDICATOR=ALLPOL.POLH_AGENT_HOUSEHOLD_INDICATOR) and ALLCSFD.CSFD_ROW_LABEL='Beginning Value')

 

[mrdenny]

You'll need to take a look at the execution plan for this query to see what is really going on. Table scans are bad, so are index scans. You want index seeks they are the fastest.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[adimulam]

I have the indexes on the columns involved. But it is still not affective.

Adimulam

 

[mrees]

This is a stab in the dark - but what about..

Update ALLPOL 
set POLH_AGENTTOTAL_PRIOR_Q = 
select 
 sum(CSFD_QUARTER_DATA) 	
from 
 ALLCSFD 
join
 ALLPOL as P 
ON 
 ALLCSFD.CSFDPOL = P.POLH_POLICY_SEQUENCE  AND
 P.POLH_AGENT_ID=ALLPOL.POLH_AGENT_ID AND   P.POLH_AGENT_HOUSEHOLD_INDICATOR=ALLPOL.POLH_AGENT_HOUSEHOLD_INDICATOR

It might be completely off the mark, but I have never been a fan of correlated subqueries.

mrees

 

[mrdenny]

What does the execution plan show?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[mrees]

Ignore all of that - its rubbish Sorry! But I would try to remove the correlated subqueries. I may have another go if I get time!

mrees