Should I use multiple threads?

[OhioSteve]

My coworker made a dts that does eight threads simultaneously. He claims that multiple threads are "the way to go". He points out that our server has four processors, so it can do at least four threads simultaneously.

Currently my big package has just ONE thread. I thought that this multithread stuff was just crazy. I thought that it would be hard to create such a package, and that it might blow up when I ran it.

Today I made a multithreaded version of the package and I ran it. I was surprised that it was not too difficult to create. When it ran, the server did not experience any problems, and it did not blow up.

However, it was NOT any quicker than my old package. Plus, I noticed that only ONE task had the word "running" beside it at any time. So do I need to do something besides just make two green piplines? Under properties I have the max threads at four.

 

[MDXer]

If your package has a workflow where every step is contigent upon a previous step and only one step is running at a time then Multithreading makes no sense however if your package has multiple independent workfolws or a single step may branch into multiple steps then multi threading is a benifit.

Example

Step 1
On Sucess
Step 2
On Sucess
Step 3

No benifit

Step 1
On Success
Step 2
Step 3

then with multiple threads 2 threads would get utilized after the completion of Step 1

You can take it to another degree by multi threading packages that call addition multi threaded packages.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[OhioSteve]

My task certainly has multiple workflows. This is the task:

1) enter data into sql server log table
2)truncate 14 sql server tables
3)transfer data from 14 oracle tables into the empty sql server tables
4) enter more data into sql server log table


Step three consumes almost all of the execution time. Two of the 14 tables each contain about 150k records. The other 12 tables are small...they contain 1k-36k. Currently I start with one thread. Then on step three it branches into three parts. The big tables each get their own thread. The 12 small tables are in the other thread. Then I bring the threads together in step4.

I'm using the word "step" loosely in this message.