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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.