[dba-VB] vb.net - Updates

Shamil,

I have just moved the whole project to my quad core x64 server and am 
bringing it up now.  If I can get that code running over on another 
machine (especially IF it is a faster machine) then the update part will 
be less of an issue.  In any event the parse WAS taking about 15 seconds 
for 10K records on the dual core x32 machine which was also running SQL 
Server.

I have designed this thing as a set of classes.  There is a "data class" 
which is opened by a class that parses the names.  The NameParser just 
writes the data directly into the ADO recordset in the data class.  I 
MIGHT be able to just shuffle the class holding the open ado recordsets 
off into a collection.  Another thread could pull those class instances 
out of the collection and call the update method which would start the 
update.  As it finished the update, it would destroy that data class 
instance and pull the next one out of the collection.

This would effectively separate the parsing and the updating, and each 
could proceed at it's own speed.

I have not investigated yet but I might be able to have multiple threads 
doing the name parsing, and storing the resulting data class in that 
collection.  It depends on whether the dll is reentrant I suppose and I 
really don't have a clue yet..

There would only be a single thread processing the dataset classes "update".

I would need to watch that I didn't get too many open data classes at 
once time I suppose, though this machine has Windows x64 and 8 gigs of 
RAM so it has a little more breathing room than the Windows x32 machine 
I was running it on.

Of course all of this is just "play".  At least it is running now.

I was also thinking about running two threads in "opposite directions", 
one processing data PK high to low, and the other processing data PK Low 
to High.  Until they met in the middle that MIGHT avoid the deadlock 
thing.  Again though, that is just play.

NOT TO MENTION that I have never run a thread anyway so i have to figure 
THAT out.  ;-)

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> John,
> 
> I'm not sure yet what happens< I will try to find out more details in the
> coming days - here is the message I'm currently getting with new Strategy 4
> , which I'm making similar to your way of coding this task but running in
> multi-threading environment:
> 
> +++
> System.Data.SqlClient.SqlException: Transaction (Process ID
>  57) was deadlocked on lock resources with another process and has been
> chosen as the deadlock victim. Rerun the transaction.
> ---
> 
> Just wanted to warn you - it might happen you'll never see this message but
> if you'll see it then you may find there is no workaround except abandoning
> the idea of batch updates running in several threads - and I mean parallel
> batch updates: of course, as I noted in my previous e-mail parallel reading
> and parsing could be done but batch updates very probably can't be run in
> parallel threads because they might result in deadlocks...
> 
> But it looks like (from your recent posting here) that you don't need such
> parallel batch updates at all?
> 
> Thanks.
> 
> Shamil