Suppose we have a database server, with SQL Server on it. And we are moving data from another database server, through our Informatica server, and onto that SQL Server database server. And our SQL Server database server has four processors. Is it possible to force the connections to be multi-threaded so it sends data quicker? How can this be done?
Right now, only one of the processors is being used on the SQL Server database server.
This question was asked many years ago, but I never posted how I solved it. I got a > 24 hour ETL process to run in under 8 hours by having multiple tasks run, and then each task runs the exact same mappings IN PARALLEL, but with a slightly different source definition. I basically split up the tables based into multiple duplicate tables and then split up the data equally. I probably could have used indexes, but this is a once a month process, so that didn't matter too much.
Also had to configure this in SQL Server to allow the executions to run in parallel..
Multi-thread Informatica connections to use different processors on target database server
And had the same 4TB dataset moving to Oracle with the same ETL process. But oracle has way more configuration!