If you are doing an UpdateBatch with ADO.NET 2.0 and do not explicitly set an UpdateBatchSize parameter, this will default at one – meaning that one row will be sent at a time to the server for updating. Whatever other number you set it at is the number of rows that will be sent in a batch and of course you need to consider a variety of factors when choosing this number (such as network latency, how many columns are in the table, etc – bigger is not always better!) This is hardly new info at this point, but there is one other setting – zero. I am listening to Pablo Castro’s ADO.NET 2.0/SQL Server integration talk from TechEd (DAT320) and laughing because he says (this is not a direct quote – I am paraphrasing) “If you set it to zero, there will be no limit to the number of rows….[pause]…which isn’t really very good for performance .. [pause].. I don’t really know why we put it there, but…[you can practially hear him shrug his shoulders] .. we did”. Pablo can totally get away with this… the audience laughs with him and he moves on. Funny how if some other teams said something like this this, they would probably have many detractors. ADO.NET is just a happy API!
Don’t Forget: www.acehaid.org
Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!