Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with Update Query

Status
Not open for further replies.

adimulam

Programmer
Feb 13, 2002
25
US
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')
 
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)

[noevil]
(My very old site)
 
I have the indexes on the columns involved. But it is still not affective.

Adimulam
 
This is a stab in the dark - but what about..
Code:
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
 
What does the execution plan show?

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

[noevil]
(My very old site)
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top