I have a table with 20M rows, and each row has 3 columns:
value. For each
time, there is a
value for the status. I want to know the values of the last and the next periods for a specific
id, and have the following query to get the values:
update a1 set a1.value_last = b1.value, a1.value_next = c1.value from tab1 a1 left join tab1 b1 on a1.id = b1.id and a1.period = b1.period + 1 left join tab1 c1 on a1.id = c1.id and a1.period = c1.period - 1
It seems that the query takes forever and the log file increased by more than 10 GB. I'm wondering what's the most efficient way to write this query? I know using index will speed up the joining process, but how can I reduce the logging?
I'm using SQL Server 2016 on Win10 64bit.