I am trying to update the table with a result from select query. The problem is that query takes too long and i get an error that log file is not enough. For example:
With cte as (
select t1.key, t1.col1, t2.col2, t3.col3
from t1
left join t2 on t1.key = t2.key
left join t3 on t1.ket = t3.key
)
UPDATE t4
SET t4.col1 = cte.col1
t4.col2 = cte.col2
t4.col3 = cte.col3
from cte
where
t4.key = cte.key
Right now i am updating the table by parts (just change the end of the query to
where
t4.key < 10000000
t4.key = cte.key
). Is there any way to optimize it and make queries faster?
Is there any way to optimize it and make queries faster?
You're doing most of it already. Doing your update in range-limited batches (with key < 10000000 in your case) is the way to do this kind of mass update to a table. More chunks and fewer rows in each chunk will perform even better. When I do this kind of thing I use lots of batches, usually with 500 rows in each.
There's one other thing you can do: refrain from updating rows which already have the correct values. Like this:
...
UPDATE t4
SET t4.col1 = cte.col1
t4.col2 = cte.col2
t4.col3 = cte.col3
FROM cte
WHERE t4.key = cte.key
AND t4.col1 <> cte.col1
AND t4.col2 <> cte.col2
AND t4.col3 <> cte.col3
The point here is to have each UPDATE statement touch a limited number of rows. SQL server works by writing the whole update into its transaction log. Then, when it knows the whole transaction will succeed, it changes the tables with the data in the transaction log. All that is to make it possible for other queries to see the old values in the table until your UPDATE statement finishes and lands in the table all at once rather than trickling in. It's all part of SQL's ACID -- atomicity, consistency, isolation, durability -- functionality.