What to look for when setting up UpdateBatchSize

Question

I have a .NET application that combines two data types with a lot of rows (10,000+). There is a good chance that there will be a large number of updates / inserts to execute the SQL table when using the DataAdapter.Update command.

I currently have the UpdateBatchSize adapter property set to 200. VS warns me that this value is set too high, as this may slow performance. Ok, got it.

The efficiency that I should look for when setting this property? Regardless, updating a few lines will take a lot of time. Running it on my computer (or on the database server) does not take much time, but I am sure that when the system boots up, doing other elements, this can be a problem.

Is there something I can find in Profiler? Performing standard profiling, the duration is usually 0. Sometimes these are hits 1 or 2 (maybe 20 times in total) and about 20,000 updates, 3-4 hits 20. The CPU is at 0, except for a pair that hits 1-2, There are 2 entries that go up to about 10. Reads are always 2, and entries are always 0.

Answer 1

First of all, I would configure this parameter so that you can test various values ​​without compilation. The next is to keep track of the duration of your entire party. If you are satisfied with your work, do not change it. If your dissatisfied with the performance will try to increase or decrease the setting to see how it behaves.