Multithreading in a looping situation

  • Thread starter Thread starter RSH
  • Start date Start date
R

RSH

Hi,

I have a situation in where I have a main loop that queries a "Parent"
Global database table. Based on that resultset I loop through all of the
databases and the appropriate table in the "children" databases.

Because we have roughly 5000 "Children" databases this process is taking
quite some time.

I was wondering if there is some way to incorporate multithreading to speed
the process up.

This is the process in short:

----------------------
Query the Parent database table: SELECT CompanyID FROM Global.dbo.Parent
ORDER BY CompanyID

Begin Loop through the Parent result set

SELECT <columnlist> FROM <CompanyID>.dbo.Company

Begin Loop through the Child result set

Process Child Rows < Lightweight processing...>

End Loop through the Child result set

End Loop through the Parent result set

--------------------
Ideally I would like to is to run the Parent Query and have multiple Child
threads running. Is this possible? If so how?
If it isn't possible using the current structure how could I reconfigure the
scenerio to ultilize multi-threading?

Thanks for your time!
Ron
 
Why is this slow ? Are those databases on the same server ? Even with
multithreading you could end up anyway with a bottleneck if they are on the
same server.

What is this processing ?

My first thought would be :
- what is the processing you have to do ?
- do you have to loop, can't this processing be done using a SQL statement ?
- do you need a separate DB by company (my understanding is that you have a
separate database for each company ?)
- if this is to gather information, you could keep separate data for each
company but keep internal audit trail or whatever it is in a central
location toa void ahaving to browse all those DB to collect them (not sure
what you have to gather those partitioned data...)

etc...
 
Back
Top