?
Solved

Most efficient way to update?

Posted on 2006-06-28
2
Medium Priority
?
809 Views
Last Modified: 2008-03-10
The issue we have is with efficiency of processing data in SQL Server with updates.

If you have one table (A) with 100k records and 200 columns and you need to update two columns from different source tables (B and C).  B will update 50k records and C will update 100k records.  Combined they will update 125k records.

Is it more efficient to update the columns in A all at once 1) or one at a time2)?

Basically the question is: does an update pull the entire record sets with all the columns into memory or just the relevant columns?

1)
UPDATE A
SET column1 = B.column1,
column2 = C.column2
FROM A
LEFT OUTER JOIN B
ON A.key = B.key
LEFT OUTER JOIN C
ON A.key = C.key
WHERE A.date = ‘2005-01-01’
AND B.type = 1
AND C.type = 2
 
2)
UPDATE A
SET column1 = B.column1,
FROM A
LEFT OUTER JOIN B
ON A.key = B.key
WHERE A.date = ‘2005-01-01’
AND B.type = 1

UPDATE A
SET column2 = C.column2
FROM A
LEFT OUTER JOIN C
ON A.key = C.key
WHERE A.date = ‘2005-01-01’
AND C.type = 2

 
0
Comment
Question by:rzup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 750 total points
ID: 17002834
>>Basically the question is: does an update pull the entire record sets with all the columns into memory or just the relevant columns?<<

Pulls the entire page the the relevant record is on, not just columns...I suspect it pulls all of the records to be able to do it all in one transaction without paging, assuming there's enough RAM in the box
0
 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 750 total points
ID: 17007072
Thje answer to which is more efficient depends on the data in the tables.     Look as the estimated query plans for each.   check the logical reads for each.    If concurrency is not an issue when you perform the update try using the WITH(TABLOCK) hint, as this will minimise the overhead of locking (often a contributory factor of poor performance on large updates)
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question