need ideas on multi threaded db update

8 Mar 2006 8:10 PM

cj

I have to take all records in a SQL db that have an empty val_code field
and process and update the records with a code number in the val_code field.

The processing is done by sending select info from each record to a
remote server. The remote server returns the code number to be put in
the val_code field. The remote server will allow me to have up to 5
"sessions" open with it at a time.

I'm thinking each "session" will have to be in a separate thread. I
also assume a main thread must exist to interact with the database. Can
anyone give me any other ideas?

Thanks.

9 Mar 2006 5:12 AM

Peter Huang" [MSFT]

Hi

Based on my understanding, you will get something from a DB.
And then send request to a remote server which allow 5 "session" and then
update DB.
Can you describe how did you send the request?
Via WebService or anything else?

What do you mean by "session" here?

If you mean a multitheading issue in Winform, we can use the Control.Invoke
to marshal the cross thread call on the main thread.
Safe, Simple Multithreading in Windows Forms, Part 2
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnforms/htm
l/winforms08162002.asp

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

9 Mar 2006 11:37 AM

Andrew Morton

cj wrote:

> I have to take all records in a SQL db that have an empty val_code
> field and process and update the records with a code number in the
> val_code
> field.
> The processing is done by sending select info from each record to a
> remote server. The remote server returns the code number to be put in
> the val_code field. The remote server will allow me to have up to 5
> "sessions" open with it at a time.

How is the val_code derived from the data? If there are a limited number of
val_code values then you could get them all in one go, or at least implement
a local cache (Array/ArrayList/whatever else might be appropriate) so you
don't have to query the remote server so much.

Are you using one database to update another database on a different
computer, or is it all within the same database? If the latter then surely
it would be more efficient to do it all in a stored procedure in the
database.

Andrew

9 Mar 2006 1:58 PM

cj

Thanks Peter and Andrew, I'll try to explain better. The program will
send fields from the records w/o a val_code to the remote server (it's a
soap web service kinda thing but this should be unimportant as it is set
in stone and I have the code necessary to send/receive available) the
response could take milliseconds or several seconds. Records need
processing very quickly. They allow me to have 5 "sessions" (that's the
server folks word for it) open with their server at the same time. I
don't have to wait for the first request to come back to submit another
I can submit up to 5 at a time but each request is done in a seperate
"session" or connection to the server. The only way I see having 5
sessions open on one pc at a time is multiple programs or multiple
threads. Threads being my choice.

I've been thinking since I asked the question (dangerous, I know) and I
see a main thread that launches 5 worker threads. Each worker thread
creates a session and logs in to the server. The main thread meanwhile
pulls a group of records needing validation from the sql server. Then
as long as it has records starts a loop checking to see if worker thread
1 is busy, if thread 1 is the main thread checks worker thread 2, if
it's busy 3 and lets say 3 isn't so it passes the record to worker
thread 3. then picks the next record and check to see if 1 is busy then
2 etc. Each thread would be responsible for updating the sql database
with the changes to the record it was assigned.

To do this I need to know:
--how to reference a thread ie how to know which thread is 1, 2, 3 etc.
--how to set a busy indicator in a worker thread and how to read it
from the main thread
--how to assign a thread a record

cj wrote:
Show quoteHide quote

> I have to take all records in a SQL db that have an empty val_code field
> and process and update the records with a code number in the val_code
> field.
>
> The processing is done by sending select info from each record to a
> remote server. The remote server returns the code number to be put in
> the val_code field. The remote server will allow me to have up to 5
> "sessions" open with it at a time.
>
> I'm thinking each "session" will have to be in a separate thread. I
> also assume a main thread must exist to interact with the database. Can
> anyone give me any other ideas?
>
> Thanks.

9 Mar 2006 7:34 PM

cj

Forget this for now--my priorities have been changed.

cj wrote:
Show quoteHide quote

> Thanks Peter and Andrew, I'll try to explain better. The program will
> send fields from the records w/o a val_code to the remote server (it's a
> soap web service kinda thing but this should be unimportant as it is set
> in stone and I have the code necessary to send/receive available) the
> response could take milliseconds or several seconds. Records need
> processing very quickly. They allow me to have 5 "sessions" (that's the
> server folks word for it) open with their server at the same time. I
> don't have to wait for the first request to come back to submit another
> I can submit up to 5 at a time but each request is done in a seperate
> "session" or connection to the server. The only way I see having 5
> sessions open on one pc at a time is multiple programs or multiple
> threads. Threads being my choice.
>
> I've been thinking since I asked the question (dangerous, I know) and I
> see a main thread that launches 5 worker threads. Each worker thread
> creates a session and logs in to the server. The main thread meanwhile
> pulls a group of records needing validation from the sql server. Then
> as long as it has records starts a loop checking to see if worker thread
> 1 is busy, if thread 1 is the main thread checks worker thread 2, if
> it's busy 3 and lets say 3 isn't so it passes the record to worker
> thread 3. then picks the next record and check to see if 1 is busy then
> 2 etc. Each thread would be responsible for updating the sql database
> with the changes to the record it was assigned.
>
> To do this I need to know:
> --how to reference a thread ie how to know which thread is 1, 2, 3 etc.
> --how to set a busy indicator in a worker thread and how to read it
> from the main thread
> --how to assign a thread a record
>
>
>
>
> cj wrote:
>> I have to take all records in a SQL db that have an empty val_code
>> field and process and update the records with a code number in the
>> val_code field.
>>
>> The processing is done by sending select info from each record to a
>> remote server. The remote server returns the code number to be put in
>> the val_code field. The remote server will allow me to have up to 5
>> "sessions" open with it at a time.
>>
>> I'm thinking each "session" will have to be in a separate thread. I
>> also assume a main thread must exist to interact with the database.
>> Can anyone give me any other ideas?
>>
>> Thanks.

10 Mar 2006 5:14 AM

Peter Huang" [MSFT]

Hi CJ,

Thanks for your update!
If you still have any concern, please feel free to post here.
Thanks!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

10 Mar 2006 7:57 AM

Cor Ligthert [MVP]

cj,

> Forget this for now--my priorities have been changed.
>

I don't know if I wrote this already to you, I think that to let that be
forever is a good choose, however.

Let the Server do its job by handling the sessions in a proper way, and
don't interfere in that.

Just my thought,

Cor