Hi Sabrina,
I found the problem.. there are 2 bottleneck in the process, first one is network, once I run the process on a server with high network connection it solve part of the problem. The second issue is the update else insert mode. Once I change it to insert only, I could run the loading in a batch mode. (Put 500 rows per insert work like magic). The data is loaded into a temp tables in Redshift and the update else insert operation is done by a join from the temp table to the target table as proposed by Amazon (Amazon best practice for upsert operation)
In that way I manage to insert 1000 Rows per second, the update else insert is very fast when done on Redshift db, so this design works for me.
I wonder if Talend could create a better upsert mechanism in the future that could use batch mode like the insert.
Thanks !
Ophir
