Inexplicably Long running update query

[whool]

The following update query is taking a long time to complete - (the longest it has run is 20 minutes before being stopped.)

UPDATE SelectedContactsTbl 
   SET SumDonations2  = 
	(SELECT sum(DonationValue) FROM MailManNonProf.dbo.donations d
	  WHERE SelectedContactsTbl.ContactID = d.contactID
         )

However if I break the query into two parts as follows:

SELECT s.contactid, sum(donationvalue) dv into #table
FROM MailManNonProf.dbo.donations d, selectedcontactstbl s
WHERE d.contactid = s.contactid
GROUP BY s.contactid

UPDATE SelectedContactsTbl 
   SET SumDonations2  = 
	(SELECT sum(dv) FROM #table d
	  WHERE SelectedContactsTbl.ContactID = d.contactID
         )

… it completes very quickly, each statement taking about 6 seconds each. Can anybody explain why this is. I would much rather run it as a single statement. Any suggestions?



details:
SQL Server 2000,
MailManNonProf.dbo.donations is a view containing 1, 200, 000 recs,
SelectedContactsTbl table is contains 50,000 recs,
All tables (including those under the view) are appropriately indexed.

 

[SemperFiDownUnda]

Try this

UPDATE SelectedContactsTbl 
   SET SumDonations2  =  sum(d.DonationValue) 
  FROM SelectedContactsTbl SC
 INNER Join MailManNonProf.dbo.donations d
    ON SC.ContactID = D.ContactID

I believe the problem comes from the corrolated sub query and how it is being optimised. When ever possible you should try to make a sub query into a join. Generally speaking the DBMS will optimise them better.

Just as a extra precaution make sure that
ContactID is indexed on both tables.

 

[whool]

That was actually what I tried first but you cannot use aggregetes in UPDATE statments, viz.

Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.


There are indexes the contactid fields of both tables.

Thanks anyway SemperFi,


Yael

 

[SemperFiDownUnda]

Hmmm ok. Well There are some times that you have to use sub queries. What you did, creating a temp table, is what I suggest for certian situations. The problem might be the data spread. If you have statistically few ContactIDs in your 1.2 million row table then the optimiser may be choosing to do a full table scan because it would have to hit almost every page in the 1.2 million row table anyway. That means your query blows out to a 1,200,000x50,000 or 60,000,000,000 read/write operation.

If you look at how you did this tho it breaks it into a distinct 1,200,000 row operations producing, I'm guessing, a 50,000 row table then your 50,000 row update with a scan of a 50,000 row temp table which in your case is about 2 datapages in memory. Thus you compair 60 billion with about 1.3 million....big difference.

Don't feel bad. I once worked on a purge program that the previous programmer had 4 levels of sub queries that INGRES would die on the optimisation step. Simply doing what you did and breaking the inner 2 queries into their own creating a temp table was enough to get it not only workable but acceptable in terms of CPU cost.


One thing you can do is change

UPDATE SelectedContactsTbl 
   SET SumDonations2  = 
    (SELECT sum(dv) FROM #table d
      WHERE SelectedContactsTbl.ContactID = d.contactID
         )

to

UPDATE SelectedContactsTbl 
   SET SumDonations2  = d.dv
  FROM SelectedContactsTbl SC
 INNER Join MailManNonProf.dbo.donations d
    ON SC.ContactID = D.ContactID

Because your creation of the temp table alreadysummed them up.

 

[whool]

Actually the Est. Execution plan says that the ContactID clustered index under the donations view is used...however I guess if the index is fragmented your argument would to some extent hold true as this would also result in additional page reads.

In any event I'm going to stick with the 2 stage version, removing the reduntant sum function as you suggested.

Thanks for your time

Yael

 

[SemperFiDownUnda]

NP glad to help.