How to increase performance?

[rp2018]

Performance loading from source db to target db is very slow. Our source db is on premise and target db on Azure.  Loading three tables and all of them total around 25,000 records and it takes 5 minutes to load. No transformation is done, only straight load.  My job uses tMSSqlConnection to connect and auto commit option is selected.  Is there any options to set to increase the speed?

[diazsmiedts]

Hi, was this eventually resolved or not?

I'm facing the same issue - reading from salesforce connection and inserting in a test table on Azure SQL Database takes too long. Compared to the same action on a PostgreSQL environment, it is 100 times slower. Azure SQL is a standard 100 DTU environment. Postgres is free RDS on AWS. The Azure processing takes a total of 529 seconds for 50K records

The postgres variant doing exactly the same, only takes 49seconds. Go figure ..

Any Azure / Talend experts that can recommend some improvements? 

The process start with SF connection and DB connection, then uses TDBrow to truncate the target table (using existing connection), calls the SF api (Bulk Query mode) and transfers the data for insertion into the target tabel. Target table uses Parallel 4, Data action = Insert, table is always empty to start with, no keys required, batch size = 10000

Post-job does the commit and close of connection. 

[Anonymous]

---

@rp2018 wrote:

Performance loading from source db to target db is very slow. Our source db is on premise and target db on Azure.  Loading three tables and all of them total around 25,000 records and it takes 5 minutes to load. No transformation is done, only straight load.  My job uses tMSSqlConnection to connect and auto commit option is selected.  Is there any options to set to increase the speed?

---

Hello everyone,,

Do you have a (unique/primary) key and have UPDATE or INSERT or the other way around?
Update/insert is the performance killer.

Suggestion, load from premises into tmp_tbl and run a t-sql script to load this tmp table into target tbl. Destroy the tmp table on success..

[rp2018]

After we upgraded the Azure database to Premium (P1)  the performance drastically improved.