Async DB Ops

  • Thread starter Thread starter Mike Caputo
  • Start date Start date
M

Mike Caputo

I couldn't get to the newsgroup yesterday, much like a lot of other people,
apparently. I posted something yesterday and haven't seen it yet... if it
ends up being a double post, please forgive me. Here it is, and I hope it
gets through:

Using VB.NET to update an OLE DB. Basically the VB part is just a shell
that iterates through all the items that need to be update, but all the real
work (calcs, etc) is done internally in the DB. Specifically, it's updating
stock data for about 5000 stocks and 500 stock groups (indices, industries,
market sectors, etc). Anyway, what I'd like to do is a nested iteration on
all the items, so that 6 are started at a time. Currently it does two at a
time and the server handles it fine, but the way I have it set up is ugly
and not at all scalable. I want to have something like this:

For i = 0 to StockTable.Rows.Count-1 Step 6
For j = 0 to 5
'Wait here?
AsyncCommand.UpdateStockData (StockTable.Rows(i+j)
Next
'Or wait here?
Next

I already have an AsyncCommand class, which opens a separate connection and
sends the command; it works fine, but two at a time is not fast enough. The
problem I'm having is how to get it to wait so that I don't end up trying to
open 1000 connections. Ideally, I'd like it to wait on each iteration of
the inner loop so that it will sort of stutter-step through and send the
next command as soon as a connection becomes available. I'm assuming I have
to use an array of Mutexes to do this, but I've never used them before, and
I'm really not sure where to put the WaitAny calls. Also, I would like to
be able to just create all the connections ahead of time and leave them open
rather than opening and closing every time, but I haven't been able to get
that to work even when I only do two at a time. I know this is a lot, but I
hope someone has the time to give me some suggestions. I would really
appreciate it.

Thanks,
Mike


--


Michael Caputo
Programmer/Database Administrator
Simon Economic Systems Ltd.
847-272-7691
(e-mail address removed)
 
Hi Mike,

1
At the moment you've got a superior list (those For loops) telling your
subordinate AsyncCommands to "Go do it!".

You could turn it on its head and create a subordinate list (queue) of
jobs to which your AsyncCommands say "Gissa job". You can apply your locking
just to the queue cutting down on the amount of synchronization required.

2
Have your JobAllocator (those For loops) in a separate thread. Give it a
counter. Have it start AsyncCommands while Counter <= 6. When it reaches 6
have it sleep. Each AsyncCommand that finishes (or fails with an Exception)
decrements the Counter (with locking) and wakes the JobAllocator. This then
etc, etc.

As for the connections, you haven't said why keeping them open doesn't
work. What are you doing in that respect and how is it failing?

Regards,
Fergus
 
Back
Top