January 20, 2011 at 4:01 am
Hi
I have 10 SSIS packages pulling data from 10 tables of remote database server and loading the data in 10 tables with one to one mapping.
I am going to call all 10 packages from single package (by creating Master package).
Which is best way to arrange them in Master package from Perfromance point of view.
Parallel OR Sequential
So that we can reduce the overall execution time.
Thanks
January 20, 2011 at 4:09 am
Why not try both and see for yourself?
I would, however, expect parallel to win...
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 20, 2011 at 4:16 am
yes, I am trying for both option.
Is it depend on Source/Target Database configuration.
January 20, 2011 at 5:27 am
It kind of depends on how much processors you have.
But normally, I would expect parallellism to win. Just make sure no locks are introduced.
Also, be aware that high parallellism can introduce a lot of I/O contention.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 20, 2011 at 5:53 am
Koen (da-zero) (1/20/2011)
It kind of depends on how much processors you have.But normally, I would expect parallellism to win. Just make sure no locks are introduced.
Also, be aware that high parallellism can introduce a lot of I/O contention.
I have single processor, I tried both the option and I observed that Sequential takes less time than Parallel.
I want to know that what are things we need to consider before deciding Sequential Or Parallel.
January 20, 2011 at 6:12 am
saurabh.deshpande (1/20/2011)
--
I want to know that what are things we need to consider before deciding Sequential Or Parallel.
If you are talking purely about performance, there are many possible factors.
If you are talking about something apart from performance, please elaborate.
Even if you are armed with 100,000 words of series-vs-parallel deep theoretical analyses, nothing beats running a test. Unless you have insomnia, that is.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 20, 2011 at 6:47 am
saurabh.deshpande (1/20/2011)
Koen (da-zero) (1/20/2011)
It kind of depends on how much processors you have.But normally, I would expect parallellism to win. Just make sure no locks are introduced.
Also, be aware that high parallellism can introduce a lot of I/O contention.
I have single processor, I tried both the option and I observed that Sequential takes less time than Parallel.
I want to know that what are things we need to consider before deciding Sequential Or Parallel.
There are so many factors to be considered.
For example: if the source system has only one harddrive, it will probably better to use sequential task, to minimize the head of the disk spinning everywhere (or in other words, minimize I/O). With sequential processing, the different CPUs can work together to process that one task very fast.
On the other hand, if the source system has multiple disks and everything is nicely partitioned, parallel processing will probably be faster, as there is no I/O contention.
As Phil said: experiment experiment experiment
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 20, 2011 at 6:53 am
Along with what everyone else has said, I suspect that since you are pulling the data from a remote db server the network will have a bigger impact in the performance of your SSIS packages. Also have read of the SQL CAT teams best practices if you are looking at getting max performance out of your SSIS packages.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply