I mentioned in a previous post that the Batch updating process was changed with the current (November) bits of Whidbey to overcome a limitation in SQL Server – which is that you cannot have more than 2100 parameters in one query.
In response to a discussion in the newsgroups (ADO.NET 2.0 Batch Update), below is a screenshot from SQL Profiler with the earlier bits when I had set UpdateBatchSize=3.
It bunched together 3 queries into one big query. With 15 parameters per query, SQL Server was receiving 46 parameters.(the 45 parameters + the actual query string which was stuffed in to @P1).
In this case, I would be hitting the limit if I tried to send more than 140 rows.
In the new bits, if you watch the profiler, you will see one row being updated per query. So ADO.NET is now sending a group of individual queries in each batch, rather than one big huge query. I have sent batches of 10,000 rows at a time.
Example from Beta 1 October CTP – no longer true for future releases of .NET 2.0
Posted from BLInk!
Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!