UpdateBatch has been dramatically changed with the current bits (41129) of VS2005. Here are the details on that.
Now that we can exceed the 2100 parameter limit I have been doing some experiments with a 10000 record query from a 104,000 record table and doing batch updates in big chunks. It will be important to tune your batch updates based on the system – location of sql server relative to the application, latency, etc etc. Here is what I hit today when working against a hardwired remote sql server 2000.
Interesting….
Modifying the new CommandTimeout property (from default of 10 to 60) on the DataAdapter’s SelectCommand, fixed this problem. Then I had to change it again for a batch of 10,000. I just popped it up to 120 and that did the trick.
What’s going on of course is that ado.net is building some big goo up front. Prior to this version, it built one big fat query to accomodate the entire batch. But that’s where they quickly (very quickly) hit the 2100 parameter limit. Now watching profiler, it is getting run one update (one row) at a time. I wish I could watch what happens in between, but, really, that’s okay! Can’t geek out too much.
This is the kind of fun you can have on a beautiful winter day with a broken foot. 🙂
Posted from BLInk!
Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!