[dba-VB] ADO.Net

Thanks Shamil,

Unfortunately unless you are dealing with 80 million row 200 field 
tables, any timing comparisons are going to be suspect.

I considered doing (and may still do) a record by record update using 
either a stored procedure or dynamic sql right from vb.net.  Then I read 
an article (blog) that essentially said that the batch update from an 
ado dataset really did work as you would hope that it would, and this in 
a blog from a member of Microsoft's ADO.Net dev team.

He claimed that either of the other methods had network transaction 
overhead as each update (when done singly) required the command to do it 
as well as a result coming back, whereas if you used a batch it would 
group all of the commands out and then group all the results coming back 
into big packages.

Understand that I do not know enough to even comment on the validity of 
his argument however he said to definitely try it.  Set the batch size 
and then let ADO just send batches of updates.

Given that the coding effort is smaller to do that I figured I would at 
least get that running.

I am definitely interested in doing strongly typed recordsets though.

Shamil Salakhetdinov wrote:
> Hi John,
> 
> Try to use SqlDataReader and custom classes as in the sample code below
> (sorry C# is used - real pleasure to use C# is also coming from the fact
> that you can copy and paste code and do not care about line warps because
> even "screwed by e-mail" code should be compiled well)....
> 
> ...using ADO.NET datasets and bulk update will anyway result in a series of
> updates on SQL server side therefore custom on-the-fly built sql updates or
> calls to update SPs should work as quick as ADO.NET DataSets' batch
> update...
> 
> ...etc...
> 
> ...as you can see my sample did update 25000 records in ~10 seconds - and
> this only for starters - if that approach will work reasonably well for you
> then the next step could be to introduce multi-threading etc...
> 
> ...note also how MS SQL 2005's paging feature is used to get record's
> batches...

-- 
John W. Colby
www.ColbyConsulting.com