Console App - MultiThreading with Database access

[fishberg5051]

Hey everyone, I have a question in regards to Threading an console app (as the title describes).

Here is my situation: I currently have an access Database that gets populated by a third party utility with a bunch of data, ID's, and hex messages that contain a bunch of data. My project consists of writing a parser that will grab those records in the access database, convert the hex to ascii (which is already done) and insert it into a sql tables.

I'm new to multi-threading and bought a book and have been working on a few things. I got as far as I can using a thread pool and have my classes written to pass an arugment into the class when the thread gets innitiated.

So going further into explination: I want to inniate one thread per record (so 500 records = 500 threads) then when it gets to the point of converting my hex value into ascii there is usually 5 to 27 different values derived from that value and I want to create a thread for each value, so potentially I will have about 2000+ threads running, but with proper management and killing them when I'm finished with them that shouldn't be a problem should it?

Can anyone give me an idea of what I can try to get this accomplished. I have posted below sections of my code. Currently the application works, and it takes 4.5 minutes to parse 500 records and insert the 5-27 records per parent record into sql. Ideally I want to do this in under a minute if at all possible. Thanks in advance, and sorry for the lengthy post.

'how I'm calling my first class using a thread pool
[SIZE=2]Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"Thread A: "[/COLOR][/SIZE][SIZE=2] & RecordCount & [/SIZE][SIZE=2][COLOR=#800000]" initiated"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]ThreadPool.QueueUserWorkItem([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] WaitCallback([/SIZE][SIZE=2][COLOR=#0000ff]AddressOf[/COLOR][/SIZE][SIZE=2] WorkerA.DoWork), RecordCount)[/SIZE]
 
'my first class
'######################
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] MessageProcessor[/SIZE]
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] _IsCompleted [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Boolean[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] _ConnectionString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"Provider=Microsoft.Jet.OLEDB.4.0; Data Source="[/COLOR][/SIZE][SIZE=2] & strIAMSDBLocation & [/SIZE][SIZE=2][COLOR=#800000]";"[/COLOR][/SIZE]
 
 
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]ReadOnly[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Property[/COLOR][/SIZE][SIZE=2] IsCompleted() [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Boolean[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Get[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Return[/COLOR][/SIZE][SIZE=2] _IsCompleted[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Get[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Property[/COLOR][/SIZE]
 
 
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] DoWork([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] state [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Object[/COLOR][/SIZE][SIZE=2])[/SIZE]
 
[SIZE=2]_IsCompleted = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]SyncLock[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](MessageProcessor)[/SIZE]
[SIZE=2]subProcessIAMSMessages(state)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]SyncLock[/COLOR][/SIZE]
 
[SIZE=2]_IsCompleted = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[COLOR=#0000ff]End Class[/COLOR]
[COLOR=#0000ff]'#######################[/COLOR]
 
'after building a virtual recordset I loop through it to insert my values into sql
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intCurrentRecordCount [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 0[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] InsertThreads(objRS_Message.RecordCount) [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Thread[/SIZE]
[SIZE=2][COLOR=#0000ff]Do[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][SIZE=2] objRS_Message.EOF <> [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] StartInsert [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] InsertProcessor(objRS_Message([/SIZE][SIZE=2][COLOR=#800000]"DataPointId"[/COLOR][/SIZE][SIZE=2]).Value, objRS_Message([/SIZE][SIZE=2][COLOR=#800000]"SampleTime"[/COLOR][/SIZE][SIZE=2]).Value, objRS_Message([/SIZE][SIZE=2][COLOR=#800000]"DataPointValue"[/COLOR][/SIZE][SIZE=2]).Value)[/SIZE]
[SIZE=2]InsertThreads(intCurrentRecordCount) = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Thread([/SIZE][SIZE=2][COLOR=#0000ff]AddressOf[/COLOR][/SIZE][SIZE=2] StartInsert.DoWork)[/SIZE]
[SIZE=2]InsertThreads(intCurrentRecordCount).Name = [/SIZE][SIZE=2][COLOR=#800000]"Thread#"[/COLOR][/SIZE][SIZE=2] & intCurrentRecordCount[/SIZE]
[SIZE=2]Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"Thread Insert Started for: "[/COLOR][/SIZE][SIZE=2] & intCurrentRecordCount)[/SIZE]
[SIZE=2]InsertThreads(intCurrentRecordCount).Start()[/SIZE]
[SIZE=2]InsertThreads(intCurrentRecordCount).Abort()[/SIZE]
[SIZE=2]intCurrentRecordCount = intCurrentRecordCount + 1[/SIZE]
[SIZE=2]objRS_Message.MoveNext()[/SIZE]
[SIZE=2][COLOR=#0000ff]Loop[/COLOR][/SIZE]
 
'my last class to do the insert
[SIZE=2][COLOR=#0000ff]'############################[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] InsertProcessor[/SIZE]
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] _IsCompleted [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Boolean[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] _DataPointID [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] _SampleTime [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Date[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] _DPV [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Double[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] DataPointID [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] SampleTime [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Date[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] DPV [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Double[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2]._DataPointID = DataPointID[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2]._SampleTime = SampleTime[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2]._DPV = DPV[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]ReadOnly[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Property[/COLOR][/SIZE][SIZE=2] IsCompleted() [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Boolean[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Get[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Return[/COLOR][/SIZE][SIZE=2] _IsCompleted[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Get[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Property[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] DoWork([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] state [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Object[/COLOR][/SIZE][SIZE=2])[/SIZE]
 
[SIZE=2][COLOR=#0000ff]SyncLock[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](InsertProcessor)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strSQL_GTW [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE]
[SIZE=2]strSQL_GTW = [/SIZE][SIZE=2][COLOR=#800000]"query string removed for security reasons"[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand[/SIZE]
[SIZE=2]conn.ConnectionString = [/SIZE][SIZE=2][COLOR=#800000]"Removed for security reasons"[/COLOR][/SIZE]
[SIZE=2]conn.Open()[/SIZE]
[SIZE=2]cmd.Connection = conn[/SIZE]
[SIZE=2]cmd.CommandType = CommandType.Text[/SIZE]
[SIZE=2]cmd.CommandText = strSQL_GTW[/SIZE]
[SIZE=2]cmd.ExecuteNonQuery()[/SIZE]
[SIZE=2]cmd = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE]
[SIZE=2]conn.Close()[/SIZE]
[SIZE=2]conn = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE]
 
 
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]SyncLock[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE]
'#########################

so if anyone has any comments/ideas on what I can do to do "X" amount of inserts very fast I would appreciate it. Thanks for your patience.

Sincerely,

Fishberg

 

[Anti-Rich]

i would strongly advise against having that many threads, as i doubt the processor/s would be able to handle that many threads. for each new thread created windows needs to allocate time to execute at least part of the thread, if you create too many, windows will spend all its time switchin between the threads, executing parts of the code, and it will result in a huge performance hit (or even possibly not getting to execute some threads and/or crashing your app)

my advice would be to create an array or list of sql statements, and loop through the array, executing them on one connection... not only that (and i just thought of this) the overhead of trying to execute so many threads, each opening and closing a connection is going to take AGES...

i recently did something similar... i grabbed 58000 records from an excel spreadsheet and chucked it into an sql server 2005 express database. maybe an alternate way of doing it for you rather than executing an inserting record per thread would be to grab the entire table as a dataset, and update the dataset onto the sql database, on one single separate thread? this took me about a minute for my thing and the ui remained responsive... is this basically what you are trying to do?

also, if you want to learn a bit more about multithreading, search the thread "useful multithreading tutorial" and follow the first link (but be sure to read down anyway, as jmcillney clarified some things that might also be useful to you) and have a read.

maybe im wrong about the processor handling so many threads, like you i am new to multithreading, so if anyone else has any input (or corrections to my statements) id be glad to hear them!

hope it helps and any more questions dont hesitate to ask!

regards
adam

 

[fishberg5051]

Anti-Rich, thanks for your reply and your insight. I will definitley check out that thread you suggested. I wish I could just iterate through a dataset, essentially yes that's what I'm doing, but I have to parse a bunch of hex messages and obtain the values from the hex message and that's what I'm inserting into sql. Even though that's the easy part, every hour we have a service that brings in a couple thousand of these messages, and 5 to 24 values to insert (from the hex message) so about 24,000 inserts to do. Multi-threading is the only way (in my mind as of now) we can do this relatively fast (and this will be running on a dual processor server, so maybe that will help???) ....I guess I will just have to read some more. Thanks a ton for posting back. Keep me posted of anything else comes to mind.

Speed is definitley the main focus here. *cheers* anyway!

 

[Anti-Rich]

hey, no problem dude glad to help. ive lost count of how many times people have helped me out on the forums so i try to do the same for other people when they need it.

actually i did think of something...

this is working on the basis that you have 2 tables... the first one is the main where the inital inserts go to, and the second is where the derived records from the 1st table go...

why dont you simply create a list(or dictionary) and as you are inserting values to the main table, for each dervied record you also need to insert, add it to the list. and then at the end, simply loop through the list and insert the derived values into the second table...

does that help?

regards
adam

 

[Anti-Rich]

hm, after reading your post again, i think i may have misunderstood part of your question. are you just inserting rows related to hex messages? ie.

MESSAGE COMES IN >> 5 ROWS GENERATED FOR INSERTION, MOVE ONTO NEXT MESSAGE... and so on and so forth

is that what you mean? lol im a little bit slow at the moment *sheepish grin*.

still, you could add every single row to a dictionary or list and when you have finished figuring out how many rows you will need to insert (due to the amount of hex values etc), simply loop through the entire dictionary and insert the values to the server (on a separate thread of course, this will keep the responsivness of your app at good levels)

hope it helps mate, and excuse the stupidity on my part

regards
adam