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!