Updating millions of rows
Before running this example, create a text file named 'Stock Data.txt' in the folder C:\SQLFiles\. Stock AS s USING OPENROWSET ( BULK 'C:\SQLFiles\Stock Data.txt', FORMATFILE = 'C:\SQLFiles\Bulkload Format File.xml', ROWS_PER_BATCH = 15000, ORDER (Stock) UNIQUE) AS b ON s.
The file should have two columns of data separated by a comma.
By doing so, the entire file is processed in a single batch.
To improve the performance of the bulk merge process, we recommend the following guidelines: These guidelines ensure that the join keys are unique and the sort order of the data in the source file matches the target table.
Throttling has two benefits: it minimizes the amount of load on the master host, and it minimizes the replication lag introduced when the update statements are executed on the replicas.
It achieves this by introducing sleep statements after each chunk is updated.
These indexes ensure that the join keys are unique and the data in the tables is sorted. When the source is smaller than the target table, a nested loops operator is preferable. This means that any literal values that are contained in the query are substituted with parameters. This process, called simple parameterization, increases the ability of the relational engine to match new SQL statements with existing, previously-compiled execution plans.
Using does not guarantee that you will completely avoid replication lag, but if you do some testing you should be able to find an appropriate value that will strike a balance between the total run time of your updates and the level of replication lag that it introduces.That is, there is no order in which the rows are distributed among the actions defined in the WHEN clauses.For example, specifying TOP (10) affects 10 rows; of these rows, 7 may be updated and 3 inserted, or 1 may be deleted, 5 updated, and 4 inserted and so on.It is common to use the TOP clause to perform data manipulation language (DML) operations on a large table in batches.When using the TOP clause in the MERGE statement for this purpose, it is important to understand the following implications.
In addition, this method only limits the number of rows that can be updated; each batch will try to insert all of the "unmatched" source rows.