0

In SQL Server there seems to be a few ways to update a field with an aggregate function. The two forms I'm interested in are :

UPDATE t1
SET t1.field1 = t2.field2Sum
FROM table1 t1
INNER JOIN (select field3, sum(field2) as field2Sum
   from table2
  group by field3) as t2
on t2.field3 = t1.field3

versus

UPDATE table1
   SET field1 = (SELECT SUM(field2)
                   FROM table2 AS t2
                  WHERE t2.field3 = t1.field3)
  FROM table1 AS t1

In my application I have update queries in the second form and I'm seeing the same query (run thousands of times) perform fine, while at other times the query times significantly longer for similar data row counts. Using RedGate SQL Monitor I see the time where it runs slow has a lot more logical read operations.

There's a thread that suggests that the former is more efficient. I'd like to know if this is indeed the case and the explanation behind it. Given that the query I have in my application performs well at some times and extremely slow at others, I'm wondering if the group by version of the query will consistently reduce the logical reads and provide a consistent behavior.

Sanjiv Jivan
  • 1,922
  • 18
  • 19
  • 2
    Inspect, and if necessary post, your execution plan. Performance is very specific to your individual database design. – Dale K Dec 15 '20 at 05:38
  • They are very different queries logically. The second will update every row in table1 regardless if there are matching rows in table2. Comparing "performance" is pointless depending on your actual goal. – SMor Dec 15 '20 at 13:24

0 Answers0