Update optimization - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update optimization

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-13 : 11:45:20

I have a table that has 2 updates run on it every night (around that) The tables are about 10 columns each and over 3 million entries. Nice and big hey?

Now as it's currently done 2 updates are made on the table (as I said)

UPDATE strat_pick_Dts
SET resource_interpretation_id = r.resource_interpretation_seq
from strat_pick_dts p, resource_interpretation_event_dts r
where p.bwdgtmk_dbkey = r.bwdgtmk_dbkey
go

UPDATE strat_pick_Dts
SET pick_quality = q.pick_quality
from strat_pick_dts p, r_pick_quality q
where p.pick_quality = q.legacy_code
go

my question is would this be faster as one update that joins the tables into one update (sorry if there is something wrong with the syntax... I just quickly threw it together)

UPDATE strat_pick_Dts
SET resource_interpretation_id = r.resource_interpretation_seq, pick_quality = q.pick_quality
FROM strat_pick_dts p, resource_interpretation_event_dts r, r_pick_quality q
WHERE p.bwdgtmk_dbkey = r.bwdgtmk_dbkey and p.pick_quality = q.legacy_code
go

I guess I could also use joins to bring these together... any comments on what would be the fastest for tables with this many rows?

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-13 : 12:00:18

I guess the inner join syntax would look like this

UPDATE strat_pick_Dts
SET resource_interpretation_id = r.resource_interpretation_seq, pick_quality = q.pick_quality
FROM strat_pick_dts p inner join resource_interpretation_event_dts on p.bwdgtmk_dbkey = r.bwdgtmk_dbkey inner join r_pick_quality q on p.pick_quality = q.legacy_code

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-13 : 12:07:36

You might see some improvement using the ANSI JOIN syntax:

UPDATE P
SET P.resource_interpretation_id = r.resource_interpretation_seq
FROM strat_pick_dts p
INNER JOIN resource_interpretation_event_dts r
ON p.bwdgtmk_dbkey = r.bwdgtmk_dbkey

The optimizer might be more inclined to use an index if one exists. The next suggestion would be to try index hints, but you should examine the plan first to see how the optimizer runs this update. You might also try locking hints, maybe a TABLOCK. The same thing applies if users are SELECTing from this table during the update; you may want to try using SELECT...WITH (NOLOCK) if performance is really crucial.

It's hard to say whether doing both UPDATEs with one statement will be faster, somehow I don't think it will. The one problem I can see is page splitting; if the columns being updated vary in the length of data they hold, then 2 UPDATEs can split pages each time, instead of 1 page split. This is not very likely though.

In any case, why UPDATE the column? Is it to speed up or simplify queries (instead of joining the tables together)? Also, how long does it take to run the UPDATEs now? How big are the resource_interpretation_seq and r_pick_quality tables? If these last two are pretty small (under 1000 rows) you will probably be better off JOINing them in a query rather than updating the bigger table.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-13 : 12:31:44

Unfortunately as you could tell by the table names (ending in _DTS) this table is being used to export and import data and some of the functions run on it require that all info needs to be in the one table (I've already suggested using veiws instead of updates but I guess one table is the main criteria).The dts service that includes this update actually takes over 3 hours to import and alter data. I'm unsure exactly how long the updates take, but I do now its quite some time. The size of some of the other tables are over 50k lines themselves... so I'm not sure how well a view or query joining the tables would work.

Thanks for the input though, the locking idea may help I'll have to look into it.

The entire thing gets more complicated actually... later on there is 5 more updates simular to this and all run at the same time and once again I am faced with a simular problem except this time there are 5 updates instead of 2. But for now I'm not concerning myself with that...

Thanks for the input rob... I really starting to think I should make all my posts read For : Robvolk

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-13 : 12:54:26

Well thanks rob.. Problem solved. views turned out to be much more useful in this than I thought.

I think this forum helps ya think it through as much as anything... typing out and describing the problem to other readers seems to help me find an answer pretty quickly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources