I am running some performance tests and I started with a dataset of 900.000 records the update took 10 seconds or about 90000 records per second. Then I increased the number of records to 9.000.000 and the update took 170 seconds or about 52941 records updated per second.
Why does the number of records updated per seconds drop when the total number of records increases?
The distribution in the data is the same, the larger set is the smaller set copied 10 times. The size of the data and log files did indeed increase since I copied the data 10 times. I did shrink the log file after that. There is no other workload on the database server at this moment.
The SPROC doing the update is the following:
CREATE PROCEDURE updatePoints @ids NVARCHAR(MAX) AS DECLARE @points1 INT DECLARE @points2 INT SET @points1 = 1 SET @points2 = 2 UPDATE p SET points = ( 0 + CASE WHEN p.[x] = m.[x] AND p.[y] = m.[y] THEN @points1 ELSE 0 END + CASE WHEN p.[u] = m.[u] AND p.[v] = m.[v] THEN @points2 ELSE 0 END ) FROM p JOIN m ON m.id = p.mid JOIN platform.Split(@ids) i ON i.Value = m.id WHERE m.[status] = 1