Poor performance with multiple updates

Question

Poor performance with multiple updates

AlanB

SSC Eights!

Points: 845
More actions
March 12, 2008 at 8:21 am

#70881

I'm troubleshooting a system that is able to process data overnight, but takes forever to complete during the day when other users are on the server.
I think I have narrowed the problem down to a DTS package that contains an Execute SQL Task containing about 700 rows of code comprising about 100 UPDATE statements. The UPDATE statements use inner joins to join two or three tables to copy an id field from one table to another.
I thought that the code would work more efficiently as a stored procedure, so I took the first 23 blocks and created a stored procedure. This procedure took 57 minutes to run. However, if I run each UPDATE blocked separately, it runs in a total time of 17 minutes. Presuming the server was under a similar load, I can't understand the difference.
Is this difference indicative of anything? 17 minutes would be quite acceptable, but I don't want to have to run individual UPDATE statements each time.
Any help appreciated.
Alan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

You might try adding [font="Courier New"]WITH (NoLock)[/font] to your joins, if that is acceptable for your database.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Are all the updates running as one transaction? You may want to try wrapping each update in a begin-commit block.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

AlanB SSC Eights! Points: 845 More actions · Answer 3

Thanks for the suggestions.

Wrapping each UPDATE statement in a BEGIN TRANSACTION..COMMIT block has made a huge improvement.

Cheers!

Alan

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Is the same table UPDATEd in each statement? If so, the batch would run very much faster if it was written as a set-based update (UPDATE...FROM...)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

AlanB SSC Eights! Points: 845 More actions · Answer 5

Chris...please could you elaborate? I'm not exactly sure what you mean...

Alan

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Sure Alan. If you are only updating a fixed number of columns in one table using data from another table, you can do it with a single statement, written something like this:

UPDATE u SET column1 = j.columna, column2 = j.columnb

FROM TableToBeUpdated u

INNER JOIN TableWithNewValues j

ON j.joincolumn = u.joincolumn

It's a long shot I suppose - but could your new values be put into a temp table (with a join column of course) with the final UPDATE running as a single statement?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ernie marquez SSC Rookie Points: 47 More actions · Answer 7

Ernie marquez

SSC Rookie

Points: 47

May 12, 2008 at 11:51 am

#813984

Alan?

Ernie