Link to home
Start Free TrialLog in
Avatar of rzup
rzup

asked on

Most efficient way to update?

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

 
ASKER CERTIFIED SOLUTION
Avatar of Kevin Hill
Kevin Hill
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial